mysql 锁
张亚京 2019-11-03
引言：
锁是计算机协调多个进程或者多个线程之间并发访问同一资源的机制。在数据库系统中，除了传统的计算机资源(CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性和有效性是所有数据库系统需要考虑的问题。锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来讲，锁对于数据库尤其重要，也更加复杂。
对于mysql来说，不同的引擎锁的实现方式不一样，所以需要根据不同数据库引擎来进行讨论。
1.MyISAM
锁分类
MyISAM 引擎的表锁有2种模式，读锁和写锁。
锁之间的关系
- 读锁之间：并行，不阻塞
- 读写锁之间：串行阻塞
- 写锁之间：串行阻塞
加锁时机
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
MyISAM存储引擎的读和写锁是互斥，读操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
2.InnoDB
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。
1.事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。
- 原性性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
- 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
- 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
2.并发事务带来的问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。
- 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题
- 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
- 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
- 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
3.事务隔离级别
在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。
一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
数据库的事务隔离级别越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的，同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了４个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己业务逻辑要求，通过选择不同的隔离级别来平衡＂隔离＂与＂并发＂的矛盾
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读（Read uncommitted） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交度（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（Repeatable read） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
4.锁分类：
InnoDB锁包括了共享锁和排他锁，同时为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
- 共享锁（s）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（Ｘ）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。
- 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
| 当前锁模式/是否兼容 | X | IX | S | IS |
|---|---|---|---|---|
| X(排他锁) | 冲突 | 冲突 | 冲突 | 冲突 |
| S(共享锁) | 冲突 | 冲突 | 兼容 | 兼容 |
| IX(意向排他锁) | 冲突 | 兼容 | 冲突 | 兼容 |
| IS(意向共享锁) | 冲突 | 兼容 | 兼容 | 兼容 |
4.1.间隙锁（Next-Key锁）：影响并发insert的锁
键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。举例来说，假如有如下数据表：
| Id | Name | Age |
|---|---|---|
| 1 | p1 | 10 |
| 4 | p2 | 18 |
| 8 | p3 | 12 |
这时候执行如下的sql语句：
select * from demo where id>1 for update;
这时候在数据库中会对id=4，8的记录添加行锁。同时会添加如下间隙锁：(2,4], (4,8],（8, +suprenum]，这种情况下如果要在数据库中insert id在间隙锁之间的记录是会被阻塞的。
4.1.1.范围查询间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁。
举例来说有表demo：
| Id | Name | Age |
|---|---|---|
| 1 | p1 | 10 |
| 2 | p2 | 18 |
| 3 | p3 | 12 |
开启第一个命令执行窗口执行for update操作：
mysql> set autocommit = 0; Query OK, 0 rows affected (0.01 秒) mysql> select * from demo where id>2 for update; +----+------+------+ | id | name | age | +----+------+------+ | 3 | p3 | 12 | | 4 | p4 | 11 | +----+------+------+ 2 行于数据集 (0.02 秒) mysql>
这是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为3，4的记录加锁，也会对id大于3（这些记录并不存在）的“间隙”加锁。
开启第二个命令窗口执行插入数据操作：
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.01 秒)
mysql> insert into demo(name,age) values('p5', 22);
Lock wait timeout exceeded; try restarting transaction可以看到这条sql执行会被阻塞，因为大于3的不存在的记录也被加上了间隙锁了，所以在执行插入时就执行不下去了，因为新增记录的id是大于3的。
4.1.2.等值记录不存在导致间隙锁
除了范围查询会使用间隙锁之外，对于等值查询而不存在的记录也会使用间隙锁，同样打开第一个命令窗口，去更新一条不存在的记录。
假如当前数据库记录如下：
| Id | Name | Age |
|---|---|---|
| 1 | p1 | 10 |
| 2 | p2 | 18 |
| 6 | p3 | 12 |
开启第一个命令执行窗口执行for update操作：
mysql> set autocommit = 0; Query OK, 0 rows affected (0.01 秒) mysql> update demo set name='pp' where id=3; Query OK, 0 rows affected (0.00 秒)
这时候去另一个命令窗口新增一条记录，可以看到这条记录一直阻塞到超时也没有执行，这是因为如果一个等值的记录不存在的情况下，mysql会使用间隙锁锁住它临近的前面和后面2条记录之间的所有记录。对于id=3的记录来说不存在，所以他会锁住id(2-6]的所有记录。
mysql> set autocommit = 0; Query OK, 0 rows affected (0.01 秒) mysql> insert into demo(id,name, age) values(4,'p5', 88); Lock wait timeout exceeded; try restarting transaction
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件，对于删除。
5.加锁时机：
在MVCC并发控制中，读操作可以分为2类。快照读和当前读，其中快照读读取的是可见版本，不加锁；而当前读读取的是最新版本，并且当前读返回的记录都会加锁，保证其他并发事务不能修改该记录。那么MVCC中那些语句会加锁，那些不加锁呢？
快照读：简单的select属于快照读，不需要加锁。
当前读：特殊的select操作（select for update），insert/update/delete属于当前读，都需要加锁。
6.什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。
- 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。
在InnoDB下 ，使用表锁要注意以下两点。
（１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
（２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。
注意：
InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB将使用表锁。
由于 MySQL 行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的，设计的时候要注意这一点
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引
检索值的数据类型与索引字段不同，虽然 MySQL 能够进行数据类型转换，但却不会使用索引，从而导致 InnoDB 使用表锁。通过用explain 检查两条SQL的执行计划
3.死锁
1.死锁的产生原因
所谓死锁是2个或者2个以上的并发进程在执行过程中因争夺资源而相互等待的现象，若无外力作用它们都将无法继续推进下去，此时称系统处于死锁状态或系统产生了死锁。这些永远相互等待的进程称为死锁进程。表级锁不会产生死锁，所以解决死锁主要还是针对于InnoDB。
死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序
2.常见的死锁案例：
1.不同表相同记录行锁冲突
这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。
| 事务A | 事务B |
|---|---|
| begin | begin |
| delete from t1 where id=1 | |
| update t2 set name='test' where id=2 | |
| update t2 set name='test' where id=2 | |
| delete from t1 where id=1 |
2.相同表记录行锁冲突
这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。
| 事务A | 事务B |
|---|---|
| begin | begin |
| update t2 set name='test' where id=1 | |
| update t2 set name='test' where id=2 | |
| update t2 set name='test' where id=2 | |
| update t2 set name='test' where id=1 |
3.不同索引锁冲突
这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[2,3,4,5]，这样就造成了死锁的可能性。
| 事务A | 事务B |
|---|---|
| update msg set message='订单' where token>'abc' | delete from msg where id>1 |
4. gap锁冲突
innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。
| 事务A | 事务B |
|---|---|
| begin | begin |
| update msg set message='订单' where token='asd' | |
| update msg set message='订单' where token='aaa' | |
| insert into msg values(null, 'aad', 'hello'); | |
| commit | insert into msg values(null, 'bsa', 'hello'); |
| commit |
3.如何尽可能避免死锁
- 以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。
- 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
- 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
- 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
总结
ＭyISAM注意点
（１）共享读锁（S）之间是兼容的，但共享读锁（S）和排他写锁（X）之间，以及排他写锁之间（X）是互斥的，也就是说读和写是串行的。
（２）在一定条件下，ＭyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。
（３）ＭyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（４）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，ＭyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。
InnoDB 注意点
（１）InnoDB的行销是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。
（３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
（４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
（５）锁冲突甚至死锁很难完全避免。
在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：
- 尽量使用较低的隔离级别
- 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
- 选择合理的事务大小，小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
- 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
- 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
间隙锁是针对insert而导致阻塞的锁，实际开发中应该尽量避免间隙锁的发生，对于范围查询以及等值查询的修改，尽量先去查询数据是否存在，存在再去执行更新和删除操作，否则可能会产生死锁。
锁、事务、隔离级别、acid之间的关系：
锁是解决并发的机制，事务是对一组sql处理单元的统称，ACID是事务的4个属性，隔离级别是对事务之间隔离程度的一种描述。
