MySQL Bugs: #60437: InnoDB locking on consistent read in UPDATE ... SELECT statement

Bug #60437	InnoDB locking on consistent read in UPDATE ... SELECT statement
Submitted:	11 Mar 2011 23:03	Modified:	8 Feb 2012 23:58
Reporter:	Roman Kravcenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.9	OS:	Linux
Assigned to:	John Russell	CPU Architecture:	Any

Description:
According to manual:

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. Because a consistent read does not set any locks on the tables it accesses, other sessions are free to modify those tables while a consistent read is being performed on the table.

This isn't the case for UPDATE ... SELECT statement in REPEATABLE READ isolation mode. This creates way too many unnecesary locks.

How to repeat:
mysql> create table t1 (id int primary key, a int) engine=innodb;
mysql> create table t2 (id int primary key, b int) engine=innodb;

mysql> insert into t1 values (1,0), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0), (8,0), (9,0), (10,0);
mysql> insert into t2 values (1,1), (2,4), (3,5), (4,6), (5,7);

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

mysql> begin;
mysql> update t1 set a=a+1 where id in (select b from t2 where b > 4);

-- This creates 17 locks, even locks on t2 table.
-- Equivalent SELECT; UPDATE statement in REPEATABLE READ mode result in 3 locks on t1 table.
-- There Details:

---TRANSACTION A364D36, ACTIVE 10 sec, process no 23300, OS thread id 139852442859280
4 lock struct(s), heap size 1248, 17 row lock(s), undo log entries 3
MySQL thread id 124000, query id 1810645 localhost root
TABLE LOCK table `test`.`t1` trx id A364D36 lock mode IX
RECORD LOCKS space id 0 page no 281 n bits 80 index `PRIMARY` of table `test`.`t1` trx id A364D36 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab0084; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab0091; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab009e; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab00ab; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000a364d36; asc    6M6;;
 2: len 7; hex 0200000b032820; asc      ( ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000a364d36; asc    6M6;;
 2: len 7; hex 0200000b032842; asc      (B;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 00000a364d36; asc    6M6;;
 2: len 7; hex 0200000b032864; asc      (d;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab00df; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab00ec; asc        ;;
 3: len 4; hex 80000000; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000a364cf1; asc    6L ;;
 2: len 7; hex c9000098ab00f9; asc        ;;
 3: len 4; hex 80000000; asc     ;;

TABLE LOCK table `test`.`t2` trx id A364D36 lock mode IS
RECORD LOCKS space id 0 page no 283 n bits 72 index `PRIMARY` of table `test`.`t2` trx id A364D36 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000a364cf2; asc    6L ;;
 2: len 7; hex ca000098b00084; asc        ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000a364cf2; asc    6L ;;
 2: len 7; hex ca000098b00091; asc        ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000a364cf2; asc    6L ;;
 2: len 7; hex ca000098b0009e; asc        ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000a364cf2; asc    6L ;;
 2: len 7; hex ca000098b000ab; asc        ;;
 3: len 4; hex 80000006; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000a364cf2; asc    6L ;;
 2: len 7; hex ca000098b000b8; asc        ;;
 3: len 4; hex 80000007; asc     ;;

----------------------------------------------------

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITED;

mysql> begin;

mysql> update t1 set a=a+1 where id in (select b from t2 where b > 4);

---TRANSACTION A364E35, ACTIVE 7 sec, process no 23300, OS thread id 139852442859280
2 lock struct(s), heap size 376, 3 row lock(s), undo log entries 3
MySQL thread id 124000, query id 1811219 localhost root
TABLE LOCK table `test`.`t1` trx id A364E35 lock mode IX
RECORD LOCKS space id 0 page no 281 n bits 80 index `PRIMARY` of table `test`.`t1` trx id A364E35 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000a364e35; asc    6N5;;
 2: len 7; hex 4e0000003e1e5e; asc N   > ^;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000a364e35; asc    6N5;;
 2: len 7; hex 4e0000003e1e80; asc N   >  ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 00000a364e35; asc    6N5;;
 2: len 7; hex 4e0000003e1ea2; asc N   >  ;;
 3: len 4; hex 80000001; asc     ;;

Suggested fix:
Using plain SELECT before UPDATE or SET TRANSACTION ISOLATION LEVEL READ COMMITED;
Solve this problem resulting in only 3 locks.

In our case, this results in constant deadlocks if we try to update t2 at the same time.

While our manual, http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html, indeed does not describe this specific case of UPDATE ... WHERE col in (SELECT ...), the same logic as for REPLACE applies:

"For REPLACE INTO T SELECT ... FROM S WHERE ..., InnoDB sets shared next-key locks on rows from S."

This is needed for correct binary logging and replication:

"If the transaction isolation level is READ COMMITTED or innodb_locks_unsafe_for_binlog is enabled, and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally."

We see these S locks in your case, this is intended behavior for the reasons described above.

At best I see a documentation request to include your specific case of UPDATE (or DELETE) with subquery into the manual.

Thanks for clarification. I think I missed "REPLACE INTO T SELECT ... FROM S WHERE ..." part.

Also http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html, states the following:

InnoDB uses a consistent read for select in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK 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 on rows read from the 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.

I think I missed the point in the "Otherwise ... SELECT part acts like READ COMMITTED" part too.

If the documentation could be improved, it'll definitely help. Locking and transactions part are pretty hard one to understand.

Thank you for support.

I agree that http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html (and similar pages for older versions) needs clarification for the case of UPDATE ... (SELECT ...).

Updated the wording in 5.1, 5.5, and 5.6 manuals for both locations mentioned in this bug:

The type of read varies for selects in clauses like INSERT INTO ...
SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not
specify FOR UPDATE or LOCK IN SHARE MODE:

* By default, 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.

* To use a consistent read in such cases,
enable the innodb_locks_unsafe_for_binlog option and set the
isolation level of the transaction to READ UNCOMMITTED, READ
COMMITTED, or REPEATABLE READ (that is, anything other than
SERIALIZABLE). In this case, no locks are set on rows read from the
selected table. 

---

When a SELECT is used in the constructs REPLACE INTO t SELECT ...
FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s
...), InnoDB sets shared next-key locks on rows from table s.