MySQL锁详解

什么是锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。

为什么要用锁？

在数据库中，除了传统的计算资源（CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。当出现并发的时候，如果不做特殊控制的话，就会出现意外的事情，比如“脏“数据、修改丢失等问题。

因此如何保证数据库并发访问的一致性、有效性是所有数据库必须解决的一个问题。而目前比较常用的解决并发性问题的方案就是锁机制。

注意：

数据库并发需要使用事务来控制，事务并发问题需要锁来控制，所以锁跟并发控制和事务是联系在一起的。

加锁是消耗资源的，包括获得锁、检测锁是否已解除、释放锁等锁的各种操作。

锁的分类

锁的粒度

表级锁

页级锁

行级锁

MySQL 的锁机制比较简单，而且不同的存储引擎支持不同的锁机制。

MyISAM 和 Memory 存储引擎使用的是表级锁

InnoDB 存储引擎既支持行级锁，也支持表级锁，默认情况下使用行级锁

BDB 引擎使用的是页级锁，也支持表级锁（BDB 引擎基本已经成为历史）

MySQL 这 3 种锁的特性大致为：

• 表级锁：它直接锁住的是一个表。开销小，加锁快，不会出现死锁的情况；锁定粒度大，发生锁冲突的概率更高，并发度最低。
• 页级锁：它是锁住的是一个页。在 InnoDB 中是按页读取数据的，一个页为16KB，它的开销介于表级锁和行级锁中间，可能会出现死锁；锁定粒度也介于表级锁和行级锁中间，并发度也介于表级锁和行级锁中间。
• 行级锁：它直接锁住的是一条记录。开销大，加锁慢，会出现死锁；锁的粒度最小，发生锁冲突的概率最低，并发度很高。

从特点来说很难笼统的说哪种锁更好，只能就具体的业务特点来说哪种锁更合适。

从锁的角度来说，表级锁更加适合于以查询为主的应用，只有少量按照索引条件更新数据的应用，比如大多数的 web 应用；行级锁更适合大量按照索引条件并发更新少量不同的数据，同时还有并发查询的应用，比如一些在线事务处理系统，即 OLTP。

锁兼容性

共享锁

排它锁

• 共享锁（读锁 S）：其他事务可以读，但不能写。
• 排他锁（写锁 X） ：其他事务不能读，也不能写。

需要明确的是：

不论是共享锁还是排它锁，只有在开启事务的时候，才会有可读不可写或者不能读写的特性；

排它锁的其他事务不能读写，是指其他事务同样要上锁时，不能读写；如果开启事务，只运行一个不加锁的查询语句，是没有锁等待直接可以得到结果的。

锁模式的兼容情况：

如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。

InnoDB 锁类型

InnoDB 实现了以上两种类型的锁：共享锁和排它锁

InnoDB 加锁方法

意向锁是 InnoDB 自动加的， 不需用户干预。对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是**如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。**select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁。
• 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁、增删改操作，而是等待获得锁。select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候使用。

用 select ... lock in share mode来获得共享锁，主要用在数据依存关系时来确认某行记录是否存在，并确认没有人对这个记录进行 update 或者 delete 操 作。但是如果当前事务也需要对该记录进行更新操作时，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select... for update 来获得排他锁，它会拒绝其他事务在其上加其他锁。

MyISAM锁类型

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

需要明确的是：

MyISAM 锁与 InnoDB 锁不一样的地方是，MyISAM 锁无须与事务一块执行就可以起效，因为 MyISAM 是个非事务存储引擎，不支持事务。

MyISAM 读锁是只能读不能写，包括在当前加锁的进程中也是；区别在于在当前进程执行写操作会报错，其它进程执行写操作会进入锁等待。

MyISAM 写锁是当前进程可以读写，其它进程不能读写，只能进入锁等待。

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

锁模式的兼容情况：

MyISAM 加锁方法

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁；在执行更新操作 (UPDATE、DELETE、INSERT 等 ) 前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

• 读锁：lock table table_name read。 其他 session 仍然可以查询记录，并也可以对该记录加读锁。但是不能执行增删改操作，包括当前 session。
• 写锁：lock table table_name write。其他 session 不可以加锁，也不可以读写，只能进入锁等待；当前 session 可以进行增删改查操作。

给 MyISAM 表显示加锁，一般是为了在一定程度上模拟事务操作，实现对某一时间点多个表的一致性读取。

如：有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，就需要执行如下SQL：

select sum(total) from orders;
select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail 表可能已经发生了改变。因此，正确的方法应该是：

lock tables orders read local, order_detail read local;
select sum(total) from orders;
select sum(subtotal) from order_detail;
unlock tabels;

注意：

在用 lock tables 给表显示加表锁时，必须同时取得所有涉及表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 lock tables 后，只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

在用 lock tables时，不仅需要一次锁定用到的所有表，而且，同一个表在 SQL 语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错。如 lock table orders as a read, orders as b read;

MyISAM 并发插入

MyISAM 表的读和写是串行的，但在一定条件下，MyISAM 表也支持查询和插入的并发进行。

MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为 0、1 或 2。

当 concurrent_insert 设置为 0 时，不允许并发插入。

当 concurrent_insert 设置为 1 时，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。

当 concurrent_insert 设置为 2 时，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录。

MyISAM 的锁调度

MyISAM 存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一个表的写锁，MySQL 如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。这是 MySQL 认为写请求一般比读请求要重要。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。还有就是一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条 SELECT 语句来解决问题，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

MyISAM 调度行为的设置：

通过指定启动参数 low-priority-updates: 使 MyISAM 引擎默认给予读请求以优先的权利。

set low-priority-updates=1，使该连接发出的更新请求优先级降低。

通过 insert、update、delete 语句的 low-priority 属性，降低该语句的优先级。

加锁机制

悲观锁

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞，直到它拿到锁。关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。悲观锁比较适用于写多读少的情况(多写场景)。

乐观锁

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁比较适用于读多写少的情况(多读场景)。

锁模式

Record lock

Gap lock

Next-key lock

• **Record lock ** : 对单行索引项加锁。
• Gap lock : 作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。Gap 锁的目的是为了防止同一事务的两次当前读，出现幻读的情况。
• **Next-key lock ** : 前两种的组合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，但是表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”。

自增锁

自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况：如果一个事务正在向表中插入值，则任何其他事务必须等待，以便第一个事务插入的行接收连续的主键值。

我们一般把主键设置为AUTO_INCREMENT的列，默认情况下这个字段的值为0，InnoDB会在AUTO_INCREMENT修饰下的数据列所关联的索引末尾设置独占锁。在访问自增计数器时，InnoDB使用自增锁，但是锁定仅仅持续到当前SQL语句的末尾，而不是整个事务的结束，毕竟自增锁是表级别的锁，如果长期锁定会大大降低数据库的性能。由于是表锁，在使用期间，其他会话无法插入表中。

死锁

死锁产生的原因

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁。所以解决死锁主要还是针对于最常用的InnoDB 行锁。

注意：

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

死锁的现象

事务1事务2分别对id为1与id为2的数据进行排它锁加锁，随后进行交叉的数据修改。

事务运行顺序	事务1	事务2
①	begin;	begin;
②	select * from user where id = 1 for update;	select * from user where id = 2 for update;
③	update user set name= ‘222’ where id =2;	update user set name = ‘111’ where id =1;
④	commit;	commit;

运行结果：

死锁避免

• 以固定的顺序访问表和行。简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。

• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

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

• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

当前读

select lock in share mode (共享锁), select for update ; update, insert ,delete (排他锁) 这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读

不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是未提交读和串行化级别，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁。
对于一个快照来说，它能够读到哪些版本数据，要遵循以下规则：

• 当前事务内的更新，可以读到；
• 版本未提交，不能读到；
• 版本已提交，但是却在快照创建后提交的，不能读到；
• 版本已提交，且是在快照创建前提交的，可以读到；

参考文章1

参考文章2

参考书籍：《深入浅出MySQL》