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 changed
mysql> update t1 set id=400 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> 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 transaction
mysql> update t1 set id=600 where id=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=700 where id=7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=800 where id=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 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: 0
mysql>
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: 0
mysql> 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 transaction
mysql> update t1 set id=600 where id=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=700 where id=7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t1 set id=800 where id=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 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条记录