inno db isolation levels and locking

Question

I am reading a manual about innodb transactions but still, there is lots of unclear stuff to me. For instance, I don't quite understand to the following behaviour:

-- client 1                             -- client 2
mysql> create table simple (col int) 
       engine=innodb; 

mysql> insert into simple values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into simple values(2);
Query OK, 1 row affected (0.00 sec)

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

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

mysql> update simple set col=10 where col=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

                                         mysql> update simple set col=42 where col=2;
                                         -- blocks

Now, the last update command (in the client 2) waits. I would expect the command to execute because I would suppose only the row 1 is locked. The behaviour is the same even if the second command in the client 2 is insert. Could anyone describe the locking background behind this example (where and why the locks)?

Answer 1

InnoDB sets specific types of locks as follows.

• 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. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.

• SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.

• For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.

• UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

InnoDB has several types of record-level locks:

• Record lock: This is a lock on an index record.

• Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

• Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

See More :

Avoiding the Phantom Problem Using Next-Key Locking

Avoiding deadlock