Innodb的行级锁依赖于索引,如果条件字段上没有索引,将会锁定全表。
小实验
SESSION1:
mysql> create table t1 ( id int ) ;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t1 values(1 ), (2), ( 3) ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
SESSION2:
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set id = 4 where id = 1 ; #这时会给整个表加写锁!!因为没有索引
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SESSION1:
mysql> update t1 set id = 5 where id = 2 ;
出现等待。
SESSION2:
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
SESSION1:
阻塞消失。
Query OK, 1 row affected (14.17 sec)
Rows matched: 1 Changed: 1 Warnings: 0