MySQL Bugs: #28534: INSERT INTO ... SELECT FROM doesn't care about REPEATABLE READ

Bug #28534	INSERT INTO ... SELECT FROM doesn't care about REPEATABLE READ
Submitted:	19 May 2007 10:41	Modified:	10 Sep 2007 18:29
Reporter:	Peufeu Peufeu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.44, 5.0.38	OS:	Linux (Gentoo)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
INSERT INTO ... SELECT FROM selects rows using READ COMMITTED even if transaction isolaiton level is REPEATABLE READ.

In REPEATABLE READ mode, doing a SELECT will return the correct rows. INSERT... SELECT will not insert the correct rows.

See example :

How to repeat:
CREATE TABLE one ( a INTEGER ) ENGINE = InnoDB;
CREATE TABLE two ( b INTEGER ) ENGINE = InnoDB;
INSERT INTO one (a) VALUES (1),(2),(3);

Client 1 :

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM one;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

Client 2 (autocommit) :

INSERT INTO one (a) VALUES (4),(5),(6);

Client 1:

SELECT * FROM one;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

INSERT INTO two SELECT * FROM one;

SELECT * FROM one;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM two;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

I had some other problem, but I believe is cause by the same reason.

I found that the INSERT .. SELECT .. actually place a READ LOCK on the selected table, more like a LOCK IN SHARE MODE.
This can be the reason why the rows read is not from the REPEATABLE-READ isolation, but looks like READ-COMMITTED, since LOCK IN SHARE MODE always get the latest committed data.

I tested using MySQL 5.0.41 community on Windows.

Steps to show there is a read lock

Connection 1:
mysql> create table t (i int) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 like t;
Query OK, 0 rows affected (0.10 sec)

mysql> insert t values (1),(2),(3);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

This will place a write lock on the rows with the SELECT .. FOR UPDATE

Connection 2:
mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> insert t2 select * from t;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This shows that the SELECT is non-blocking, but the INSERT .. SELECT is blocking. I suspect it is trying to do a SELECT .. LOCK IN SHARE MODE.

I also tested with LOCK IN SHARE MODE in connection 1 instead of FOR UPDATE, this does not block the INSERT .. SELECT which means the INSERT .. SELECT is not trying to get a write lock on the selected table, but a read lock.

This is expected. When binary logging is enabled, InnoDB uses stronger locks for INSERT ... SELECT. Otherwise an UPDATE, for example, that started after INSERT ... SELECT started, may finish faster and will be written in the binlog before INSERT ... SELECT. So, on the slave UPDATE will be executed before INSERT ... SELECT and will affect the results. Which will break replication.

There's a variable @@innodb_locks_unsafe_for_binlog that would disable this behaviour. Alternatively, if you use row-based replication, InnoDB won't use stronger locks either.

Original test case is repeatable with 5.0.44. But I am still not sure it is a bug. SELECT will still give you results in a state before the autocommitted transaction from client 2. Second INSERT ... SELECT will insert the same results as first (because first will lock table from further updates). Reads are repeatable by subsequent SELECTs, and by subsequent INSERT ... SELECTs. It is not clear from http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html should they be consistent with respect to each other though. 

It can be an intended behaviour, but it should just be documented in more details.

Well, setting isolation to REPEATABLE READ is supposed to give, ahem, repeatable reads, which it doesn't. So this is a bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Serg's explanation is correct.  I've added this para to the consistent-read page:

InnoDB uses a consistent read for select in clauses like INSERT INTO
... SELECT and UPDATE ... (SELECT) that do not specify FOR UPDATE or
IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and
the isolation level of the transaction is not set to serializable.
Thus no locks are set to rows read from selected table. Otherwise,
InnoDB uses stronger locks and the SELECT part acts like READ
COMMITTED, where each consistent read, even within the same
transaction, sets and reads its own fresh snapshot.

This will appear at:

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html