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

此时正常