MySQL- 技术专题 -MySQL 中的锁机制

为什么需要锁？

开发多用户、数据库驱动的应用系统，最大的一个难点：一方面就是要最大程度的利用数据库的并发访问，另一方面还要确保每个用户能一致的方式修改和读取数据。因此，有了锁。当然，这也是数据库系统区别于文件系统的特点。

保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要。

什么是锁？

锁是数据库系统区别文件系统的关键特性。锁机制用于管理对共享资源的并发访问。

Innodb 引擎会在行级别上对表数据进行上锁。不过存储引擎也会在数据库内部的其他多个地方使用锁，从而允许对多个不同资源提供并发访问。例如，操作缓存持中的 LRU 列表，删除，添加，移动 LRU 列表，为了保证一致性，必须要有锁介入。

这也间接了证明了数据库系统使用锁是为了支持共享资源进行并发访问，以达到提供数据的完整性和一致性的目的。同时，我们知道，由于 SQL 标准的存在，“导致”越来越多的数据库越来越像。但是其数据库锁的实现机制，不同的数据库是完全不一样，不存在你掌握了 MySQL 的锁实现，而 Oracle 也是类似实现的。此外，MySQL 中的 MyISAM 引擎使用的表锁的设计。并发情况下，读是没有问题的，但是并发插入的性能就要差一些。要是在底部插入的，其还是有一定的并发插入的。

MySQL 常见的存储引擎使用的锁机制

表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。

所以获取锁和释放锁的速度很快。由于表级锁定一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

使用表级锁定的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎。

MySQL 的表级锁有两种模式：

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁。

过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示锁 sql 语句

• 共享读锁：lock table tableName read

• 独占写锁：lock table tableName write

• 同时加多锁：lock table t1 write，t2 read

• 批量解锁：unlock tables

行级锁定特点就是锁定对象颗粒度很小，目前各大数据库管理软件所实现的锁定颗粒度最小的。

由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。

此外，行级锁定也最容易发生死锁。使用行级锁定的主要是 InnoDB 存储引擎。

InnoDB 的锁定模式实际上可以分为四种：

• 共享锁（S）

• 排他锁（X）

• 意向共享锁（IS）

• 意向排他锁（IX）

意向锁是 InnoDB 自动加的，不需用户干预：

对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)。

对于普通 SELECT 语句，InnoDB 不会加任何锁。

事务可以通过以下语句显示给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

用 SELECT ... IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT... FOR UPDATE 方式获得排他锁。

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。注意：一定要满足索引的条件才使用行锁

实际例子来加以说明：

在不通过索引条件查询的时候，InnoDB 确实使用的是表锁，而不是行锁。

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引。这种情况下 InnoDB 将使用表锁，而不是行锁。

因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。这和 SQL 优化的知识有关。

间隙锁（Next-Key 锁）

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

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。

假如 emp 表中只有 101 条记录，其 empid 的值分别是  1,2,...,100,101，下面的 SQL：

mysql> select * from emp where empid > 100 for update;

这是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101（这些记录并不存在）的“间隙”加锁。

InnoDB 使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求（关于事务的隔离级别）。对于上面的例子，要是不使用间隙锁，如果其他事务插入了 empid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读。

为了满足其恢复和复制的需要。很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

索引锁定的方式带来性能隐患

1、当 Query 无法利用索引的时候，InnoDB 会放弃使用行级别锁定而改用表级别的锁定，造成并发性

能的降低。

2、当 Query 使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该 Query 的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键。

3、当 Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。还要特别说明的是，InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁。

死锁

在 InnoDB 的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。

当 InnoDB 检测到系统中产生了死锁之后，InnoDB 会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。

如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免。

避免死锁的常用方法：

1、在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

2、在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

3、在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

4、在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT...FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功，程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题。

当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第三个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行 ROLLBACK 释放获得的排他锁。

什么时候使用表锁

对于 InnoDB 表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择 InnoDB 表的理由。

1、事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，可以考虑使用表锁来提高该事务的执行速度。

2、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

InnoDB 行锁优化建议

1、合理利用 InnoDB 的行级锁定，做到扬长避短

2、尽可能让所有的数据检索都通过索引来完成，从而避免 InnoDB 因为无法通过索引键加锁而升级为表级锁定。

3、合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。

4、尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。

5、尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

6、在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

由于 InnoDB 的行级锁定和事务性，所以肯定会产生死锁，减少死锁产生概率：

1、类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

总的来说，MySQL 这三种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用。而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

深入理解MySQL锁