Mysql锁详解

Mysql锁分类

锁从类别上分：表锁、行锁、页锁

表锁的模式分为：IS(意向共享锁)和IX(意向排它锁)

行锁的模式分为：S（共享锁）和X（排它锁）

实现的算法上：Record Lock，Gap Lock，Next Key Lock

1、表级锁

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 会发生在：MyISAM、memory、InnoDB、BDB 等存储引擎中。

（1）lock tables … read/write 表锁

可以用unlock tables主动释放锁，客户端断开的时候也会自动释放。

（2）mdl锁（meta data lock）元数据锁

不需要显示使用，在访问表的时候，会自动加上。

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如果一个mdl锁，申请时被阻塞住，那么之后的mdl锁就都会阻塞。

例如：给表加字段的场景。

给表加字段时，有时会很长，有时会导致整个库挂掉。

原因就在于此，因为加字段的语句就会申请一个mdl锁，如果在这个申请锁的动作之前，表在执行一个长事务，而且没有提交。那么它就是阻塞住，后面的任何操作也会阻塞。如果客户端在配有重试机制的话，超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。

那么如何给表加字段（加字段的时候，是会全表扫描的）

1）、去trx表，暂停或者kill掉长事务。

2）、如果要变更热点表，虽然表不大，但是上面的请求频繁，kill就很有可能没有用，这时最好就是alter table语句加上过期时间，如果在超时时间内可以拿到mdl写锁就可以操作，拿不到就先放弃，不影响后面的请求。之后，重复这个操作。

SET statement_timeout = 50;//先设置事务超时时间 
ALTER TABLE test add column whatever2 INT4;

2、行级锁

（1）开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。会发生在：InnoDB 存储引擎。(在存储引擎层，由各个引擎自己实现)

（2）在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

（3）对于更新频繁的行，可以通过调整语句的顺序来减少阻塞。建议将更新频繁的行相关的方法放在最后执行，这样更新行造成的阻塞不会影响到其他的事务。

（4）但是上面的方法只能减少一些阻塞的时间。死锁和死锁监测是innodb默认开启的

(innodb_deadlock_detect=on)，但是死锁监测非常的消耗CPU资源，它的过程是：

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的，这是一个时间复杂度是O(n)的操作。所以会发现有这样的情况，mysql挂了，CPU接近100%，但是每秒处理的事务只有100条。这就有可能是因为死锁监测造成的CPU消耗。

解决的方案：

1）、关闭死锁监测，如果业务可以保证一定不会发生死锁，可以暂时关闭死锁监测。

2）、控制并发度，把每行同时更新的数量降低，就可以很好的解决这个问题。可以将一行改成多行，比如收费记录，以前是一行，改成10行，记录由这10行相加来决定，这样再更新时，可以随机到一行去执行，那么并发量就是以前的1/10，这样可以避免死锁检测消耗过多的cpu。但是回滚的时候，需要做特殊的处理，需要找到修改的行。

3、页级锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。会发生在：BDB 存储引擎。

4、全局锁

Flush tables with read lock (FTWRL)

锁住整个库，整个库都是只读，常用于数据备份。备份时，禁止对库中的表进行更新等操作。

（1）对于innodb引擎，可以使用官方自带的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

对于不支持一致性读的引擎，就需要使用这个锁来进行备份了。

（2）set global readonly=true也可以设置库为只读，但是会有2方面的问题：

1）、在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，我不建议你使用。

2）、在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

二、InnoDB 中的锁

1、共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

innodb中s锁和x锁都是行锁。

 此时就会发生死锁的现象，T2的update在等待T1释放S锁，而T1的delete在等待T2释放X锁。

3、为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），意向锁是表锁，主要作用是提升存储引擎的性能

T1:
SELECT * FROM A WHERE id = 1 lock in share mode；（加S锁）

T2:
SELECT * FROM A WHERE id > 0 for update; (加X锁)

看上面这2个SQL事务，T1执行时候，对id=1这行加上了S锁，T2执行前，需要获取全表的更新锁进行判断，即：

step1：判断表A是否有表级锁

step2：判断表A每一行是否有行级锁

当数据量较大时候（我们一张表一般500-5000万数据），step2这种判断极其低效。

加了意向锁之后，step2就变成了对意向锁的判断。

（1）意向共享锁（IS）：事务计划给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。

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

（3）自增锁（AUTO-INC Locks）：特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。

InnoDB死锁

1、因为 InnoDB 是逐行加锁的，极容易产生死锁。那么死锁产生的四个条件是什么呢？

（1）互斥条件：一个资源每次只能被一个进程使用； 

（2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放； 

（3）不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺； 

（4）循环等待条件：多个进程之间形成的一种互相循环等待资源的关系。

2、在发生死锁时，InnoDB 存储引擎会自动检测，并且会自动回滚代价较小的事务来解决死锁问题。但很多时候一旦发生死锁，InnoDB 存储引擎的处理的效率是很低下的或者有时候根本解决不了问题，需要人为手动去解决。

3、既然死锁问题会导致严重的后果，那么在开发或者使用数据库的过程中，如何避免死锁的产生呢？这里给出一些建议：

（1）加锁顺序一致；

（2）尽量基于 primary 或 unique key 更新数据。（锁住的数据少）

（3）单次操作数据量不宜过多，涉及表尽量少。

（4）减少表上索引，减少锁定资源。

4、避免线上业务因死锁造成的不必要的影响建议。

（1）更新 SQL 的 where 条件时尽量用索引；

（2）加锁索引准确，缩小锁定范围；

（3）减少范围更新，尤其非主键/非唯一索引上的范围更新。

（4）控制事务大小，减少锁定数据量和锁定时间长度（innodb_row_lock_time_avg）。

（5）加锁顺序一致，尽可能一次性锁定所有所需的数据行。

一致性非锁定读

1、不需要等待访问的行上X锁的释放。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。

2、在事务隔离级别READ COMMITTED和REPEATABLE READ（InnoDB存储引擎的默认事务隔离级别）下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本

自增锁

1、这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

2、性能问题

首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。其次，对于INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

3、

（1）InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1。连续锁定模式，通过是用轻量级互斥锁来提升性能。

（2）innodb_autoinc_lock_mode为2时，自增值的产生通过互斥量，性能最好，可能带来的问题就是，自增值不连续，对于主从同步，基于sql的主从同步（statement-based replication）会有问题。所以使用这种自增策略时，主从复制应该使用基于行的复制（row-based replication）。

间隙锁

1、对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。

2、在默认的事务隔离级别下，即REPEATABLE READ下，避免了幻读问题（不可重复读），即查询时通过MVCC，获取开始时的事务；查询时使用Next-Key Lock避免了插入等操作的幻读。

3、InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在事务隔离级别READ COMMITTED下，其仅采用Record Lock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READ COMMITTED。

锁的问题

一、脏数据、脏读、脏页

1、脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。

2、脏数据

是指还没有提交的数据，如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。

3、脏读

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

4、在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在MySQL官方文档中将不可重复读的问题定义为Phantom Problem，即幻像问题。

丢失更新

即使在RP隔离级别下，也会发生丢失更新的问题。

1）事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。

2）事务T2也查询该行数据，并将取得的数据显示给终端用户User2。

3）User1修改这行记录，更新数据库并提交。

4）User2修改这行记录，更新数据库并提交。

这个过程会使User1的记录丢失。

解决的方案就是

1、乐观锁

2、悲观锁（分布式锁等）

用户User1在没有执行完操作前，其他事物不可以进行操作。

死锁

1、innodb通过wait for graph来主动监测死锁，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

2、而从1.2版本开始，对wait-for graph的死锁检测进行了优化，将递归用非递归的方式实现（深度优先算法），从而进一步提高了InnoDB存储引擎的性能。

3、InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。

乐观锁和悲观锁

1、乐观锁适合读多写少的场景，即冲突很少发生的时候，这样会省去锁的开销

2、悲观锁适合在写多读少的情况，发生冲突的可能性会很大，不断的进行重试反而会增加性能的开销。