【.com原稿】随着IT技术的飞速发展，各种技术层出不穷，让人眼花缭乱。尽管技术在不断变化，但有些技术仍然被几代IT人使用。来自PexelsMySQL的图片就是其中之一，它已经经历了多次迭代。数据库锁是MySQL数据引擎的一部分。今天我们将学习MySQL数据库锁及其优化。MySQL锁的分类当多个事务或进程访问同一个资源时，为了保证数据的一致性，需要锁机制。从锁定资源的角度来看，MySQL中的锁分为：表级锁行级锁页面锁表级锁：锁住整个表。开销小，锁定快；不会有僵局；加锁粒度大，锁冲突概率最高，并发度最低。行级锁：锁定一行记录。开销大，加锁慢；会有死锁；加锁粒度最小，锁冲突概率最低，并发度最高。页锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁粒度介于表锁和行锁之间，并发度一般。在实际开发过程中，主要使用到表级锁和行级锁两种。既然锁是针对资源的，那么这些资源就是数据，而MySQL提供了一个插件式的存储引擎来存储数据。插件式存储引擎的好处是开发者可以根据自己的需要选择合适的存储引擎。在众多的存储引擎中，有两个被广泛使用。它们是：MyISAM存储引擎，不支持事务和表锁设计，但支持全文索引，主要面向一些联机分析处理（OLAP）数据库应用。说白了，主要是查询数据，数据插入和更新操作比较少。支持事务的InnoDB存储引擎主要是为联机事务处理(OLTP)应用程序设计的。其特点是行锁设计，支持外键，支持类似Oracle的非锁读，即默认读操作不产生锁。简单来说就是多了数据的插入和更新操作。从MySQL数据库版本5.5.8开始，InnoDB存储引擎是默认的存储引擎。以上两种存储引擎在处理多进程数据操作时表现如何，就是我们接下来要讨论的内容。为了让整个描述更清晰，我们用表级锁、行级锁和InnoDB存储引擎MyISAM组成一个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默认设置）.注意：一个坑是一条行记录被删除后，只是被标记为“已删除”，其存储空间并没有被回收，也就是说没有被物理删除。数据被另一个进程异步删除。由于空间长度的问题，删除后的物理空间无法被新记录使用，从而形成空洞。③当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实现了行锁，它也可以实现表锁。该方法是意向锁（IntentionLocks）。这里有两种意向锁：意向共享锁（IS）：一个事务打算给一个数据行加一个行共享锁。事务在给一个数据行加共享锁之前，首先要获得该表的IS锁。意向排他锁（IX）：事务有意向数据行加排他锁。事务在给一个数据行加排它锁之前，必须先获得该表的IX锁。注意：意向共享锁和意向排他锁是数据库主动添加的，不需要我们手动处理。对于UPDATE、DELETE和INSERT语句，InnoDB会自动为数据集添加排他锁。InnoDB表锁的实现：假设有一张表test2，有两个字段id和name。不设置主键，不设置索引（index）如下：InnoDB表锁实现示意图InnoDB存储引擎和行级锁第四象限我们用的多讨论的多：2*2表行锁，MyISAM，InnoDB示意图图-第四象限InnoDB存储引擎行锁，当对索引数据进行数据查询时，会用到行级锁。共享锁（S）：当一个事务读取一条记录时，它不会阻塞其他事务对同一条记录的读请求，但会阻塞它的写请求。当读锁被释放后，就会进行其他事务的写操作。例如：select...lockinsharemodeexclusivelock(X)：当一个事务写入一条记录时，会阻塞其他事务对同一张表的读写，只有释放锁后，其他事务才会读取和写写操作。例如：select...forupdate行锁的实现方法：假设有一张表test1，有id和name两个字段。id作为主键同时也是表的索引如下：InnoDB行锁实现图高并发情况下，多个事务同时请求更新数据，由于等待事务数增加资源被占用。这样会造成性能问题，可以通过innodb_lock_wait_timeout来解决。innodb_lock_wait_timeout是事务等待资源的最长时间，以秒为单位。如果在限定时间内没有分配资源，则返回申请失败。四种锁的兼容性：共享锁、排它锁、意向共享锁、意向排他锁Compatiblelegend如果一个事务请求的锁模式与当前锁兼容，InnoDB将把请求的锁授予该事务；否则，如果两者不兼容，事务将等待锁被释放。前面说了，gaplock就是锁定一条记录。当锁定一系列记录时，我们称之为间隙锁。当使用范围条件对数据进行索引时，InnoDB会锁定满足条件的数据索引项。对于键值在条件范围内但不存在的记录，称为“间隙（GAP）”，InnoDB也会锁定这个“间隙”，即间隙锁。间隙锁和行锁统称为（Next-Keylocks）。如果表中只有11条记录，它们的id的取值分别为1,2,...,10,11。如下SQL：Select*fromtable_gapwhereid>10forupdate;这是一个范围条件检索，InnoDB不仅会锁定id值为10的符合条件的记录，还会锁定id大于10的“gap”，即使大于10的记录不存在，比如12、13。目的InnoDB使用间隙锁：一方面是为了防止幻读。对于上面的例子，如果不使用间隙锁，其他事务插入任何id大于10的记录，本事务再次执行select语句，就会出现幻读。另一方面也是为了满足恢复和复制的需要。GapLockDiagramDeadlock两个事务需要获得对方持有的独占锁才能继续完成任务。这种等待对方释放资源的情况就是死锁。死锁图检测死锁：InnoDB存储引擎可以检测死锁循环依赖并立即返回错误。死锁恢复：发生死锁后，只有其中一个事务的部分或全部回滚才能打破死锁。InnoDB的做法是回滚持有最少行级独占锁的事务。在应用程序设计时必须考虑处理死锁，在大多数情况下，重新执行因死锁而回滚的事务就足够了。避免死锁：在事务开始时，如果有记录要修改，先用SELECT...FORUPDATE语句获取锁，即使这些修改语句在后面执行。在一个事务中，如果要更新一条记录，直接申请排他锁。而不是查询的时候申请共享锁，更新的时候申请排他锁。这样做会导致其他事务在申请排它锁的时候获取同一条记录的共享锁，导致锁冲突甚至死锁。简单来说，如果要更新一条记录，要做两步，第一步是查询，第二步是更新。第一步不要用共享锁，第二部用排他锁，第一步拿排他锁就可以抢占先机。如果事务需要加锁多张表，尽量使用顺序相同的加锁语句，减少死锁的机会。通过SELECT...LOCKINSHAREMODE（共享锁）获得该行的读锁后，如果当前事务需要更新记录，极有可能造成死锁。因此，如果要修改行记录，直接加排他锁。更改事务隔离级别（后面会详细介绍事务隔离级别）。查询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事务隔离级别的死锁是由于并发访问数据库造成的。当多个事务同时访问数据库并进行并发操作时，会出现以下问题。脏读是指一个事务在处理过程中读取了另一个事务未提交的数据。未提交的数据称为脏数据。脏读示例不可重复读（non-repeatableread），在事务范围内，多次查询一条记录，每次得到不同的结果。在第一个事务的两次读取之间，由于第二个事务的修改，第一个事务读取的数据可能不同。不可重复读示例幻读（phantomread）是在事务未独立执行时发生的一种现象。幻读示例数据库在同一时间点允许多个并发事务同时读写数据，会造成数据不一致。四个隔离级别，解决事务并发问题对比图隔离就是用来防止这种数据不一致的。事务隔离根据级别不同，从低到高依次为：未提交读（readuncommitted）：是最低的事务隔离级别。当一个事务还没有提交时，它所做的更改可以被其他事务看到。脏读的可能性。读提交（readcommitted）：确保一个事务在提交后只能被另一个事务读取。另一个事务无法读取此事务未提交的数据。可以避免脏读，但可能会出现不可重复读。可重复读（可重复读MySQL默认方法）：多次读取同一范围的数据会返回第一次查询的快照，即使其他事务更新和修改了数据。事务在执行过程中看到的数据必须是一致的。Serializable：是最可靠的事务隔离级别。“写”会加“独占锁”，“读”会加“共享锁”。当发生读写锁冲突时，后面访问的事务必须等待前面的事务完成，所以事务执行是串行的。可以避免脏读、不可重复读和幻读。InnoDB优化建议在锁机制的实现上，InnoDB的行级锁带来的性能损失可能会高于表级锁，但其并发处理能力远优于MyISAM的表级锁.这也是为什么大多数公司的MySQL都采用InnoDB模式的原因。然而，InnoDB也有脆弱的一面。这里有几个优化建议供大家参考：尽量通过索引进行数据检索，避免InnoDB因为无法通过索引加行锁而无法升级到表锁的情况。也就是说，多用行锁，少用表锁。添加索引时尽可能准确，以避免影响其他查询的不必要的锁定。尽量减少数据检索的范围（间隙锁），避免因间隙锁的影响而锁定不该锁定的记录。尽量控制事务的大小，减少锁定资源量和锁定时间。尽量使用较低级别的事务隔离来减少MySQL因为事务隔离而产生的开销。MySQL数据库锁思维导图总结MySQL锁主要分为表级锁和行级锁。MyISAM引擎使用表级锁。对于表级共享锁和独占锁，可以通过concurrent_insert和low_priority_updates参数进行优化。InnoDB支持表锁和行锁，根据索引如何选择。有行锁、行共享锁和行排它锁；有表锁，意向共享锁，意向排他锁，表锁是系统自己加的；锁范围是间隙锁。遇到死锁，我们如何检测、恢复以及如何避免。MySQL有四个事务级别：读未提交、读已提交、可重复读和序列化。它们的隔离级别依次增加。通过设置隔离级别，可以避免脏读、不可重复读和幻读。最后针对使用较多的InnoDB引擎提出一些优化建议。作者：崔浩简介：十六年开发架构经验。曾在惠普武汉交付中心担任技术专家、需求分析师、项目经理，后在一家初创公司担任技术/产品经理。善于学习，乐于分享。目前专注于技术架构和研发管理。【原创稿件，合作网站转载请注明原作者和出处为.com】
