Implementation of the MySQL row lock

Tagged: mysql mysql lock MySQL gap MySQL gap lock MySQL Lock detailed

The way the MySQL lock is different depending on the isolation level, because the default isolation level is Repeatable-read repeatable read, we generally understand that the implementation of the MySQL lock is a row lock, row lock is achieved by the implementation of the index, MySQL support isolation level of four kinds, this online many introduction, The most common use is read-committed and Repeatable-read two, today on the two isolation level under the implementation of the lock to do a comparison

First we will test with the isolation level of the default level repeatable-read:

mysql> CREATE TABLE ' T1 ' (

' id ' int (one) not NULL auto_increment,

' Age1 ' int (one) DEFAULT NULL,

' Age2 ' int (one) DEFAULT NULL,

-PRIMARY KEY (' id '),

KEY ' Age1 ' (' Age1 ')

) Engine=innodb;

Mysql> select * from T1;

+----+------+------+

| ID | Age1 | Age2 |

+----+------+------+

|    1 |    1 | 2 |

|    2 |    1 | 2 |

|    3 |    1 | 2 |

|    4 |    1 | 4 |

|    5 |    1 | 2 |

|    6 |    2 | 3 |

+----+------+------+

Created a table T1, inserted part of the data, set up a secondary index of the Age1, now open two sessions to do the test:

Session1	Session2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and id=2; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and id=3; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
Rollback	Rollback

See this everyone crossing must feel no problem, it should be like this ah, the row lock is to use the index to find the corresponding row to lock the good, not urgent to continue testing

Session1	Session2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and age2=4; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
Rollback	Rollback

Hey............. What is this? This update is obviously not the same row why is it blocked? I'm going to use insert for the test.

Session1	Session2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=111 where age1=2; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT INTO T1 (age1,age2) values (1,33); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
Rollback	Rollback

.............. Access data and update data are not blocked in the same industry! Okay, look at the lock waiting message.

Mysql> SELECT * from Innodb_lock_waits \g;

1. Row ***************************

wait_started:2016-04-07 15:35:00

wait_age:00:00:08

Wait_age_secs:8

Locked_table: ' sbtest '. ' T1 '

locked_index:primary

Locked_type:record

waiting_trx_id:33333

waiting_trx_started:2016-04-07 15:35:00

waiting_trx_age:00:00:08

Waiting_trx_rows_locked:1

waiting_trx_rows_modified:0

Waiting_pid:3

waiting_query:update T1 set age2=55 where age1=1 and id=1

Waiting_lock_id:33333:33:3:2

waiting_lock_mode:x

.....................................

In order to save the section after the deletion, this is the above two update different rows are blocked when the record

Mysql> SELECT * from Innodb_lock_waits \g;

1. Row ***************************

wait_started:2016-04-07 15:40:43

Wait_age:00:00:26

Wait_age_secs:26

Locked_table: ' sbtest '. ' T1 '

Locked_index:age1

Locked_type:record

waiting_trx_id:33343

waiting_trx_started:2016-04-07 15:40:43

Waiting_trx_age:00:00:26

Waiting_trx_rows_locked:1

Waiting_trx_rows_modified:1

Waiting_pid:3

Waiting_query:insert into T1 (age1,age2) VALUES (1,33)

Waiting_lock_id:33343:33:4:7

Waiting_lock_mode:x,gap

.......................................

This is the insert time record, the first can be seen in the primary X lock, but according to the record we can query the two update of the primary key value should be different, Session1 ID should be 4,session2 directly specify the ID of 1, This is obviously not a line, and then look at the lock wait with the insert, the lock is on the Age1 index, but Lock_mode more a gap, what is this thing? All right, just scratching the scalp and looking in the official documentation:

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 rec Ord.

The original MSYQL has a gap lock mechanism, that is, two records directly to the seam to lock, or locked before and after recording, in the Repeatable-read isolation level of MySQL will have a gap lock algorithm, if the update scan, The secondary index has multiple records to satisfy the condition is that these records and records in the middle of the seam will be locked, and insert when the next record is to determine whether there is lock, so the above blocking occurs. This is MySQL in order to ensure that the isolation level of the repeatable read mechanism, but also affect the efficiency of business concurrency, you can modify the system parameters Innodb_locks_unsafe_for_binlog=on can be closed to change the gap lock mechanism. The value is off by default, and a gap lock can be avoided by modifying the transaction isolation level to read-committed. Change to Read-committed to try.

mysql> set global tx_isolation= ' read-committed ';

Query OK, 0 rows Affected (0.00 sec)

All session connections can be reconnected to take effect, now follow the steps above to test again:

Session1	Session2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and age2=4; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=55 where Age1=1 and id=1; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
Rollback	Rollback

This is no problem ....... .....

Session1	Session2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set age2=111 where age1=2; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT INTO T1 (age1,age2) values (1,33); Query OK, 1 row Affected (0.00 sec)
Rollback	Rollback

It's no problem ... it's not the same thing.

MySQL under the default isolation level Repeatable-read, there will be gap lock and Next-key lock two algorithm lock to ensure data repeatable read

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 Recor D

Next-key Lock is a range lock, and when we scan with an index range, the data within that range is locked to prevent phantom reads.

This article is from the "Yuanzhan" blog, make sure to keep this source http://xiaozhong991.blog.51cto.com/2354914/1761360

Implementation of the MySQL row lock