数据库（二）：锁（针对mysql）

承接上一节 数据库（一）：ACID，事务。
在理解了事务以及事务的问题后，自然会想办法解决这些问题。
笔者在实习过程中，遇到了mysql批量插入导致死锁的问题，于是顺着之前的博客延续下去，这次探究下数据库的锁和解决方案（即隔离级别）。

锁

当多个事务并发执行时，会导致三类数据读取问题，和丢失更新问题。仔细研究这些问题，会发现，这些问题都是由于同一个原因造成的：事务A读取某数据时，另一个事务B企图对该数据进行修改；事务A修改某数据时，另一个事务B企图读取或者修改该数据。

因此，只要做出如下规定即可。

事务A的状态	事务B读	事务B写
事务A读时	√	×
事务A写时	×	×

由此引申出了锁的机制来保证该规定的正确。事务只有在获得了锁后才可对该锁的数据项进行所获得锁允许的操作：

• 共享锁（shared-mode lock，记为S）
  如果事务T获得了数据项Q的共享锁，则T可读但不可修改Q
• 排它锁（exclusive-mode lock，记为X）
  如果事务T获得了数据项Q的排他锁，则T可既可读又可修改Q。
  对于同一个数据项的共享锁。

一般来说，数据项的排它锁只有一把，而共享锁不限，共享锁不可与排它锁同时获得。
因此，共享锁也可以理解为读锁，排它锁可以理解为写锁。

锁的级别

上述提到了数据块是一个较笼统的概念。
具体到数据库上，数据块可以理解为，单独的某一个字段，数据行，表（不知道有没有整个数据库）。

下面只针对表级锁和行级锁的解释如下：

表级锁

顾名思义，表级锁就是获取整个表的锁。整个表是一个数据块。
那么由此，对于表级锁，事务的操作有如下特性：

增删改查操作中，只有读操作能并行执行，其他任何组合的并行操作都不能同时实行

由于增删改查都是针对表的，因此，允许多个针对同一张表的查询，其他任何并行操作都会被阻塞。

具体到MySQL数据库的的MYISAM引擎，由于MYISAM是表级锁，因此MYISAM的查询时会锁表的，参考mysql select是否会锁表

由于是表级锁，因此开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突（阻塞）的概率最高,并发度最低。

也因此，MYISAM不适合有大量更新的操作，因为更新操作会阻塞查询。

最后也是最重要的一点，MYISAM不支持事务。

行级锁

行级锁的增删改查都锁定有关的记录（行）。
具体到MySQL的的InnoDB引擎，InnoDB的行锁是通过给索引上的索引项加锁来实现的，当查询条件中不包含索引时，会全表扫描，并对符合的记录加聚簇索引。
行锁的其他特性和表锁一致。
行锁的特性有：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

MySQL的InnoDB引擎支持事务，也因此，会出现事务的三类读取问题和两类更新问题。

解决这些问题的方法通常是使用隔离级别（见上一篇博客）

另外提一下，InnoDB支持事务，其回滚操作是通过日志机制来实现的。

一次MySQL的并发bug解决过程①

结合spring data jpa，讲解一次并发带来的问题（该问题折腾了好久才解决）。

一个简单的数据库表phone如下：

id	number	name
主键	手机号码	姓名

phone表记录了手机号码(number)，和该手机号码的主人的姓名(name)。

前端并发请求如下：

//一下为同一个事务，事务级别为：READ_COMMITTED
//前端传来phoneList和name
phoneDao.deleteByName(name);
phoneDao.save(phoneList)

即删除xxx的所有手机号码，在全部保存xxx新的手机号码。(phoneList中的name均相同，且与name变量相同。
以上代码同时执行两次。

基于spring data jpa的策略。以上代码转化为sql的过程如下：

1. 执行select操作：select id from phone where name='xxx'
2. 执行插入操作：insert into ...（你没看错，hibernate是先插入再删除）
3. 执行delete操作：delete from phone where id='{上述查出来的id}'

于是问题来了，由于jpa的这种操作的特性，两个相同的事务并发，最后总有一个事务会重复删除数据。由于是事务操作，因此对于重复删除数据的事务来说，是不可理解的，因为该事物之前明明查询到有可以删除的id，但由于被前一个事务删除了，因此无数据可删，于是会报如下异常：
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
我们知道，单独的sql在数据库中删除不存在的数据是没有问题的，但在一个事务中，明明之前确定有数据，但准备执行删除时却不能删除，于是就抛出了异常。
以上问题有点类似幻读，由于READ_COMMITTED是允许幻读的，因此报错。
整理后的出错过程如下：

事务A	事务B
开始事务
	开始事务
查询表phone,获得10条记录
	查询表phone,获得10条记录
删除查询到的10条记录，提交事务
	删除查询到的10条记录？无数据可删，报错

死锁

行锁机制容易引起死锁。
MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。
但在InnoDB中，除**单个**SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。
具体的InnoDB参考文档：MySQL详解－－锁

一次MySQL的并发bug解决过程②–死锁

由于之前的问题，如果业务需要上述请求的并发操作，该如何进行呢？
首先笔者考虑到了使用更高级别的事务–SERIALIZABLE，毕竟之前的READ_COMMITTED允许幻读的出现嘛。
相同的代码，唯一不同的是事务级别：

//一下为同一个事务，事务级别为：SERIALIZABLE
//前端传来phoneList和name
phoneDao.deleteByName(name);
phoneDao.save(phoneList)

现在给出原表的所有数据和name以及phoneList的数据以便查看。

id	number	name
101	189	allan

name:"allan"
phoneList:[
    {"number":"190","name":"allan"}
]

当两个并发的事务执行相同的代码时，就出现死锁了。
查看MySQL的死锁日志：show engine innodb status
贴出死锁部分的日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-08-12 23:21:14 20a0
*** (1) TRANSACTION:
TRANSACTION 19274, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 992, 130 row lock(s)
MySQL thread id 27, OS thread handle 0x2fe0, query id 4738 localhost 127.0.0.1 za_resource Sending data
select phone0_.id,phone0_.number,phone0_.name from phone phone0_ where phone0_.name='allan'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 148 page no 7 n bits 136 index `PRIMARY` of table `za_resource_api`.`phone` trx table locks 1 total table locks 3  trx id 19274 lock mode S waiting lock hold time 0 wait time before grant 0 
*** (2) TRANSACTION:
TRANSACTION 19273, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 992, 155 row lock(s), undo log entries 8
MySQL thread id 28, OS thread handle 0x20a0, query id 4745 localhost 127.0.0.1 za_resource updating
delete from phone where id='105'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 148 page no 7 n bits 136 index `PRIMARY` of table `za_resource_api`.`phone` trx table locks 2 total table locks 3  trx id 19273 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 148 page no 7 n bits 144 index `PRIMARY` of table `za_resource_api`.`phone` trx table locks 2 total table locks 3  trx id 19273 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (1)
------------

有了上述的死锁日志，就可以分析死锁的过程了，具体的过程如下：

事务A	事务B
开始事务
	开始事务
查询表phone中name为allan的记录,获得一条id为101的记录(同时给该行加共享锁S)
插入新的记录:{"name":"allan","phone":"190"}(同时给该记录加互斥锁X)
	查询表phone中name为allan的记录,获得一条id为101的记录(同时给该行加共享锁S)，在企图获得id为102的记录（新插入的记录）时，由于该行的互斥锁被事务A占有而等待
企图删除id为101的记录，但由于事务B获取了该记录的共享锁，出现死锁，回滚事务B

死锁排查结束。

该死锁的出现是由于使用了最高的隔离级别——SERIALIZABLE，而使用该隔离级别时，每次select请求也会加上共享锁（S）。

对于MySQL中各种不同情况的加锁机制，参考如下：Mysql加锁过程详解