案例一
表里面没有任何索引
- 创建表
mysql> delimiter //
mysql> -- 新建一个表,这个表没有任何索引
mysql> drop table if exists t1;
-> CREATE TABLE `t1` (
-> `id` int(11) , `name` char(20) ,
-> `age` int(11)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-> insert into t1(id,name)values(1,1),(2,2);
->
-> //
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
-> //
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
+------+------+------+
2 rows in set (0.00 sec)
- 在事务A开启一个事务
mysql> begin;select * from t1 where id=1 for update;
-> //
Query OK, 0 rows affected (0.00 sec)
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 1 | NULL |
+------+------+------+
1 row in set (0.00 sec)
- 在事务B中依次执行下面的语句
mysql> select* from t1 where id=1 for update;
-> //
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select* from t1 where id=2 for update;
-> //
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1(id, name, age) values (2, 1, 1);
mysql->//
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结论:在A事务中开启事务,在没有索引的情况下,在其他事务中执行的操作都失败了。
原因分析
InnoDB行级锁是通过给索引上的索引项加锁来实现的,这就导致只有通过索引条件检索数据,InnoDB才会使用行级锁,否则InnoDB将使用表级锁。