1、前言
mysql有两种常见引擎。
InnoDB:支持事务。
MyISAM:不支持事务。
坑:曾经刚接触数据库的时候,没有区分InnoDB、MyISAM,在创建表的时候默认选择了MyISAM,造成在业务异常时,数据全都没有回滚。
2、InnoDB锁
2.1 共享锁(Share Locks)和排他锁(Exclusive Locks)
InnoDB通过两种类型的行级锁来实现标准的行锁。共享锁(Share Locks,简称S)和排他锁(Exclusive Locks,简称X)。
- S:允许事务拥有锁,去读取某一行数据
- X:允许事务拥有锁,去修改或删除某一行数据
如果事务T1在第r行上拥有来S锁,另一个事务T2在请求第r行锁的过程中,处理方式如下:
- 如果事务T2请求的是S锁,则会立即获得锁。T1和T2在第r行都拥有S锁。
- 如果事务T2请求的是X锁,则不会立即获得锁。
如果T1在第r行获取的是X锁,另一个事务T2在请求第r行的任一类型的锁时,都不会成功。T2必须等待T1释放锁才能获取到第r行的锁。
2.2 Intention Locks
InnoDB支持行锁、表锁共存。Intention Locks(意向锁)就是这种表现形式。但Intention Locks是一种表锁,它表明后面的行处理时,获取的行锁是S或X。
- Intention Share Lock(简称IS):表明一个事务想要获取某写行的共享锁
- Intention Exclusive Lock(简称IX):表明一个事务想要获取某些行的排他锁
比如:SELECT ... LOCK IN SHARE MODE创建IS锁,SELECT ... FOR UPDATE创建IX锁。
意向锁的处理协议如下:
- 在一个事务能获取表中某行的S锁之前,它必须首先获取表的IS锁,或者更强级别的锁
- 在一个事务能获取表中某行的X锁之前,它必须首先获取表的IX锁
如下是表锁的兼容情况(⚠️这里的X、S是指表锁,非前面提到的共享锁、排他锁。具体请自行查阅Lock Table Write/Read)
X | IX | S | IS | |
X | Conflict | Conflict | Conflict | Conflict |
IX | Conflict | Compatible | Conflict | Compatible |
S | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
多个事务在一个区间持有冲突的锁是合法的。事务A在区间持有gap S-lock,事务B在相同的区间持有gap X-lock。冲突锁合法的原因是:如果索引的某一行被删除,不同事务持有的该区间锁将会被合并。间隙锁只能起到阻止其他事务对某一块区间进行insert操作,不会影响其他事务去获取该区间锁。所以gap S-lock和gap X-lock具有相同的效果。
间隙锁是可以显示禁止的。当你将事务隔离级别设置为READ COMMITTED或使系统变量
innodb_locks_unsafe_for_binlog有效(现在是弃用的)。当进行这两种操作时,也会有一些其他的影响发生。当MYSQL检查了where条件之后,会释放不满足条件的Record locks;当执行UPDATE操作时,InnoDB发生‘半一致性’读操作,这样能把最新提交版本的数据给到MYSQL,如此MYSQL能判断这些行是否满足update的where条件。
2.5 Next-key Locks
Next-key锁是一个组合锁:索引记录上的记录锁 + 索引记录之前区间的区间锁。
InnoDB在检索索引时,在遇到的索引记录上添加S/X锁,因此行锁就是所以你记录锁。加在索引记录上的Next-key锁也影响着索引记录之前的区间。如果一个会话在索引记录=R的行上有S/X锁,那么其他会话不能在索引记录<R上插入记录。
假设一个索引包含的值是10,11,13和20,那么可能存在的Next-key锁包含一下区间:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
默认情况下,InnoDB的事务隔离级别是REPEATABLE READ,同时系统变量
innodb_locks_unsafe_for_binlog
是失效的,在这种情景下,使用next-key锁去检索数据时,可以阻止幻读。
2.6 Insert intention Locks
Insert intention Lock是一种在行插入之前,通过INSERT操作创建的区间锁。这个锁的动机是:在同一个区间,如果多个事务进行insert操作,只要插入的不是同一个坐标位置,彼此之间就不需要相互等待。
假设有个索引记录,索引值为4、7。不同的事务企图插入索引值5、6,在获取插入行的排他锁之前,先通过insert intention lock来锁定4-7这个区间,但并不相互阻塞,因为待插入的行不冲突。
2.7 AUTO-INC Locks
AUTO-INC是一个特殊的表锁,发生在包含AUTO_INCREMENT列的插入操作。如果一个事务在进行插入操作时,其他事务必须等待,以便获取连续的递增主键值。