复习盘点-mysql锁知多少(表/行锁、共享读锁/独占写锁、共享锁/排他锁、间隙锁、意念锁)

  1. MySQL锁概论:
    Mysql的锁机制比较简单,其最显著的特定就是:不同存储引擎支持不同的锁机制!!!

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);

BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;

InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但是默认情况下采用行级锁。

那么什么叫做表级锁、行级锁、页面锁呢?

表级锁:开销小,加锁快;不会出现死锁;锁粒度大。发生冲突几率最大,并发度最低。
行级锁:开销大,加锁慢;会出现死锁,发生锁冲突几率最低,并发度最高。
页面锁:介于表级锁和行级锁之间。

那选用那种锁最好呢?
从上述特点来说,无法确切的说出那种锁更好,只能根据具体应用的特点来说那种最合适。

表级锁:更适合于查询为主的场景。
行级锁:更适合于大量按索引条件 并发更新 少量不同的数据,同时又有 并发查询 的应用。

1.1那么mysql何时使用MyISAM,什么时候使用InnoDB
INNODB和MyISAM是mysql数据库提供的两种存储引擎。INNODB会支持一些关系数据库的高级功能,如事务功能和行级锁;MyISAM不支持,但是MyISAM的性能更优,占用的空间少。
如果应用程序一定要使用事务,那么要选择INNODB引擎。但是INNODB的行级锁是有条件的。在where条件没有使用主键时,照样会锁住全表。比如delete from mytable
若是应用程序对查询性能要求高,就要使用MyISAM。MyISAM索引和数据是分开的,而且索引是压缩的,可以更好的利用内存。所以它的查询性能明细优于Innodb。MyISAM拥有全文索引的功能,这可以极大的优化like查询的效率。
InnoDB什么时候使用表锁,什么时候使用行锁?

事务需要更新大部分或者全部数据,表又比较大。如果使用默认的行锁,那么不仅事务效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高事务的执行速度。
事务设计到多个表,比较复杂,很可能造成死锁,造成大量事务回滚。也可以考虑一次性锁定事务涉及到的表,从而避免死锁,减少数据库事务回滚的开销。

当然应用中若是1,2比较多,那么就可以考虑使用MyISAM表了。

MyISAM和InnoDB的区别:

InnoDB支持事务和外键以及行级锁,MyISAM不支持。
MyISAM读性能优于InnoDB。
MyISAM索引和数据是分开的,而且索引是压缩的,而InnoDB索引和数据是紧密捆绑在一起的,无法压缩,所以InnoDB的体积比MyISAM庞大。
MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据类型,而是数据记录的地址。索引文件与数据文件分离,这样的索引称之:“非聚簇索引”。
InnoDB引擎索引结构的叶子节点的数据域,存放是就是实际的数据记录。这样的索引被称为“聚簇索引”,一个表只能有一个聚簇索引。
InnoDB并不保存表的具体行数,也就是说,执行select count() from table时,InnoDB要扫描整个表来计算有多少行,但是MyISAM只要简单读出保存好的行数即可。注意的是,当count()语句包含where条件时,两个表的操作是一样的。
InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQL不能确定扫描的范围,InnoDB表同样也会锁全表。
在where条件没有主键时,InnoDB照样会锁全表。

  1. MyISAM和MEMORY引擎的表锁
    MySQL表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。
    对于MyISAM表的读操作,不会阻塞其他用户对同一表的读操作。但会阻塞对同一个表的写操作。
    对于MyISAM表的写操作,则会阻塞其他用户对同一表的读操作和写操作。
    2.1 如何加表锁
    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,再执行查询操作。
    (update、delete、insert)前,会自动给涉及到的表加写锁。用户一般不需要显示加锁。
    2.2 如何优化MyISAM表锁
    对于MyISAM存储引擎,虽然使用表级锁加锁和解锁的开销最小,但是由于加锁粒度大,所以会造成更多的资源争用的情况。会较大程度上降低并发处理能力。
    优化MyISAM存储引擎关键是提高并发度。
    2.2.1 查询表级锁争用情况:
    使用show status like ‘table%’可以查询系统内部锁资源争用的情况。

表级优化

Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而等待的次数。
两个参数值都是系统启动后开始记录的,出现一次对应的事件,则数量加一。当Table_locks_waited状态值比较高时,那么说明系统中表级锁定争用现象比较明显。
2.2.2 缩短锁定时间:
如何让锁定时间尽可能的短呢?唯一的办法就是让我们的Query执行的时间尽可能短。

尽量减少大的复杂的sql,将复杂的query分拆成为小的query分步进行。
尽可能建立高效索引,让数据检索更迅速。
尽量让MyISAM存储引擎的表只存放必要信息,控制字段类型。
利用合适的机会优化MyISAM表的数据。

2.2.3 分离能并行的操作:
虽然MyISAM是读写相互阻塞的表锁,但是MyISAM存储引擎还有一个有用的特性,那就是ConcurrentInsert(并发插入)的特性。

当concurrent_insert=0时,此时读不能与insert写共存。
当concurrent_insert=1时,如果表中没有空数据块时,读可以与insert写共存,insert新增加的数据将插入到数据文件尾部。(默认设置)。
当concurrent_insert=2时,不论有没有空数据块,insert新增加的数据都将插入到数据文件尾部。

注:在myisam里,如果没有空的数据块,新增加的数据都会附加到数据文件的尾部,但是如果经常做update和delete操作,数据文件就不再是连续的,出现了许多空的数据块,此时再插入数据,按照缺省设置会先看看这些空的数据块是否能够容纳新插入的数据,如果可以则直接存储到这些空的数据块里,如果不行再直接插入到数据文件的尾部。MyISAM的默认的这种处理方式是为了减少数据文件的大小和碎片,以免造成IO性能问题。
如果我们要设置concurrent_insert=2,后面插入的数据都会直接追加到数据文件的尾部,而系统如果有频繁的delete和update操作,可能就会有过多的碎片造成过多的IO消耗,小鱼建议如果需要设置concurrent_insert=2一定要定期对表进行(优化)optimizer,以免造成过多的碎片。

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表的插入和查询操作。可以将concurrent_insert设置为2,总是允许并发插入;同时通过定期在系统空闲时段执行optimizer [‘ɑ:ptɪmaɪzər] table语句来整理空间碎片,

2.2.4 合理利用读写优先级:
MyISAM存储引擎的读写时相互阻塞的,但是,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一个表的写锁,MySQL如何处理呢?
默认情况下:写进程先获得锁,不仅如此,即便读请求先到锁等待队列,写请求后到,写锁也会插入到读锁之前。
这是由于Mysql的表级锁定对于读和写是有不同优先级设定的,默认情况下写的优先级要大于读的优先级。
所以可以利用各自系统的差异觉得写与读的优先级:
通过执行命令:set low_priority_updates=1 [praɪˈɒrəti],使该连接的读请求优先级比写的优先级高。如果系统是一个以读为主,可以设置改参数,否则,不需要设置。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
另外MySQL也提供了一种折中的方法调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定的获取锁的机会。
还有一点:一些需要长时间运行的查询操作,也会使得写进程“饿死”,因此,应用中尽量避免出现长时间运行的查询操作。
2.3 Innodb行锁
总的来说,InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁分为两种类型:共享锁和排他锁。而在锁定机制的实现过程中,为了让行级锁和表级锁共存,InnoDB也同样使用了意向锁(表级锁定)的概念,于是也就有了意向共享锁和意向排他锁两种。
当一个事务需要给自己需要的资源加锁时,如果遇到有一个共享锁正锁定自己需要的资源的时候,自己可以再加一个共享锁,不过不能加排他锁。但是,如果遇到自己需要的资源已经被排它锁占用之后,则只能等待该排他锁释放资源之后自己才能获取资源,并将其锁定。
2.3.1 什么叫做意向锁
可以说:Inoodb的锁定模式实际上可以分为四种:共享锁(S),排它锁(X),意向共享锁(IS),意向排他锁(IX)

首先,意向锁到底是做什么用的。
知乎的回答——InnoDB 的意向锁有什么作用?

首先,申请意向锁的动作是数据库完成的,也就是说,事务A申请一行行锁的时候,数据库会自动先开始申请表的意向锁。
那么到底意向锁有什么用?
首先,意向锁是——表级锁。
(1)事务A锁住了表中的一行,这一行只能读,不能写。【事务A加了共享锁】
(2)事务B申请整个表的写锁。【请注意:是整个表!!!】
(3)如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的共享锁时冲突的。【事务A不允许修改】
(4)数据库为避免这种冲突,怎么办的?就是让B的申请阻塞,直到A释放行锁。
解决方案:
step1:判断表是否已被其他事务用表锁锁住。
step2:判断表中每一行是否已经被行锁锁住。
请注意step2,需要遍历整个表。效率实在不高。
改进版:
step1:不变
step2:发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。

锁之间的兼容关系

假如一个表被加了意向排他锁(IX),证明此时有事务在修改表中具体某行的数据,那么对应的某行可能加了x锁,1.如果这时候其他事务要再加意向锁,那么可以加成功(因为加了意向锁之后,后续查询或者修改的是某行的数据,这行和上面的x锁未必冲突)所以意向锁之间是兼容的。2.如果此时其他事务加的是全表共享锁S,因为前面表中的数据正在被修改,所以S锁是加不成功的。所以意向排他锁和表共享锁是冲突的。

(敲黑板,划重点)意向锁的作用就是协调行锁和表锁之间的关系的,是将行锁从另一个角度提高到了表锁的等级(伪表锁),与表锁进行判断。
注意:select语句不是加锁!!!

意向锁是InnDB自动加的,不需要用户的干预。
对于update、delete和insert语句,Innodb会自动给涉及数据集加排他锁(X);对于普通的select语句,Innodb不会加任何锁!!!事务可以通过以下语句显式的给记录集加锁:
//共享锁
select * from table_name where … lock in share mode;
//排它锁
select * from table_name where … for update;

2.3.2 使用共享锁注意事项
使用 select … in share mode获取共享锁,主要用在数据依存关系时,确认某行记录是否存在,并且确保没有人对这个记录进行update或者delete操作。
(敲黑板,划重点)但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定记录后进行更新的应用,应该使用select…for update方式获得排他锁。
2.3.3 InnoDB行锁实现方式
InnoDB行锁是通过给索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
在实际开发中,要特别注意InnoDB这一特性,不然,可能造成大量的锁冲突,从而影响并发!!!
InnoDB使用索引的条件:

在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
mysql的行锁是针对索引加的锁。不是针对记录加的锁,虽然是访问不同的行,但是若是相同的索引,会出现锁锁冲突的。
当表中含有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行。
即使在条件中使用了索引,但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价决定的,如果MySQL认为全表扫描效率更高,比如很小的表,他也不会使用索引,此时InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突的时候,不要忘记检查SQL的执行计划,以确定是否真正使用了索引。

关于InnoDB到底是使用行锁还是表锁,我们需要依据索引来决定的,本质上行锁是针对索引加的锁,而非记录!!!虽然是访问不同的行,但是若是含有相同的索引,还是会发生锁冲突的!!!而且就算条件里面使用了索引,Mysql也不一定走索引,还是要看SQL的执行计划!!!

2.3 间隙锁

当我们用范围条件而不是相等条件检索数据的时候,并请求共享或者排他锁时,InnoDB会给符合条件的已有的数据记录的索引项加锁。

对于键值在条件范围内但是不存在的记录,叫做间隙(GAP)。InnoDB也会对这个“间隙”加锁,这种锁机制就是间隙锁(Next-Key锁)
InnoDB使用间隙锁的目的:

防止幻读,以满足相关隔离级别的要求;
满足恢复和复制的需要;

InnoDB的危害:
使用范围条件检索并锁定记录时,即使某些不存在的键值也会被无辜的锁定。而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些时刻可能会造成很大的危害。
在实际应用开发中,尤其是并发插入比较多的应用,尽量优化业务逻辑,尽量使用相等的条件来访问更新数据,避免使用范围条件。
需要特别说明的是:InnoDB除了通过范围条件加锁使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

  1. 死锁
    MyISAM表锁总是一次性获得所需的全部锁,要么全部满足,要不等待,因此不会出现死锁。
    InnoDB中,处理单个sql组成的事务外,锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能完成事务时,这种循环等待就是典型的死锁。
    在InnoDB的事务管理和锁机制中,有专门检测死锁的机制,会在系统产生死锁之后的很短的时间内就检测到死锁的存在。当InnoDB检测到系统产生了死锁之后,InnoDB会通过相应的判断来选出产生死锁两个事务中较小的事务回滚,而让较大的事务成功完成。
    但是需要注意,当产生死锁的场景涉及到的不只是InnoDB存储引擎的情况下,InnoDB是无法检测到死锁的,只能通过锁的超时限制参数InnoDB_lock_wait_timeout来解决。
    需要说明的是,这个参数并不是解决死锁问题的,而事故在并发高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重的性能问题。甚至拖垮数据库。我们可以通过设置合适的锁等待超时的阈值,避免这种情况的发送。
    通常来说,死锁都是应用设计的问题,我们可以通过:

若不同程序并发存取多个表,应约定以相同的顺序来访问表,这样可以降低死锁的机会。
在程序中以批量的方式处理数据时,如果事先对数据进行排序,保证每个线程按固定顺序处理记录,也可以大大降低死锁可能。
在事务中,如果要更新记录,应该申请足够级别的锁(排它锁),而不是先申请共享锁,更新时在申请排他锁。当用户申请排他锁时,其他事务可能已经获得了相同记录的共享锁,从而造成锁等待,甚至死锁。

文章参考:

https://blog.csdn.net/hxpjava1/article/details/79407961
https://blog.csdn.net/soonfly/article/details/70238902
https://blog.csdn.net/qq_36071795/article/details/83956125
http://www.dbaxiaoyu.com/archives/1547

作者:小胖学编程
链接:https://www.jianshu.com/p/95a0d10eb881
来源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。

打赏 支付宝打赏 微信打赏

未经允许不得转载!

评论列表 0

访客
取消