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 此时正常