about next-key locking
Posted by:
yu liang
Date: August 22, 2017 07:59PM
My table:
mysql> show create table a;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------
| a | CREATE TABLE `a` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `idx_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> select * from a;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
| 3 | 5 | 7 | 9 |
| 5 | 7 | 9 | 11 |
| 7 | 9 | 11 | 13 |
+---+------+------+------+
when I excute sql:
session1:
begin;
select * from a where c=9 for update
session2:
mysql> insert into a select 100,100,7,100;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into a select 100,100,8,100;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into a select 100,100,9,100;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into a select 100,100,10,100;
^C^C -- query aborted
mysql> insert into a select 100,100,11,100;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Why is the last statement inserted?
I think the interval should be (7,9],(9,11],but the interval in the example above seems to be [7,9],(9,11).
I don't understand why the intervals is that.