---+-----+
1 row in set (0.02 sec)
mysql>
很明显,以上语句中,打开了事务,然后执行了一条SQL语句,在select 语句后边加了 for update
相当于加了排它锁(写锁),加了写锁以后,其他的事务就不能对它修改了!需要等待当前事务修改完提交之后才可以修改;
现在我们在窗口B执行相同的操作:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
-
注意到了吗,窗口B并没有数据出现,因为窗口A执行的时候加了排他锁,但是窗口A并没有提交事务,所以锁也没有得到释放,现在我们在窗口A提交事务:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1 | tom | 23 |
+----+----------+-----+
1 row in set (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
同时,窗口B出现了以下情况:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1 | tom | 23 |
+----+----------+-----+
1 row in set (4.34 sec)
mysql>
没错,因为窗口A提交了事务,释放的排他锁,所以窗口B获取到了数据并重新为该数据添加了排他锁,所以此时你在A窗口在重复之前操作的时候还是会阻塞,因为窗口B没有提交事务,也就是没有释放排他锁;
现在,我们在窗口A执行以下语句:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 2 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 2 | joey | 22 |
+----+----------+-----+
1 row in set (0.00 sec)
mysql>
有的同学可能会说,不对啊,我窗口B还没有提交事务,释放排他锁啊。
但是,大家注意看我的SQL语句,这次查的是id = 2的数据;
这是InnoDB的一大特性,我上面说了,InnoDB的行锁是基于索引的 ,因为此时我们的条件是基于主键的,而主键是自带索引的,所以加的是行锁,这个时候窗口A锁的是id = 2的这条数据,窗口B锁的是id = 1的这条数据,他们互不干扰;
表锁测试
现在,我们再来测试一下,没有索引,走表锁的情况;
我们上面有提过,InnoDB的行锁是基于索引,没有索引的话,锁住的就是整张表:
我们在窗口A输入执行以下操作:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 20 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 4 | William | 20 |
+----+----------+-----+
1 row in set (0.04 sec)
mysql>
大家注意,这次的条件是使用的age,但是age是没有索引的,所以我们在B窗口执行相同的操作:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 20 for update;
-
很清楚的能看到,窗口B处于阻塞状态,我们换个条件继续执行:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 22 for update;
-
同样,尽管查询的数据换成了age = 22,但是还是会阻塞住,也就证明看不是锁的行;
我们再来试试换一个列作为条件:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
-
同样的结果,我们现在在A窗口提交事务,再来看一下B窗口:
A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 20 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 4 | William | 20 |
+----+----------+-----+
1 row in set (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
B:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1 | tom | 23 |
+----+----------+-----+
1 row in set (0.00 sec)
mysql>
当窗口A提交事务后,也就释放了锁,这个时候窗口B获取到了锁,得到了数据,并锁住了id = 1的这一行数据;
联合索引测试
关于联合索引中,需要注意的一点就是最左匹配原则 ,说白了就是查询是否走了索引,如果走了索引,同样加的还是行锁,否则锁的还是表,下面我们来看一下。首先,我们需要把表中的username和age建一个联合索引:
mysql> create index index_username_age on user(username,age);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from user;
+-------+------------+--------------------+--------------+-------------+-