排他锁、共享锁
对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题,需要使用MVCC或者加锁的方式解决它们。在使用加锁的方式解决问题时,由于既要允许读-读情况不受影响,又要使写-写、读-写或写-读情况中的操作相互阻塞,所以MySQL实现由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(Shared Lock,S Lock ) 和排他锁(Exclusive Lock , X Lock),又称读锁 (Read Lock) 和写锁 (Write Lock)。
- 读锁:也称共享锁、英文使用S表示。针对同一份数据,多个事务的读操作可以同时进行而不会互相影响,相互不阻塞的。
- 写锁:也称排他锁、英文使用X表示。当前写操作没有完成前,它会阻塞其他写锁和读锁。这样就能确保在给定的时间里,只要一个事务能执行写入,并防止其他用户读取正在写入的同一资源。
需要注意的是对于InnoDB引擎来说,读锁和写锁可以加在表上,也可以加载行上。
X锁 | S锁 | |
---|---|---|
X锁 | 不兼容 | 不兼容 |
S锁 | 不兼容 | 兼容 |
举例:读、写锁的使用场景模拟
场景1:事务1和事务2开启共享锁,事务3开启排他锁
事务1和事务2:开启共享锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user lock in share mode;
+--------+----+
| name | id |
+--------+----+
| 张三 | 1 |
| 李四 | 2 |
| 王五 | 3 |
+--------+----+
3 rows in set (0.00 sec)
事务3:开启排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user for update;
此时事务3进入阻塞状态,直到事务1和事务2提交为止
场景2:事务1和事务2同时开启写锁
事务1:开启排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user for update;
事务2:开启排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user for update;
事务2进入阻塞状态
场景3:事务1开启排他锁事务2开启共享锁
事务1:开启排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user for update;
事务2:开启共享锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user lock in share mode;
事务2进入阻塞状态
MySQL8.0新特性
在5.7及之前的版本,SELECT … FOR UPDATE ,如果获取不到锁,会一直等待,直到超时。在8.0版本中,添加SELECT … FROM TABLE FRO UPDATE NOWAIT、SKIP LOCKED语法,跳过锁等待,或者跳锁定
通过添加NOWAIT、SKIP LOCKED语法,能够立即返回。如果查询已经加了锁
- 那么NOWAIT会立即报错返回
- 而SKIP LOCKED也会立即返回,只是返回的结果中不包含被锁定的行。
mysql"> select name,id from user for update nowait;select name,id from user for update SKIP LOCKED;
意向锁 (intention lock)
InnoDB支持多粒度锁 (multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。
1、意向锁的存在就是为了协调行锁和表锁的关系,支持多粒度 (表锁与行锁) 的锁并存。
2、意向锁是一种不与行级锁冲突表级锁,这一点非常重要。
3、表明某个事务正在某些行持有了锁或该事务准备去持有锁。
意向锁分为两种:
-
意向共享锁 (IS):事务有意向对表中的某些行加共享锁 (S锁)
mysql">--事务要获取某些行的S锁,必须要先获得表得IS锁--- SELECT column FROM table ...... LOCK IN SHARE MODE;
-
意向排他锁 (IX):事务有意向对表中得某些行加排他锁 (X锁)
mysql">--事务要获取某些行的X锁,必须要先获得表得IX锁--- SELECT column FROM table ...... FOR UPDATE;
意向锁是由存储引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享/排他锁之前,InnoDB会先获取该数据行所在数据表的对应意向锁
意向锁解决的问题
现在有两个事务,分别是T1和T2,其中T2试图在该表级别上应用共享锁和排他锁,如果没有意向锁存在,那么T2就需要去检查各个页或行是否存在锁;如果存在锁,那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁,而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。
在数据表的场景中,**如果我们给某一行数据加上了排他锁,数据库会自动给更大一级的空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排他锁了,**这样当其他人想要获取数据表排他锁的时候,只需要了解是否有人已经获取了这个数据表的意向排他锁即可。
- 如果事务想要获取数据表中某些记录的共享锁,就需要在数据表上添加意向共享锁
- 如果事务想要获取数据表中某些记录的排他锁,就需要在数据表上添加意向排他锁
这时,意向锁会告诉其他事务已经有人锁定了表中的某些记录。
举例:意向锁的使用场景模拟
场景1:事务1开启意向排他锁,事务2尝试开启表锁
事务1:开启意向排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user where id = 1 for update;
+--------+----+
| name | id |
+--------+----+
| 张三 | 1 |
+--------+----+
1 row in set (0.00 sec)
事务2:尝试开启表锁
mysql">mysql> lock tables user read;
此时事务2开启表锁失败进入阻塞状态
场景2:事务1 和事务2同时对某条记录开启意向排他锁
事务1:开启意向排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user where id = 1 for update;
+--------+----+
| name | id |
+--------+----+
| 张三 | 1 |
+--------+----+
1 row in set (0.00 sec)
事务2:开启意向排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user where id = 1 for update;
此时事务2进入阻塞状态。原因并不是意向锁进行互斥,而且X锁进行互斥,所以事务2是阻塞状态。
场景3:意向排他锁是兼容的
事务1:开启意向排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user where id = 1 for update;
+--------+----+
| name | id |
+--------+----+
| 张三 | 1 |
+--------+----+
1 row in set (0.00 sec)
事务2:开启意向排他锁
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name,id from user where id = 3 for update;
+--------+----+
| name | id |
+--------+----+
| 王五 | 3 |
+--------+----+
1 row in set (0.00 sec)
可以看到两个意向排他锁是兼容的,没有进入阻塞状态。
从上面的案例可以得到如下结论:
- InnoDB支持多粒度锁,特定情境下,行级锁与表级锁共存。
- 意向锁之间互不排斥,但除了Is与S兼容外,意向锁会与共享锁/排他锁互斥。
- IS、IX是表级锁,不会和行级X,S发生冲突。只会和表级得S、X发生冲突。
- 意向锁在保证并发性的前提下,实现了行锁和表锁共存且满足事务隔离性的要求。
元数据锁 (MDL锁)
MySQL5.5引入了meta data lock ,简称DML锁,属于表锁范畴。MDL的作用是,保证读写的正确性。比如,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,增加一列,那么查询线程拿到的结构跟表结构对不上,肯定是不行的。
因此,当对一个表做增删改查时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性,解决了DML和DDL操作之间的一致性问题。不需要显示使用,在访问一个表的时候会自动加上。
举例:元数据锁的使用场景模拟
场景1:事务1查询数据并且不提交,事务2修改更改表结构操作
会话A:从表中查询数据
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name from user;
+--------+
| name |
+--------+
| 李四 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
会话B:进行DDL操作
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> alter table user add age int;
此时会话B会进入阻塞状态,直到会话A提交为止
场景2:读读操作是兼容的,事务1和事务2 同时开启查询数据,不会受影响
会话A:从表中查询数据
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name from user;
+--------+
| name |
+--------+
| 李四 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
会话B:从表中查询数据
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name from user;
+--------+
| name |
+--------+
| 李四 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
场景3:事务1开启查询操作,事务2进行DDL操作,事务3开启查询操作
会话A:从表中查询数据
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name from user;
+--------+
| name |
+--------+
| 李四 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
会话B:进行DDL操作
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> alter table user add id int;
会话C:开启查询操作
mysql">mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select name from user;
由于会话B进行DDL操作导致阻塞了,所以会话C也进入阻塞状态