奇怪的区间锁问题

来源：互联网发布：java 常量初始化编辑：程序博客网时间：2024/06/05 23:39
Session 1:mysql> select * from t1;+-----+------+------+| sn  | id   | info |+-----+------+------+| 235 |    1 | a1   || 236 |    2 | a2   || 237 |    3 | a3   || 238 |    4 | a4   || 239 |    5 | a5   || 240 |    6 | a6   || 241 |    7 | a7   || 242 |    8 | a8   || 243 |    9 | a9   || 244 |   10 | a10  || 245 |   15 | a15  |+-----+------+------+11 rows in set (0.00 sec)mysql> show index from t1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               || t1    |          1 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)Session 2:Database changedmysql> update t1 set id=400 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> update t1 set id=500 where id=5;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=600 where id=6;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=700 where id=7;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=800 where id=8;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=900 where id=9;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0/******把t1_idx1  换成unique index  继续测试:Session 1:mysql> create unique index t1_idx1 on t1(id);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> mysql> mysql> show index from t1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               || t1    |          0 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)mysql>  select * from t1 where id BETWEEN 5 and 7 for update;+-----+------+------+| sn  | id   | info |+-----+------+------+| 239 |    5 | a5   || 240 |    6 | a6   || 241 |    7 | a7   |+-----+------+------+3 rows in set (0.00 sec)Session 2:mysql> update t1 set id=400 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> update t1 set id=500 where id=5;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=600 where id=6;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=700 where id=7;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=800 where id=8;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update t1 set id=900 where id=9;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0创建唯一锁 仍旧锁住了5，6,7,8 4条记录
0 0