正文
gap锁 对于unique index 和Ununique index
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
Session 1:
mysql> select * from s100;
+-----+------+------+
| sn | id | info |
+-----+------+------+
| 227 | 1 | 1a |
| 228 | 3 | 3a |
| 229 | 6 | 6a |
| 230 | 9 | 9a |
| 231 | 12 | 12a |
| 232 | 15 | 15a |
| 233 | 18 | 18a |
+-----+------+------+
7 rows in set (0.00 sec) mysql> show index from s100;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| s100 | 0 | PRIMARY | 1 | sn | A | 7 | NULL | NULL | | BTREE | | |
| s100 | 1 | s100_idx1 | 1 | id | A | 7 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec) mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec) mysql> update s100 set info='bbb' where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 Session 2: mysql> insert into s100(id,info) select 13,'xxxxxxx'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql>
mysql>
mysql> insert into s100(id,info) select 12,'xxxxxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into s100(id,info) select 14,'xxxxxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into s100(id,info) select 15,'xxxxxxx';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 会从12 锁到14 改成unique index 呢？ Session 1:
mysql> show index from s100;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| s100 | 0 | PRIMARY | 1 | sn | A | 7 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec) mysql> create unique index s100_idx1 on s100(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> update s100 set info='bbb' where id=12;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0 mysql> show index from s100;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| s100 | 0 | PRIMARY | 1 | sn | A | 8 | NULL | NULL | | BTREE | | |
| s100 | 0 | s100_idx1 | 1 | id | A | 8 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec) Session 2: Database changed
mysql> insert into s100(id,info) select 13,'xxxxxxx';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 此时正常
