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)?

Read about **`REPEATABLE READ`** in : [Transaction levels](http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html): `For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking **depends on whether the statement uses a unique index with a unique search condition**, or a range-type search condition. ...` — ypercubeᵀᴹ, Feb 19 '12 at 22:49

score 10 · Accepted Answer · answered Feb 24 '12 at 05:30

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

Good overview but I wonder how indexes fits into what you have written. Why presence of an index changes the behaviour in my example? — clime, Feb 24 '12 at 11:40
Next-key locking combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. — Somnath Muluk, Feb 24 '12 at 12:30

score 1 · Answer 2 · answered Feb 23 '12 at 17:34

1

ypercube has it right. Specifically, without a unique index that's used in the condition, it will lock more than the single row being affected.

To see the behavior you expect, change your table creation to this:

create table simple (col int unique) ENGINE=InnoDB;

The unique index on the col field will allow it to lock only the affected row.

answered Feb 23 '12 at 17:34

kbenson

1,464
9
13

As i have found out, there need to be an index but it does not need to be unique. – clime Feb 24 '12 at 11:29
Ah, when I read that it `uses a unique index with a unique search condition` I took that as a `unique` index, but they must have meant unique as in single, specific. – kbenson Feb 24 '12 at 19:34
What ypercube wrote is not (entirely) relevant. If there is a unique index and unique search condition, record locks are used instead of next-key locks. In my example, it does not matter if record locks or next-key locks are used because only gap that might be locked by the first update is that before 1 and the second update references 2. What is important is a presence of an(y) index because update does not lock only the target records, it _locks all the records it encounters during search for the target record_. No index => full scan => all the records locked. – clime Feb 24 '12 at 19:58
I'd say it's relevant, just not nearly as accurate. It paints in broad strokes what you've described in detail. Thanks for the update! – kbenson Feb 24 '12 at 22:48

score 0 · Answer 3 · answered Jun 16 '14 at 06:24

0

"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"

What are those certain locks which can be applied with select for update so that other sessions cannot read locked record?

answered Jun 16 '14 at 06:24

Ravi Sahu

890
1
11
24

Sorry, this is not an answer. Consider posting a separate question. – Beryllium Jun 16 '14 at 06:52
stalkoverflow people not allowing me to post it as a new question – Ravi Sahu Jun 16 '14 at 12:52
There is no such lock in mysql, see [this answer](http://stackoverflow.com/a/21792907/3576887) for a workaround – elipoultorak Dec 29 '15 at 08:22

score 0 · Answer 4 · edited Jul 21 '22 at 08:18

0

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. So in this case, you expect an index record lock, however, your index is not unique index, so your statement will be added an exclusive next-key lock .

edited Jul 21 '22 at 08:18

buddemat

4,552
14
29
49

answered Jul 20 '22 at 04:22

tian lan

21
2

inno db isolation levels and locking

4 Answers4

Linked

Related