共享与排他锁:
S锁:共享锁,允许其他事务并行读;禁止其他事务持有排它锁
X锁:排它锁,允许持有排它锁的事务对数据更新,禁止其他事务对数据持有共享锁或排它锁
注:普通的slct*fromusr属于快照读,不加任何锁。
意向锁:
在MySQL事务进行读写时,需要先对表加意向读写锁,意向锁也分为共享和排他锁,记为IS、IX。
Innodb的意向锁为表级别的锁,IX,IS是表级锁,不会和行级的X,S锁发生冲突,只会和表级的X,S发生冲突。
主要有两种意向锁:
意向共享锁(ISlock):事务想要获得一张表中某几行的共享锁,必须先获取该表的IS锁。
意向排他锁(IXLock):事务想要获得一张表中某几行的排他锁,必须先获得该表的IX锁。
记录锁:
即Rcord锁。对于主键和唯一索引(全部字段)的当前读,加Rcord锁,如下:
间隙锁:
即Gap锁,区间锁,仅仅锁住一个索引区间(开区间)。
在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。
对于非唯一索引的当前读,会加Gap锁,如下:
Nxt-Ky锁:
nxt-kylock=cord+gaplock,左开右闭区间,InnoDB使用nxt-kylock来避免幻读问题。
举例来说:
假设MySQL表数据如下:
当执行下面的语句时:
slct*fromtablwhsq_id=3lockinshamod;
加锁情况如下:
在sq_id=3,id=5记录上加Rcord锁;
在[1,4]~[3,5)区间加Gap锁
在[3,5]~[5,6)区间加Gap锁
如下图:
插入意向锁
插入意向锁是一种间隙锁形式的意向锁,(区别于IS、IX,他们是表级别的锁)。在真正执行INSERT操作之前设置。
insrt会在insrt的行对应的索引记录上加一个排它锁,这是一个Xcordlock,并没有gap,所以并不会阻塞其他sssion在gap间隙里插入记录。
不过在insrt操作之前,还会加一种锁,官方文档称它为insrtionintntiongaplock,也就是意向的gap锁。
这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他sssion就可完成,这样就使得insrt操作无须加真正的gaplock。
假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。
需要注意,对于insrt操作来说,如果发生了唯一索引冲突,则需要对冲突的唯一索引加上ShaRcordLock和GapLock,(即使是RC事务隔离级别)。
这个在并发插入时容易导致死锁,后面会分析。
nxt-ky锁和插入意向锁之间的兼容性:
Insrt操作涉及到的锁:
INSERT操作,在插入行之前会设置一个插入意向锁。如果该间隙已被加上了GAP锁或Nxt-Ky锁,则加锁失败进入等待;(注意:Gap锁是为了防止insrt,插入意向锁是为了insrt并发更快,两者是有区别的)
如果是简单INSERT操作,并且存在唯一主键,那么nxt-kylock退化为记录锁(即行锁)。
如果是INSERT...ONDUPLICATEKEYUPDAT会加上间隙锁。若再发生duplicat-ky错误的时候则需要执行UPDATE操作,对重复的主键值设置排它记录锁,对重复的唯一键值设置排它临键锁,还会加一个共享记录锁(S)。
并发insrt唯一键冲突死锁示例
表和数据准备:
并发插入:
死锁分析查看事务的锁情况:
SELECT*FROMINFORMATION_SCHEMA.data_locks;
利用showngininnodbstatus;命令来查看死锁日志.
关键:对于insrt操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上ShaRcordLock+GapLock。(即使是RC事务隔离级别)
我们从时间线维度分析:
事务T2insrtintot7(id,a)valus(26,10)语句insrt成功,持有a=10的X行锁(Xlockscbutnotgap);
事务T1insrtintot7(id,a)valus(30,10),因为T2的第一条insrt已经插入a=10的记录,事务T1的insrta=10则发生唯一约束冲突,需要申请对冲突的唯一索引a=10加上ShaRcordLock+GapLock(也即是lockmodSwaiting)这是一个间隙锁会申请锁住(4,10)之间的gap区域。从这里会发现,即使是RC事务隔离级别,也同样会存在Nxt-KyLock锁,从而阻塞并发。所以,此时事务T1持有(4,10)的GapLock,并且等待a=10上的shalock。
事务T2insrtintot7(id,a)valus(40,9)该语句插入的a=9,需要先获取插入意向Gap锁(4,10),的值在事务T1申请的gap锁(4,10)之间,故需事务T2的第二条insrt语句要等待事务T1的Gaplock锁释放,在日志中显示lock_modXlocksgapbfocinsrtintntionwaiting。所以,此时事务T2持有a=10上的Xlock,并且等待(4,10)的插入意向GapLock。
综上,产生死锁。
解决:
死锁后,InnoDB会选择资源最小的那个事务进行回滚。另外一个事务会执行成功,目前的解决方案是:
尽量不要有大事务,降低锁冲突的可能。
死锁回滚后,记录下原始SQL,手动处理。
死锁回滚记录原始SQL:
参考:
MySQL官方文档:docs.oracl.