MySQL锁解决并发问题详解

蔚落 2022-09-25 00:20 127阅读 0赞

原文地址:[http://leihuang.org/2015/09/10/mysql-lock-concurrency/][http_leihuang.org_2015_09_10_mysql-lock-concurrency]

文章分为以下几个要点

1.  问题描述以及解决过程
2.  MySQL锁机制
3.  数据库加锁分析

下面讨论的都是基于[MySQL][]的InnoDB。

### 0. 问题描述以及解决过程 ###

因为涉及到公司利益问题，所以下面很多代码和[数据库][MySQL]信息，进行了缩减和修改，望见谅。

业务场景是优惠券系统规则规定了一个优惠券活动最多可发行多少张优惠券和每个用户最多可领取优惠券数量。

下面列出两张表的结构。

活动表

<code class="language-sql hljs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">CREATE TABLE `coupon_activity` (
 `act_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `act_code` char(6) NOT NULL DEFAULT '' COMMENT '活动编码',
 `coup_issue_num` int(11) NOT NULL DEFAULT '0' COMMENT '优惠券发行量',
 `coup_per_num` int(11) NOT NULL DEFAULT '0' COMMENT '单个用户可领取数',
 PRIMARY KEY (`act_id`),
 UNIQUE KEY `act_code_idx` (`act_code`) COMMENT '活动编码唯一索引'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

优惠券明细表

<code class="language-sql hljs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">CREATE TABLE `coupon_detail` (
 `coup_id` int(11) NOT NULL AUTO_INCREMENT,
 `act_code` char(6) NOT NULL DEFAULT '' COMMENT '活动编号',
 `coup_code` char(6) NOT NULL DEFAULT '' COMMENT '优惠券编码',
 `coup_user_id` int(11) NOT NULL DEFAULT '0' COMMENT '领取券用户id',
 PRIMARY KEY (`coup_id`),
 UNIQUE KEY `coup_code_idx` (`coup_code`) USING BTREE COMMENT '优惠券编码唯一索引',
 KEY `coup_user_idx` (`coup_user_id`) USING BTREE COMMENT '用户id普通索引',
 KEY `act_code_idx` (`act_code`) USING BTREE COMMENT '活动编码普通索引'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='优惠券明细表';</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul>

假设一个优惠券活动设置的最大发行量为1000张优惠券，单个用户最多可领取1张优惠券。如下

![img][]

不考虑到并发的话，用户10领取act\_code=’000000’活动的优惠券 执行的sql如下。注意\#\{\}里面的字段表示之前的sql查询出来的字段。

其实上面的代码不需要用到事务，但是为了体现接下来的并发时的情形，我就加上了事务。

首先我们来讨论，最大发行量发生并发时的问题。

假设现在优惠券领取了999张，此时有两个用户进来领取，也就是两个事务同时进行，

首先两个事务数出活动当前领用量都是999张，所以if判断通过，两个用户都可以执行insert语句，这样的话就会多出一张券没有限制住。

如何来解决这个问题呢，其实比较简单，可以利用乐观锁的方式(后面会详细介绍锁的知识)，就是我假设并发不会发生，但是我在update数据的时候我会判断他是否满足条件。   
此时我们就要另外借助一个字段来完成coup\_num\_current：当前券领用量。每次领券后我们都要更新这个字段，使其加1. 此时我们加个判断看起是否小于最大   
发行量。

那么此时的执行代码就变成了下面这样的。

<code class="language-sql hljs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">begin;
 select * from coupon_activity where act_code = '000000';
 select count(coup_id) as count_per from coupon_detail where coup_user_id = 10 and act_code = #{act_code};
 //插入明细表 首先判断是否当前领用量小于活动发行量，当前用户领取量是否小于每个用户可领取数
 if(#{count_per} < #{coup_per_num}){
 insert into coupon_detail values(1,act_code,'000000',10);
 }
 int i = update coupon_activity set coup_num_current = coup_num_current + 1 where act_code = #{act_code} and coup_num_current < #{coup_issue_num}
 //如果未有数据更新 (表明不满足coup_num_current < #{coup_issue_num}即已达最大领用量)
 if(i == 0){
 throw new Exception("此处是为了让之前的insert回滚");
 }
 commit;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li></ul>

此时我们解决了最大领用量的并发问题，下面我们来讨论下如何限制住单个用户可领取数，这个要复杂一些。因为设计到的操作要多一些，首先你要统计出已经领取的数量，   
然后插入新数据，而不像限制优惠券发行量那样只有一个update语句，所以用之前的那种乐观锁不行，因为统计出来的数据很有可能是脏数据，那么乐观锁不行的话，那用   
悲观锁来解决呢？先来分析下，伪代码如下

分析一下上面的select count语句可以发现他对coup\_user\_id = 10 and act\_code = ‘000000’的数据上了锁，但是我们接下来要做的操作是insert操作，而不是update操作。   
当两个事务刚进来的时候统计的数据都为0，也没办法给coup\_user\_id = 10 and act\_code = ‘000000’的数据上锁，所以两个selec count for update 都能执行，   
那么后面的insert操作也自然能成功，但是当有数据的时候，其中一个select for update会等待，这样的话就能成功。

这样的话悲观锁也是不行的，但是其实我们再回过头来想一下乐观锁为什么不行，是因为他分为了两个语句，而前面那个语句select count可能会读到脏数据，那么后面的利用某个字段去   
update时判断值就有可能不对，那么如何保证统计的数据跟判断保持一致呢，因为mysql处理语句的时候是一条一条处理的，所以我们通过写成一条sql就可以达到前后数据一致问题。

此处我们使用insert的时候统计出当前领取数，并与可领取数进行对比，伪代码如下

上面这条复杂的sql在高并发时会发生死锁的情况，但是确能得到正确的结果。我们来分析一下死锁的情形。

上面这条语句最里面的select where coup-user-id = 10 and act-code = ‘000000’ 会锁住这一行数据，但是当数据库没有值的时候，就上不了锁，那么另外一个事务的select也能查询，   
但是两个事务都对coup\_user-id = 10 and act-code = ‘000000’上锁了，那么insert的时候两者都处于等待对方释放锁的状态，所以就发生了死锁，数据库解决死锁之后，只有一条数据   
插入成功，这样也就得到了我们需要的结果。

> 在InnoDB中，锁是逐步获得的，因此发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另外一个事务获得锁，并继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb\_lock\_wait\_timeout来解决。

### 1. mysql锁机制 ###

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

innodb 行级锁 record-level   
lock大致有三种：record lock, gap lock and Next-KeyLocks。

record lock 锁住某一行记录   
gap lock 锁住某一段范围中的记录   
next key lock 是前两者效果的叠加。

nnoDB实现了以下两种类型的行锁：

*  共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
 *  排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（意向共享锁和意向排他锁）。这两种意向锁都是表锁。意向锁是InnoDB自动加的，不需要用户干预。   
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任意锁。

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

InnoDB的行锁实现的特点：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将会使用表锁。因为MySQL的行锁是针对索引加的锁，   
而不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引建，是会出现锁冲突的。

对于键值在条件范围内但并不存在的记录，叫做间隙。InnoDB会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。   
InnoDB使用间隙锁的目的：一是为了防止幻读，二是为了满足其恢复和复制的需要。

InnoDB如何解决死锁问题的：

在InnoDB中，锁是逐步获得的，因此发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另外一个事务获得锁，并继续完成事务。但在涉及外部锁，   
或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb\_lock\_wait\_timeout来解决。

### 2. 数据库加锁分析 ###

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，   
是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。   
在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，  
不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。   
其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

#### 2.1 事务隔离级别 ####

对锁进行分析前必须要先了解事务隔离级别的关系

详细见，[MySQL四种事务隔离级别][MySQL 1]

<table style="width:946px; border-collapse:collapse; border-spacing:0px; border:1px solid rgb(238,238,238); color:rgb(0,0,0); font-family:'microsoft yahei'; font-size:14px; line-height:26px"> 
 <thead style=""> 
 <tr style=""> 
 <th align="left" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 隔离级别</th> 
 <th align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 脏读(Dirty Read)</th> 
 <th align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可重复读(NonRepeatable Read)</th> 
 <th align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 幻读(Phantom Read)</th> 
 </tr> 
 </thead> 
 <tbody style=""> 
 <tr style=""> 
 <td align="left" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 未提交读(Read uncommitted)</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 </tr> 
 <tr style=""> 
 <td align="left" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 已提交读(Read committed)</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 </tr> 
 <tr style=""> 
 <td align="left" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可重复读(Repeatable read)</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可能</td> 
 </tr> 
 <tr style=""> 
 <td align="left" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 可串行化(Serializable)</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 <td align="center" style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 不可能</td> 
 </tr> 
 </tbody> 
</table>

\- 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

*  提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
 *  可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
 *  串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

MySQL InnoDB默认使用的级别是可重复读级别(Repeatable read),查找命令如下

*  脏读:当一个事务进行的操作还未提交时，另外一个事务读到了修改的数据，这就是脏读，但是RR级别事务避免了脏读。
 *  不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。   
    那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。   
    这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。但是，RR级别是不会出现不一样的结果的，即使另一个事务提交了修改他也查不到变化。
 *  幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，   
    这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

#### 2.2 sql语句加锁分析 ####

参考：[MySQL加锁分析][MySQL 2]

首先我们可以确定的是语句1，他是不加锁的，属于快照读。语句2和语句3要复杂些，我们慢慢来分析。

下面我们默认事务级别为可重复读(Repeated Read)，因为这是MySQL InnoDB默认级别。

语句2分析：

1.  如果id是主键或者是索引的话，那么锁定的行只有符合条件的那几行。
2.  如果id非索引，那么会锁表。

语句3分析：

1.  id或者nickname只要有一个是索引或者是主键的话，那么锁住的行都是符合条件的行。

但是要注意一个情况，如果你查看索引数据值存在大量重复的数据的话（重复的数要是where条件值），那么有可能条件是不会走索引，而是进行全表查询，所以此时锁住的也是全表。

> 因为索引扫描书超过30%时，会进行全表扫描。

[http_leihuang.org_2015_09_10_mysql-lock-concurrency]: http://leihuang.org/2015/09/10/mysql-lock-concurrency/
[MySQL]: http://lib.csdn.net/base/14
[img]: http://leihuang-net.qiniudn.com/20150910153742.png
[MySQL 1]: http://www.cnblogs.com/zhoujinyi/p/3437475.html
[MySQL 2]: http://hedengcheng.com/?p=771