mysql锁问题

在一些打折或者抢购活动中，购买商品减少库存量常常存在并发问题，一般来说，我解决这些问题通过两种方式：

1）版本号方式，CAS机制：UPDATE table SET num=num-1 WHERE id=? AND num=?;这种方法适用于并发量比较少的情况。变异版本：UPDATE table SET num=num-1 WHERE id=? AND num>0;

2）行锁：用事务+for update来解决。

2）变成单线程，比如放到队列处理，按先后顺序扔进队列，一个一个处理。比较少用，延迟太高。

 

基本锁概念：共享锁(select * from tabl where ...lock in share mode)、排他锁(select * from table_name where ...for update)。

 

读操作的相关锁：

1）一致性非锁定读：默认事务级别，事务A开启时，select所读取的数据，在事务结束前，即使被其他事务B改变且提交，或者即使新插入一条数据。A会无视期间的所有更新插入(即可重复读，读事务隔离性)，读到的数据仍然不变，相当于事务开始时的一个备份。而Read Committed事务级别不同，会读到最新值，这样违反了ACID的I特性。

2）一致性锁定读：默认事务级别，可通过select...for update(x锁，其他事务不能读不能写)或select ... lock in share mode(s锁，其他事务可读不可写)进行锁定，保证数据的逻辑一致性。

       但是，对于一致性非锁定读，即使读取的行已经被其他事务使用了select...for update，仍然可以读到值，因为一致性非锁定读，压根没有锁，直接读取的备份数据。

 

 

锁的实现方式：

       在MySQL中，行级锁并不是直接锁记录，而是锁索引(若条件没用到索引，会默认锁主键)。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

       InnoDB行锁是通过给索引项加锁实现的，如果没有索引，InnoDB会通过隐藏的聚簇索引来对记录加锁。

也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。

 

行锁的三种算法，详情请看官网：

Record lock ：行锁，即锁定一条记录，实际是对索引加锁，无索引时默认主键索引。

Gap lock：间隙锁，对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身。在默认可重复读RR下，防止其他事务操作，防止幻读(写事务隔离性，比如事务A开启select...where ... for update，where条件是非唯一索引则间隙锁、是非索引则锁整个表。如果不加间隙锁，那么事务B提交插入或更新，事务A同条件重新查询，导致数据不一致)。举例：

#这个会有间隙锁
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

#当id时唯一索引时，没有使用间隙锁。而如果id没有索引，或者非唯一索引，则有间隙锁
SELECT * FROM child WHERE id = 100;

 

Next-key Lock：行锁+间隙锁，锁定一个范围的记录并包含记录本身（上面两者的结合）。一般非主键的索引做条件的用这个。

注意：InnoDB默认级别是repeatable-read级别，所以下面说的都是在RR级别中的。

       Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。

小结：

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

何时在InnoDB中使用表锁：

InnoDB在绝大部分情况会使用行级锁，因为事务和行锁往往是我们选择InnoDB的原因，但是有些情况我们也考虑使用表级锁。

1、当事务需要更新大部分数据时，表又比较大，如果使用默认的行锁，不仅效率低，而且还容易造成其他事务长时间等待和锁冲突。

2、事务比较复杂，很可能引起死锁导致回滚。

死锁：参考。可通过查询出主键，再操作。比如update ... where id in（select...）

 

 

实验一：

数据库表结构如下：

CREATE TABLE `user` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`age` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`Id`),
	INDEX `Index 2` (`age`)
)
ENGINE=InnoDB;

INSERT INTO `user` (`Id`, `name`, `age`) VALUES (1, 'fgdg', 6);
INSERT INTO `user` (`Id`, `name`, `age`) VALUES (2, 'gdfgd', 9);
INSERT INTO `user` (`Id`, `name`, `age`) VALUES (3, 'gfdg', 12);

 

执行命令如下：

#客户端1操作
set autocommit=0;
select * from user where age=6 for update;

#客户端2操作，这时候会报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert user values(null,'df',7);

 

实验二：

数据库表结构如下：

CREATE TABLE `t` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL DEFAULT '0',
	`age` INT(11) NOT NULL,
	`workage` INT(11) NOT NULL,
	PRIMARY KEY (`Id`),
	INDEX `Index 2` (`age`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7;

INSERT INTO `t` (`Id`, `name`, `age`, `workage`) VALUES (1, '0', 2, 1);
INSERT INTO `t` (`Id`, `name`, `age`, `workage`) VALUES (2, 'q2', 6, 8);
INSERT INTO `t` (`Id`, `name`, `age`, `workage`) VALUES (3, 'fd ', 12, 16);

 

执行命令如下：

#客户端一，与客户端二每次每个执行一条语句
mysql> begin;

#顺序------>1
mysql> delete from t where age=6;
Query OK, 1 row affected (0.00 sec)

#顺序------>3，这条语句会阻塞，客户端二报死锁回滚后，才解除死锁。
mysql> insert t values(null,'d',7,0);
Query OK, 1 row affected (42.41 sec)

#客户端二
mysql> begin;

#顺序------>2
mysql> delete from t where age=12;
Query OK, 1 row affected (0.00 sec)

#顺序------>4
mysql> insert t values(null,'d',7,0);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 分析：客户端一锁定age范围为[6,12)，客户端二锁定age范围(6,12]，所以在(6,12)范围内，两个客户端都不能进行操作。

 

 

附死锁总结：

1）不同表相同记录行锁冲突

     这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

                                                                       图10

2）相同表记录行锁冲突

     这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

                                                                          图11

3）不同索引锁冲突

     这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

                                                                          图12

3.4 gap锁冲突

     innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。
                                                                               图13

如何尽可能避免死锁

1）以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

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

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

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

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

如何定位死锁成因

     下面以本文开头的死锁案例为例，讲下如何排查死锁成因。

1）通过应用业务日志定位到问题代码，找到相应的事务对应的sql；

      因为死锁被检测到后会回滚，这些信息都会以异常反应在应用的业务日志中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。

1 2 3 4 5

start tran 1 deleteHeartCheckDOByToken 2 updateSessionUser ... commit

      此外，我们根据日志回滚的信息发现在检测出死锁时这个事务被回滚。

2）确定数据库隔离级别。

     执行select @@global.tx_isolation，可以确定数据库的隔离级别，我们数据库的隔离级别是RC，这样可以很大概率排除gap锁造成死锁的嫌疑;

3）找DBA执行下show InnoDB STATUS看看最近死锁的日志。

     这个步骤非常关键。通过DBA的帮忙，我们可以有更为详细的死锁信息。通过此详细日志一看就能发现，与之前事务相冲突的事务结构如下：

1 2 3 4 5

start tran 1 updateSessionUser 2 deleteHeartCheckDOByToken ... commit

　　这不就是第一个图描述的死锁嘛！