Reference http://blog.csdn.net/zbszhangbosen/article/details/7434637#reply
Here are some additions:
(1) InnoDB default lock mode is Next-key locking
(2) In a clustered index, Next-key locking is automatically downgraded to record locking if the primary key has a uniqueness constraint (Unique,auto increment).
(3) All scanned record locks are locked due to transaction isolation and conformance requirements. For example: Update ... where/delete. Where/select from...lock in Share mode/select. From.. For update this is all Next-key lock.
(4) Note the selection of the optimizer. Include clustered and secondary indexes, and sometimes a full table scan instead of an index scan, the entire table (the clustered Index table) will be locked.
Record Lock: Records a lock, which is just a single line locked
Gap Lock: Interval lock, just lock a range (note that the interval is open interval, that is, not including the boundary value, as to why so defined?) InnoDB officially defined)
Next-key Lock:record lock+gap Lock, so Next-key lock is also half open half-closed interval, and is the nether Open, upper bound closed. (Why is it so defined?) InnoDB officially defined)
Here's an example from the manual to see what is Next-key lock. If the row of an index has 10,11,13,20
Then the possible Next-key lock includes:
(Infinity, 10]
(10,11]
(11,13]
(13,20]
(20, Infinity) (Why is infinity here not closed?) You don't get home in math ~ ~)
All right, now, by the example below:
Table Test
Mysql> Show create TABLE test;
+-------+------------------------------------------------------------------------------------------------------ --+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ --+
| Test | CREATE TABLE ' Test ' (
' A ' int (one) is not NULL,
PRIMARY KEY (' a ')
) Engine=innodb DEFAULT Charset=utf8 |
+-------+------------------------------------------------------------------------------------------------------ --+
1 row in Set (0.00 sec)
Mysql> select * from test;
+----+
| A |
+----+
| 11 |
| 12 |
| 13 |
| 14 |
+----+
4 rows in Set (0.00 sec)
Start the experiment:
A
Session 1:
mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)
mysql> Delete from test where a=11;
Query OK, 1 row Affected (0.00 sec)
Session 2:
mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)
mysql> INSERT INTO test values (10);
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT INTO test values (15);
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT INTO test values (9);
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT INTO test values (16);
Query OK, 1 row affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows Affected (0.00 sec)
OK, the above is expected because there is an index on a, then of course just lock one row, so the insertion of the other rows will not be blocked.
Then the next thing is interesting.
Two
Session 1 (same as previous session 1):
Delete from test where a=22;
Query OK, 0 rows affected (0.01 sec)
Session 2:
mysql> INSERT into test values (201);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT into test values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (9);
Query OK, 1 row Affected (0.00 sec)
From the above results, all the rows behind the a=11, that is, the interval (11, infinity) are locked. Let's not explain why, let's look at a situation:
Three
Session 1:
mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from test;
+----+
| A |
+----+
| 7 |
| 9 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
| 22 |
| 23 |
| 24 |
| 25 |
+----+
Rows in Set (0.00 sec)
mysql> Delete from test where a=21;
Query OK, 0 rows Affected (0.00 sec)
Session 2:
mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)
mysql> INSERT into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (26);
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT INTO test values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT INTO test values (6);
Query OK, 1 row affected (0.01 sec)
It can be seen from here that there is only [16,21] in the locked range.
With the previous three types of locking explanations, it is now possible to explain why, in the InnoDB table, delete from where. For scanned index record plus Next-key lock (specific what statement plus what lock can see the manual, also need to explain, row lock lock object is always index record, because the table in InnoDB is indexed).
In (a), the actual addition of Next-key lock is (11,11] so only a=11 this record is locked, all other insertions are not related.
In (b), because a=22 this record does not exist, and 22 than the table all the record value is large, so in InnoDB seems to lock the interval is (14, infinity). So after inserting 14 the values are prompted to be locked, and before 14 can be.
In (three) species, a=21 also does not exist, but in the table inside and around 21 are recorded, so here Next-key lock the interval is (15,21], so not within the range can be inserted.
So why Next-key Lock is the nether open interval, the upper bound closed interval? It doesn't matter, but it's a little bit weird for me, like
Delete Test where a > #-------1
Its Next-key lock is (11, Infinity)
Delete Test where a < #-------2
Its Next-key lock is (infinity, 10]
It's weird to feel this way, because the definition of Next-key lock in the manual:
Next-key Lock:this is a combination of a record lock on the index record and a gap lock on the Gapbefore the index record .
In 1 cases, if you follow the instructions in the manual, it would be a bit farfetched to record the lock and the gap before it. [Today again read the Official Handbook, is the previous own understanding is not in place, this before is right, because the InnoDB when the lock is all the records encountered during the scan will be locked, then for 1 that case, actually starting from 12 scanning, but because to ensure that the a> 11 of them are deleted, so they have to be scanned. The natural maximum is infinity, because this next-key lock is the record of infinity (this is a hypothetical record that represents a boundary) plus the Gap lock (11, Infinity) before it. So at any time Next-lock is the record lock plus a gap lock before the record
But as long as we can understand it: Record the lock---lock a single record, Interval lock---Lock an open interval, Next-key lock---the combination of the two before, and do not control what before.
In addition, although Next-key lock is often locked in an interval, it is also perfectly correct to understand that an interval may have only one element, so adding next-key locks in this case is called Delete from TB where key=x.
In addition, two points are also mentioned:
1. If the index is not used in our SQL statements, then the lock object will be all rows (but not the table lock), so it is important to build the index
2.next-key Lock is designed to prevent Phantom reads, and only Repeatable-read and the above isolation levels prevent phantom reads, so there is no next-key lock under the Read-committed isolation level.
InnoDB Next-key Lock resolution
