innoDB锁小结
innodb的锁分两类:lock和latch。
其中latch主要是保证并发线程操作临界资源的正确性,要求时间非常短,所以没有死锁检测机制。latch包括mutex(互斥量)和rwlock(读写锁)。
而lock是面向事务,操作(表、页、行)等对象,用来管理共享资源的并发访问,是有死锁检测机制的。
现在我们要着重讲的是innodb的lock锁,下面我们先来介绍几个概念。
行锁:innodb实现了多粒度锁,作用对象为表则为表锁,作用对象为行(Record)则为行锁。其中行锁包括共享行锁和排他行锁。
共享行锁(S):允许事务读取一行数据。
排他行锁(X):允许事务删除或更新一行数据。
意向锁:数据库需要对细粒度的对象上锁,需要首先给粗粒度的对象上锁。在粗粒度对象上上的锁成为意向锁。innodb的意向锁包括共享意向表锁和排他意向表锁。
共享意向表锁(IS):S锁对应IS锁
排他意向表锁(IX):X锁对应IS锁
锁兼容:兼容是指在同一对象上允许同种或不同种锁同时存在。
lock锁的分类
innodb实现了行级锁,包括行级共享锁(S)及行级排它锁(X),其中S和S是兼容的,其它都是不兼容的。所谓兼容是指对同一记录(row)锁的兼容性情况。innodb通过意向锁实现多粒度锁定。对innodb而言只有表意向共享锁(IS)和表意向排他锁(IX)。在给表加行锁前,需要先对该表加意向锁。因为意向锁是表级别的,而innodb的其它锁是行级别的,所以如果不是全表扫,意向锁是不会对堵塞其它请求的。(小编这里特地做了一个实验,发现select * from t1 where id=5 for update,此时id上面没有索引,走的是全表扫描,确实堵塞了其它任何请求。但给id加上索引,没有走全表时,就没有堵塞其它请求了。)各锁之间的兼容情况请看下图:
简记:含I的锁与含I的锁是相互兼容的
含I的S锁与不含I的S锁兼容
不含I的锁,含S的与含S的兼容与含X的不兼容
不含I的锁,含X的与任何锁都不兼容
如何查看锁
1、通过show engine innodb status\G;
2、直接查询试图 innodb_trx,innodb_locks,innodb_lock_waits;
关于读请求的锁
读请求可以分为两类:一致性锁定读(当前读)和一致性非锁定读(快照读)。
一致性锁定读是指读取数据的时候需要给表上加锁,有两种锁定形式:
select * from t1 for update;(加的是X锁)
select * from t1 in share mode;(加的是S锁)
而一致性非锁定读是指读取数据的时候不给表加锁,利用MVCC(multi version concurrency control)特性当读取数据时如果碰到对象已经上了X锁就直接读取镜像数据。又因为事务隔离级别的不同,在不同事务隔离级别下读取的镜像也会不同。
锁的算法
锁有三种算法:
Record Lock:单个记录上的锁
Gap Lock:间隙锁(锁定范围但不包含记录本身),没有找到数据, 左开,右开,
Next-Key Lock:锁定一个范围并且包括记录本身,Innodb对于行的查询都采用这种算法(为了解决幻读)。
但查询的索引含有唯一属性(主键或唯一索引)时,innodb存储引擎会对next-key lock进行优化,将其降级为Record Lock。即仅锁住索引本身,而不是范围。若是辅助索引则会分别对当前辅助索引及聚集索引加锁定。对聚集索引采用Record Lock锁定,而辅助索引则使用Next-Key Lock锁定。需要注意的是如果是等值更新,innodb会对辅助索引值与前后值构成的范围加上gap lock,而如果该辅助索引值不存在,则在该值所在区间上加上gap锁。区间的划分和辅助索引包含的键值有关,如一个辅助索引包含了{1,3,5},则对应的区间有(-∞,1),(1,3),(3,5),(5,+∞)。例如更新值为2,则锁定(1,3)这两个区间,而如果更新值为3则锁住(1,3),[3],(3,5)这个范围。如果是范围查询的话,则锁定的是该SQL涉及的范围内的记录和间隙。确切地说,其实辅助索引的叶子节点都包含了对应的聚集索引值,在使用gap锁划分区间的时候,其实是根据[辅助索引,聚集索引]组成的二维数组来划分的。
阻塞:
给一个对象加锁会阻塞其它对象对它的请求,innodb通过设置innodb_lock_wait_timeout来控制等待时间,并通过设置innodb_rollback_on_timeout来设置是否等待超时对事务进行回滚,默认不回滚,超过等待时间则抛出异常,由用户判断是该rollback还是commit。
死锁:
死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种相互等待的现象。死锁出现的概率是非常低的。innodb内置有死锁检查机制。当出现死锁时会自动回滚占用undo资源少的事务。死锁的检测除了超时还有wait-for graph,如果图中出现环形回路则表明存在死锁。
锁升级:
很多数据库如:SQL server就有锁升级的想象,但是innodb并没有锁升级。这是因为innodb根据事务访问的每个页对锁进行管理,采用位图方式,因此不管一个事务锁住页中的一行还是多个记录,其开销通常都是一样的。
锁涉及的三类问题:
脏读:读到未提交的数据(Read-ncommitted隔离级别);
不可重复读:(Read-committed);
丢失更新:避免丢失更新的方式就是给select ... from ... 加上 for update;
锁的常见的误区
误区一:select col1,col2 from table1 where col1='xxx' 或select count(*) from table1;会锁表;
事实上这样的select语句是不会对访问的资源加锁的。因为这样的查询会使用一致性非锁定读,它访问的是资源的镜像(此处用到的技术是mvcc即multi version concurrency control),所以不会堵塞其它事务也不会被其它事务堵塞(感兴趣的同学可以通过实验验证,不清楚实验方法的话可以私信我)。当然这只是一般的select语句。如果是如下这种格式的语句仍然会对访问的资源加锁:
select col1,col2 from table1 for update;(加X锁)
select col1,col2 from table1 lock in share mode;(加S锁)
关于什么是共享锁(S)什么是互斥锁(X),上面已经做了介绍。我们需要注意的是S锁和S锁是兼容的,S锁和X锁、X锁和X锁是不兼容的。这里说的兼容指的是不同事务对同一行(row)资源的访问的兼容性。显式加锁的select请求,会堵塞其它资源对该表的意向锁请求,从而堵塞其它请求。所以一般情况下,如无特殊需求,是不允许应用对select语句显示加锁的。
误区二:update table1 set col1='xxx' where col2='xxx'不走索引对数据库的性能没有多大影响;
RR隔离级别下,当使用update语句时,首先该语句会对它所访问的表加意向排它锁(IX),如果update语句走了索引,那么它会使用行锁(X) ,只锁定访问的记录及间隙(想了解更多可以百度MySQL锁的算法)。而如果它没有走索引,就会进行全表扫,这时会给整个表上记录加上排他锁(X)。这句SQL就会堵塞所有其它会话对该表的加锁请求,从而堵塞其它请求。但如果数据库开启了innodb_locks_unsafe_for_binlog,会触发semi-consistent read对不满足条件的记录会释放它上面的排它锁,同时不加gap锁。
RC隔离级别下,SQL会走聚集索引的全扫描过滤,由于过滤是在MySQL server层进行的。因为每条记录无论是否满足条件,都会被加上X锁。但是出于效率考虑,mysql对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足记录上的加锁/放锁动作不会省略(优化违背了2PL约束)。综上所述:使用当前读的SQL都必须要走索引
误区三:自增长键会在整个事务过程中,锁住自增长值;
现在我们数据库中的表的主键都是设为自增长的。很多同事认为,这样会不会非常影响数据库的插入效率。事实上使用自增长值确实会影响数据入库的效率,当时mysql 5.1.22版本后,对数据库的自增长设计做了很大的优化,性能已经得到了很大的提升。在5.1.22 之前的版本,使用的是auto_inc locking来生成自增长主键。它并不是在整个事务过程中锁住自增长资源而是在要生产主键的SQL执行完后就释放资源。这就是我们为什么会碰到,事务回滚后,自增长值确仍旧增大的原因。5.1.22及之后,使用了轻量级互斥量(mutex)来实现自增长,并通过inodb_atuoinc_lock_mode来控制自增长的模式。数据库默认该参数值为1,一般情况下都是用mutex来控制自增长,只有当bulk inserts的时候才会使用auto_inc locking模式。
误区四:使用外键加强约束,不会影响性能;
很多同事在设计表结构的时候喜欢使用外键,这里会给大家说明,为什么不建议大家使用外键。
如果使用外键,那么当子表需要更新或插入数据的时候会去检索父表。问题就出现在,检索父表的使用并不是使用的是一致性非锁定读,而是使用的一致性锁定读。
内部检索会是下面的格式:select * from parent where ... lock in share mode;这样的检索就会堵塞同时访问该父表的其它事务的请求,从而影响性能。
转自:
https://www.cnblogs.com/janehoo/p/5603983.html
---tips
InnoDB的行锁是通过给索引上的索引项加锁来实现的。
只有通过索引条件进行数据检索,InnoDB才使用行级锁,否则,InnoDB将使用表锁(锁住索引的所有记录, S,X, IS,IX)
意向锁的作用:
当事务想去进行锁表时,可以先判断意向锁是否存在,存在时则可快速返回该表不能启用表锁.
举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。事务B更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了。
说白了意向锁的主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”
记录锁,(Record), 间隙锁(Gap) ,临建锁(Next-key)
Next-key locks:
锁住记录+区间(左开右闭)
当sql执行按照索引进行数据的检索时,查询条件为范围查找(between and、<、>等)并有数据命中则此时SQL语句加上的锁为Next-key locks,锁住索引的记录+区间(左开右闭)
Gap locks:
锁住数据不存在的区间(左开右开)
当sql执行按照索引进行数据的检索时,查询条件的数据不存在,这时SQL语句加上的锁即为Gap locks,锁住索引不存在的区间(左开右开)
Record locks:
锁住具体的索引项当sql执行按照唯一性(Primary key、Unique key)索引进行数据的检索时,查询条件等值匹配且查询的数据是存在,这时SQL语句加上的锁即为记录锁Record locks,锁住具体的索引项
Gap锁只能存在RR(Repeat Read隔离级别), 作用就是为了防止幻读.. 其中Gap锁就是锁住的两条实际记录之间的空隙, 而不是记录本身.
Undo log
快照读:
SQL读取的数据是快照版本,也就是历史版本,普通的SELECT就是快照读
innodb快照读,数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成
当前读:
SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改
UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是
Redo log
Redo log指事务中操作的任何数据,将最新的数据备份到一个地方 (Redo Log)
Redo Log实现事务持久性:
防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候,根据redo log进行重做,从而达到事务的未入磁盘数据进行持久化这一特性。
一旦事务成功提交且数据持久化落盘之后,此时Redo log中的对应事务数据记录就失去了意义,所以Redo log的写入是日志文件循环写入的
指定Redo log日志文件组中的数量 innodb_log_files_in_group 默认为2
指定Redo log每一个日志文件最大存储量innodb_log_file_size 默认48M
指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默认16M
Redo buffer 持久化Redo log的策略,
Innodb_flush_log_at_trx_commit:
取值 0
每秒提交 Redo buffer --> Redo log OS cache -->flush cache to disk[可能丢失一秒内的事务数据]
取值 1 默认值,
每次事务提交执行Redo buffer --> Redo log OS cache -->flush cache to disk[最安全,性能最差的方式]
取值 2
每次事务提交执行Redo buffer --> Redo log OS cache 再每一秒执行 ->flush cache to disk操作
用户插入,更新, 删除操作, 在数据库中的执行流程
从图中,可以看到,一个Update操作的具体流程。当Update SQL被发给MySQL后,MySQL Server会根据where条件,读取第一条满足条件的记录,然后InnoDB引擎会将第一条记录返回,并加锁 (current read)。待MySQL Server收到这条加锁的记录之后,会再发起一个Update请求,更新这条记录。一条记录操作完成,再读取下一条记录,直至没有满足条件的记录为止。因此,Update操作内部,就包含了一个当前读。同理,Delete操作也一样。Insert操作会稍微有些不同,简单来说,就是Insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。
引申: 所以在RR加锁策略时需要锁定gap, 保证在同一个事务中读取下一条记录时, 不会有数据冲突.