An example about consistent read and next-key lock in InnoDB

  This article begins with a misunderstanding to discuss consistent read and next-key lock, and end up with a bug in MySQL optimizer. All bellow are based on the default isolation level, repeatable read, and innodb_locks_unsafe_for_binlog is off.

 

1、   Misunderstanding

        

1-1

         Look into session1, the transaction starts at the query “select … for update”, so the TRX_ID of session2’s transaction is larger than that of session1’s. But it looks “strange” that after the session2’s transaction committed, the query “select * from tb” can return the new row (200,200).

 

2、   Explanation

         Is it Phantom Read? As I understood before, the consistent view of session1’s transaction is generated at the first select query.

In fact, as described in manual page, there are statements as followed:

"All consistent reads within the same transaction read the snapshot established by the first such read in that transaction"

"Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a time point according to which your query sees the database. "

The “ordinary SELECT statement” is pointed specially, it means that the “select … for update” statement will not generate consistent view, which is in fact generated in the “select * from b”. So it is normal that we can see the (200,200) here.

 

3、   Verification

As verification, we can insert another (201, 201) in session2 now, and the next “select * from tb” will not return (201,201).

Another clue comes from the command “show engine InnoDB status”.

 

insert into tb values(10,10),(11,11),(13,13),(20,20);

begin;

select * from tb force index a where a>15 and a<18 for update;

show engine InnoDB status \G   ------------ TRANSACTIONS ------------ Trx id counter 1E82110B Purge done for trx's n:o < 1E821107 undo n:o < 0 History list length 8 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E82110A, ACTIVE 2 sec, process no 5438, OS thread id 1311758656 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 16, query id 757 localhost root show engine InnoDB status

select * from tb;

show engine InnoDB status \G   ------------ TRANSACTIONS ------------ Trx id counter 1E82110B Purge done for trx's n:o < 1E821107 undo n:o < 0 History list length 8 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E82110A, ACTIVE 29 sec, process no 5438, OS thread id 1311758656 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 16, query id 759 localhost root show engine InnoDB status Trx read view will not see trx with id >= 1E82110B, sees < 1E82110B

 

Till the second execution of “show engine InnoDB status”, there is “Trx read view” generated. The consistent view id is not 1E82110A but 1E82110B.

A “for update” statement is not “ordinary select query”, so no consistent view is generated, the same results when run “select … lock in share mod” and update statements.

If I want to create the consistent view at the beginning of the transaction, “START TRANSACTION WITH CONSISTENT SNAPSHOT” can be issued.

 

4、   Next-Key Lock

a)         Locked When Insertion

       If we want to offer a simpler case to tell about this, perhaps we can prefer to drop the column b, and make the table as “create table tb (a int key) engine=InnoDB” here. Then you will find that inserting (200) is locked in session2.

        

 

4-1

What happens and why?

 

b)        Next-Key Lock

We must mention a concept that named next-key lock in InnoDB. Look into the next case first.

 

4-2

The insertion is locked here, what is the difference between this and the prior example? In the “for update” query, the “Gap” is locked. The definitions of the gaps are as follow:

(Negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

So the whole gap (13, 20] is locked by session1, it prevent session2 from inserting (19, 19) here. Certainly the (200,200) will not be locked here, because it is located in another gap.

 

c)         Rows that Locked

 

Then come back to the question that why the (200) can’t be inserted when there is only one column in the table.

We can look into the “show engine InnoDB status\G” again.

CREATE TABLE `tb` (   `a` int(11) NOT NULL DEFAULT '0',   PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;

Insert into tb values(10),(11),(13),(20);

begin;

select * from tb where a>15 and a<18 for update;

show engine InnoDB status\G   ------------ TRANSACTIONS ------------ Trx id counter 1E82112A Purge done for trx's n:o < 1E821121 undo n:o < 0 History list length 17 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E821129, ACTIVE 4 sec, process no 5438, OS thread id 1312815424 2 lock struct(s), heap size 376, 5 row lock(s)

 

That seems to be strange that there are “5 row locks”. In fact the optimizer decides it to be a full table scan, because the row number is so small.

As proof we can see Innodb_rows_read increases 4 here. The implicit “supremum” makes the row lock to 5.

So it can say that all the gaps are locked by the “select … for update” query, so any insertion will be prevent here.

We can insert (300),(301),(302)….(10000) into the table, can run the steps again, now the (200) can be inserted in session2.

So it seems that we reach a bug from a “fake bug” :)

 

5、   Reference

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

       http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html