【网络seo公司】seo外包网络公司

作者:老羽      发布时间:2021-08-15      浏览量:11506
【51CTO。[原创稿件]随着IT技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新,但一些技术至今仍被几代 IT 人士所使用。图片来自 PexelsMySQL就是其中之一,它



【51CTO。[原创稿件]随着IT技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新,但一些技术至今仍被几代 IT 人士所使用。





图片来自 Pexels


MySQL就是其中之一,它经历了多次版本迭代。数据库锁是MySQL数据引擎的一部分,今天我们将学习MySQL数据库锁及其优化。


MySQL锁分类


当多个事务或进程访问同一个资源时,为了保证数据的一致性,需要一个锁机制。


从锁资源的角度来看,MySQL中的锁分为:


表级锁
行级锁
页面锁定


表级锁:锁定整个表。低开销,快速锁定; 没有死锁; 锁粒度大,锁冲突概率最高,并发最低。


行级锁:锁定一行记录。高开销,慢锁; 会出现死锁; 最小的锁粒度,最小的锁冲突概率,最高的并发。


页锁:开销和锁时间介于表锁和行锁之间; 会出现死锁; 锁粒度介于表锁和行锁之间,并发度一般。


在实际开发过程中,主要使用表级锁和行级锁两种。由于锁是针对资源的,这些资源就是数据,MySQL提供了插件存储引擎来存储数据。


插件存储引擎的优势在于开发者可以根据自己的需求选择合适的存储引擎。


在众多的存储引擎中,使用频率更高的有两个引擎。他们是:


MyISAM存储引擎,不支持事务,表锁设计,支持全文索引,主要针对一些在线分析处理(OLAP)数据库应用。说白了,主要是查询数据,插入和更新数据比较少。
InnoDB 存储引擎,支持事务,其设计目标主要针对在线事务处理(OLTP)应用。


其特点是行锁设计,支持外键,支持类似Oracle的非锁读,即默认读操作不产生锁。


简单来说就是数据插入和更新操作比较多。来自 MySQL 数据库 5.5。从版本 8 开始,InnoDB 存储引擎是默认的存储引擎。


以上两个存储引擎在处理多进程数据操作时的表现如何是我们接下来要讨论的问题。


为了让整个描述更清晰,我们结合表级锁、行级锁、MyISAM和InnoDB存储引擎形成了一个2*2的象限。





2*2表行锁、MyISAM、InnoDB图


由于MyISAM存储引擎不支持行级锁,实际上后面讨论的问题都会围绕着三象限的讨论。


从内容上看,InnoDB作为最常用的存储引擎有很多值得关注的问题和点,这也是本文的重点。


MyISAM 存储引擎和表级锁


首先看第一象限的内容:





2*2表行锁、MyISAM、InnoDB示意图-第一象限


MyISAM 存储引擎支持表级锁,支持两种锁模式:


MyISAM 表的读操作(共享锁)不会阻塞其他进程对同一张表的读请求,但会阻塞对它的写请求。当读锁被释放时,其他进程的写操作会被执行。
对 MyISAM 表的写操作(排他锁)会阻塞其他进程读写同一个表。当锁被释放时,会执行其他进程的读写操作。


MyISAM 优化建议


使用 MyISAM 存储引擎时。执行SQL语句会自动给SELECT语句加共享锁,给UDI(更新、删除、插入)操作加排他锁。


因为这个特性,当多个进程并发插入同一张表时,会因为排他锁而等待。


因此,可以通过配置 concurrent_insert 系统变量来控制并发插入行为。


① 当 concurrent_insert=0 时,不允许并发插入。


② 当 concurrent_insert=1 时,如果 MyISAM 表中没有空洞(即表中没有被删除的行),当一个进程读取该表时,另一个进程将记录插入到表尾(MySQL 默认设置) )。


注:ahole是某行记录被删除后,只被标记为“已删除”,其存储空间并没有被回收,即没有被物理删除。此数据被另一个进程异步删除。


由于空间长度问题,删除后的物理空间不能被新记录使用,从而形成一个洞。


③concurrent_insert=2时,无论MyISAM表是否有空洞,都允许在表尾并发插入记录。


如果插入数据时没有并发删除操作,可以尝试设置concurrent_insert为1。


反之,当插入数据且量大时有删除操作,即产生“空洞”时,需要设置concurrent_insert为2。


另外,当一个进程请求一个MyISAM表的读锁时,另一个进程也请求同一个表的写锁。


即使读请求先到,写请求晚到,写请求也会在读请求之前插入。因为 MySQL 的默认设置认为写请求比读请求更重要。


我们可以通过low_priority_updates来调整读写行为的优先级:


当数据库以读为主,首先要保证查询性能时,可以通过low_priority_updates=1设置读优先级高于写优先级。
主要写数据库时,不需要设置low_priority_updates参数。


InnoDB 存储引擎和表级锁


我们来看看第二象限的内容:





2*2表行锁、MyISAM、InnoDB示意图-第二象限


InnoDB 存储引擎表锁。当没有查询到数据表中的索引数据时,会进行表锁操作。


以上就是InnoDB实现了行锁,也可以实现表锁。方法是意图锁。


这里有两种意图锁:


意向共享锁(IS):事务打算给数据行加行共享锁,事务在给数据行加共享锁之前必须先获得表的IS锁。
意图排他锁(IX):事务打算给数据行加排他锁,事务在给数据行加排他锁之前必须先获得表的IX锁。


注意:意向共享锁和意向排他锁是数据库主动添加的,不需要我们手动处理。对于 UPDATE、DELETE 和 INSERT 语句,InnoDB 会自动给数据集添加排他锁。


InnoDB表锁实现:假设有一个表test2,有两个字段id和name。


没有设置主键,没有设置索引如下:





InnoDB 表锁实现图


InnoDB 存储引擎和行级锁


在第四象限中,我们使用的更多,讨论的内容更多:





2*2表行锁、MyISAM、InnoDB示意图-第四象限


InnoDB 存储引擎行锁,当对索引数据进行数据查询时,会使用行级锁。


共享锁(S):当一个事务读取一条记录时,不会阻塞其他事务对同一条记录的读请求,但会阻塞对该记录的写请求。当读锁被释放时,其他事务的写操作将被执行。


例如:选择。锁定共享模式


排他锁(X):当一个事务写一条记录时,会阻塞其他事务对同一张表的读写操作。当锁被释放时,会执行其他事务的读写操作。


例如:选择。进行更新


行锁的实现:假设有一个表test1,有两个字段id和name。


作为主键的id也是表的索引如下:





InnoDB 行锁实现图


在高并发的情况下,多个事务同时请求更新数据,因为资源被占用,等待更多的事务。


这样会造成性能问题,可以通过innodb_lock_wait_timeout解决。innodb_lock_wait_timeout 是事务等待获取资源的最长时间,单位为秒。如果超过时间没有分配资源,则返回申请失败。


四种锁的兼容性:





共享锁、排他锁、意向共享锁、意向排他锁兼容图例


如果事务请求的锁模式与当前锁兼容,InnoDB 将请求的锁授予该事务; 否则,如果两者不兼容,则事务等待锁被释放。


间隙锁


前面说过行锁就是锁定一条记录。当锁定一个范围内的记录时,我们称之为间隙锁。


使用范围条件索引数据时,InnoDB 会锁定符合条件的数据索引项。对于key值在条件范围内但不存在的记录,称为“Gap”。InnoDB 也会锁定这个“差距”。这是间隙锁。Gap lock 和 row lock 统称为(Next-Key lock)。


如果表中只有 11 条记录,它们的 id 值为 1、2 和。,10,11 以下 SQL:


select * from table_gapwhere id> 10 for update;


这是一个范围条件的搜索。InnoDB不仅会锁定id值为10的符合条件的记录,还会锁定id大于10的“gap”,即使大于10的记录不存在,比如12、13。


InnoDB 使用间隙锁的目的:


一方面防止幻读。对于上面的例子,如果没有使用gap lock,另一个事务插入任何id大于10的记录,这个事务再次执行select语句,就会发生幻读。
另一方面也是为了满足恢复和复制的需要。





间隙锁图


僵局


双方交易都需要获取对方持有的排他锁才能继续完成任务。这种等待对方释放资源的情况是死锁。





死锁图


死锁检测:InnoDB 存储引擎可以检测死锁的循环依赖并立即返回错误。


死锁恢复:死锁发生后,只有一个事务部分或全部回滚以打破死锁。


InnoDB 方法是回滚持有最少行级排他锁的事务。您必须在应用程序设计期间考虑处理死锁。大多数情况下,可以重新执行因死锁回滚的事务。


避免死锁:


事务开始时,如果有需要修改的记录,先用SELECT。FOR UPDATE 语句获取锁,即使这些修改语句稍后执行。
在事务中,如果要更新记录,直接申请排他锁。而不是查询时申请共享锁,更新时申请排他锁。


这样做会导致,在申请排他锁时,其他事务可能已经获得了同一条记录的共享锁,从而导致锁冲突甚至死锁。


简单的说,如果要更新记录,就要做两步,第一步是查询,第二步是更新。第一步不要使用共享锁,第二步不要使用排他锁。只需在第一步使用排他锁抓住机会。


如果事务需要锁多张表,尽量按同一个顺序使用locking语句,减少死锁的机会。
通过选择 。LOCK INSHARE MODE(共享锁)获取该行的读锁后,如果当前事务需要更新记录,很可能会造成死锁。所以,如果要修改行记录,直接上排他锁。
更改事务隔离级别(后面会详细介绍事务隔离级别)。


MySQL 锁状态查询


在实际开发中,数据被锁的问题在所难免,那么我们可以用什么方法来查询锁


可以通过两个变量查询表级锁:


table_locks_immediate,表级锁的数量。
table_locks_waited,数字显示表锁定和等待的次数。


可以通过以下变量查询行级锁:


innodb_row_lock_current_waits,当前等待的锁数。
innodb_row_lock_time(重要),系统自启动以来被锁定的总时间长度。
innodb_row_lock_time_avg(重要),每次等待花费的平均时间。
innodb_row_lock_time_max,从系统启动到现在的最长等待时间。
innodb_row_lock_waits(重要),从系统启动到现在等待的总次数。


MySQL 事务隔离级别


前面提到的死锁是并发访问数据库引起的。当多个事务同时访问数据库时,并发操作会出现以下问题。


脏读(dirty read),一个事务在处理过程中,读取另一个事务未提交的数据。未提交的数据称为脏数据。





脏读示例


Non-repeatable read(不可重复读),在事务范围内,多次查询一条记录,每次得到不同的结果。


第一个事务中两次读取的数据之间,由于第二个事务的修改,第一次事务中两次读取的数据可能不同。





不可重复的例子


幻读(phantom read)是事务没有独立执行时发生的现象。





幻读的例子


同一时间点,数据库允许多个并发事务,同时读写数据会造成数据不一致。





四种隔离级别解决事务并发对比图


隔离是用来防止这种数据不一致的。事务隔离因级别而异,从低到高包括:


读未提交(read uncommitted):是最低的事务隔离级别。当一个事务尚未提交时,它所做的更改可以被其他事务看到。脏读的可能性。
已提交读(读已提交):保证一个事物在提交后只能被另一个事务读取。另一个事务无法读取该事务未提交的数据。可以避免脏读的发生,但可能造成不可重复读。
可重复读(repeatable read MySQL默认方式):多次读取同一范围的数据会返回第一次查询的快照,即使其他事务更新了数据。交易执行过程中看到的数据必须前后一致。
Serializable:是最可靠的事务隔离级别。“写”会加“排他锁”,“读”会加“共享锁”。


当发生读写锁冲突时,后面访问的事务必须等待前面事务的执行完成,所以事务执行是串行的。可以避免脏读、不可重复读和幻读。


InnoDB 优化建议


在锁机制的实现上,InnoDB的行级锁带来的性能损失可能比表级锁高,但在并发性方面的处理能力远优于MyISAM的表级锁。这也是大多数公司对MySQL使用InnoDB模式的原因。


然而,InnoDB 也有脆弱的一面。以下是一些优化建议供您参考:


尽量通过索引来完成数据的检索,避免InnoDB因为不能通过索引添加行锁而升级为表锁。换句话说,多用行锁,少用表锁。
添加索引时尽量准确,避免不必要的锁影响其他查询。
尽量减少给予scope的数据检索(gap lock),避免gap lock的影响,锁定不应该加锁的记录。
尽量控制事务的大小,减少锁定资源量和锁定时间。
尝试使用较低级别的事务隔离来降低 MySQL 由于事务隔离而造成的成本。


总结





MySQL数据库锁的思维导图


MySQL锁主要分为表级锁和行级锁。MyISAM 引擎使用表级锁。对于表级共享锁和排他锁,可以通过 concurrent_insert 和 low_priority_updates 参数进行优化。


InnoDB 支持表锁和行锁,根据索引确定如何选择。有行锁、行共享锁和行排他锁; 表锁有意向共享锁和意向排他锁。表锁是系统自己加的; 锁定范围是间隙锁。当遇到死锁时,我们如何检测、恢复以及如何避免它。


MySQL 有四个事务级别:未提交读、已提交读、可重复读和序列化。它们的隔离级别依次增加。


通过设置隔离级别,可以避免脏读、不可重复读和幻读。最后对使用较多的InnoDB引擎提出一些优化建议。


作者:崔浩


简介:16年开发架构经验,曾在惠普武汉交付中心担任技术专家、需求分析师、项目经理,后在一家初创公司担任技术/产品经理。善于学习,乐于分享。目前专注于技术架构和研发管理。





【51CTO原稿,合作网站转载请注明51CTO原作者及出处。com】


【编辑推荐】


鸿蒙官方战略合作共建-鸿蒙科技社区
MongoDB 和 MySQL:如何选择
分享7个实用脚本-Oracle数据库游标数汇总
DBA 必备的 10 个免费数据库监控和查询工具
亚马逊彻底移除Oracle数据库:迁移完成
MySQL同步复制及高可用方案总结


【主编:电话:(010)68476606】



在线客服 在线客服 QQ咨询
返回顶部 返回顶部 返回顶部