数据库并发问题总结(锁、隔离级别、死锁)

　　锁是一种防止在某对象执行动作的一个进程与已在该对象上执行的其他进行相冲突的机制。也就是说，如果有其他人在操作某个对象，那么你旧不能在该对象上进行操作。你能否执行操作取决于其他用户正在进行的操作。

通过锁可以防止的问题

　　锁可以解决以下4种主要问题(也是多线程并发会导致的一些问题)：

• 脏读
• 非重复性读取
• 幻读
• 丢失更新

　　1、脏读

　　如果一个事务读取的记录是另一个未完成事务的一部分，那么这时就发生了脏读。如果第一个事务正常完成，那么就有什么问题。但是，如果前一个事务回滚了呢，那将从数据库从未发生的事务中获取了信息。

　　2、非重复性读取

　　很容易将非重复性读取和脏读混淆。如果一个事务中两次读取记录，而另一个事务在这期间改变了数据，就会发生非重复性读取。
例如，一个银行账户的余额是不允许小于0的。如果一个事务读取了某账户的余额为125元，此时另一事务也读取了125元，如果两个事务都扣费100元，那么这时数据库的余额就变成了-75元。

　　有两种方式可以防止这个问题：

• 创建CHECK约束并监控547错误
• 将隔离级别设置为REPEATABLEREAD或SERIALIZABLE

　　CHECK约束看上去相当直观。要知道的是，这是一种被动的而非主动的方法。然而，在很多情况下可能需要使用非重复性读取，所以这在很多情况下是首选。

　　3、幻读

　　幻读发生的概率非常小，只有在非常偶然的情况下才会发生。

　　比如，你想将一张工资表里所有低于100的人的工资，提高到100元。你可能会执行以下SQL语句：

　　UPDATE tb_Money SET Salary = 100
　　WHERE Salary < 100

　　这样的语句，通常情况下，没有问题。但是如果，你在UPDATE的过程中，有人恰好有INSERT了一条工资低于100的数据，因为这是一个全新的数据航，所以没有被锁定，而且它会被漏过Update。

　　要解决这个问题，唯一的方法是设定事务隔离级别为SERIALIZABLE,在这种情况下，任何对表的更新都不能放入WHERE子句中，否则他们将被锁在外面。

　　4、丢失更新

　　丢失更新发生在一个更新成功写入数据库后，而又意外地被另一个事务重写时。这是怎么发生的呢？如果有两个事务读取整个记录，然后其中一个向记录写入了更新信息，而另一个事务也向该记录写入更新信息，这是就会出现丢失更新。

　　有个例子写得很好，这里照敲下来吧。假如你是公司的一位信用分析员，你接到客户X打开的电话，说他已达到它的信用额度上限，想申请增加额度，所以你查看了这位客户的信息，你发现他的信用额度是5000，并且看他每次都能按时付款。

　　当你在查看的时候，信用部门的另一位员工也读取了客户X的记录，并输入信息改变了客户的地址。它读取的记录也显示信用额度为5000。

　　这时你决定把客户X的信用额度提高到10000，并且按下了Enter键，数据库现在显示客户X的信用额度为10000。

　　Sally现在也更新了客户X的地址，但是她仍然使用和您一样的编辑屏幕，也就是说，她更新了整个记录。还记得她屏幕上显示的信用额度吗？是5000.数据库现在又一次显示客户X的信用额度为5000。你的更新丢失了。

　　解决这个问题的方法取决于你读取某数据和要更新数据的这段时间内，代码以何种方式识别出另一连接已经更新了该记录。这个识别的方式取决于你所使用的访问方法。

可以锁定的资源

对于SQL Server来说，有6种可锁定的资源，而且它们形成了一个层次结构。锁的层次越高，它的粒度就越粗。按粒度由粗到细排列，这些资源包括：

• 数据库：锁定整个数据库。这通常发生在整个数据库模式改变的时候。
• 表：锁定整个表。这包含了与该表相关联的所有数据相关的对象，包括实际的数据行(每一行)以及与该表相关联的所有索引中的键。
• 区段：锁定整个区段。因为一个区段由8页组成，所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据航。
• 页：锁定该页中的所有数据或索引键。
• 键：在索引中的特定键或一系间上有锁。相同索引页中的其他键不受影响。
• 行或行标识符：虽然从技术上将，锁是放在行标识符上的，但是本质上，它锁定了整个数据行。

锁的分类

首先看一下锁的关系图:


注解：

1.乐观锁一般是指用户自己实现的一种锁机制，假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观锁的实现方式一般包括使用版本号和时间戳。

2.悲观锁一般就是我们通常说的数据库锁机制，以下讨论都是基于悲观锁。

2.1 悲观锁主要表锁、行锁、页锁。
2.1.1 在MyISAM中只用到表锁，不会有死锁的问题，锁的开销也很小，但是相应的并发能力很差。

2.1.2 innodb实现了行级锁和表锁，锁的粒度变小了，并发能力变强，但是相应的锁的开销变大，很有可能出现死锁。同时innodb需要协调这两种锁，算法也变得复杂。InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

2.1.3表锁和行锁都分为共享锁和排他锁（独占锁），而更新锁是为了解决行锁升级（共享锁升级为独占锁）的死锁问题。

2.1.4 关于意向锁的解释：
在mysql中有表锁，读锁锁表，会阻塞其他事务修改表数据。写锁锁表，会阻塞其他事务读和写。
Innodb引擎又支持行锁，行锁分为共享锁，一个事务对一行的共享只读锁。排它锁，一个事务对一行的排他读写锁。
这两中类型的锁共存的问题考虑这个例子：事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？

若没有意向锁，解决办法为：
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。
注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成
step1：不变
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

数据库四种隔离级别

了解了数据的锁机制，数据库的隔离级别也就好理解多了。每一种隔离级别满足不同的数据要求，使用不同程度的锁。

• Read Uncommitted，读写均不使用锁，数据的一致性最差，也会出现许多逻辑错误；会导致脏读、丢失更新(覆盖)、不可重复读、幻读
• Read Committed，使用写锁，解决了脏读和丢失更新的问题，但是不能解决不可重复读和幻读
• Repeatable Read, 使用读锁和写锁，解决脏读、丢失更新、不可重复读问题，不能解决幻读问题
• Serializable, 使用事务串形化调度，避免出现因为插入数据没法加锁导致的不一致的情况，解决所有多线程问题 ，但是本质是单线程处理，性能极低

• 读不提交，造成脏读(Read Uncommitted)
• 一个事务中的读操作可能读到另一个事务中未提交修改的数据，如果事务发生回滚就可能造成错误。

  例子：A打100块给B，B看账户，这是两个操作，针对同一个数据库，两个事物，如果B读到了A事务中的100块，认为钱打过来了，但是A的事务最后回滚了，造成损失。

  避免这些事情的发生就需要我们在写操作的时候加锁，使读写分离，保证读数据的时候，数据不被修改，写数据的时候，数据不被读取。从而保证写的同时不能被另个事务写和读。

• 读提交(Read Committed)
• 我们加了写锁，就可以保证不出现脏读，也就是保证读的都是提交之后的数据，但是会造成不可重读，即读的时候不加锁，一个读的事务过程中，如果读取数据两次，在两次之间有写事务修改了数据，将会导致两次读取的结果不一致，从而导致逻辑错误。

• 可重读(Repeatable Read）
• 解决不可重复读问题，一个事务中如果有多次读取操作，读取结果需要一致（指的是固定一条数据的一致，幻读指的是查询出的数量不一致）。 这就牵涉到事务中是否加读锁，并且读操作加锁后是否在事务commit之前持有锁的问题，如果不加读锁，必然出现不可重复读，如果加锁读完立即释放，不持有，那么就可能在其他事务中被修改，若其他事务已经执行完成，此时该事务中再次读取就会出现不可重复读，

  所以读锁在事务中持有可以保证不出现不可重复读，写的时候必须加锁且持有，这是必须的了，不然就会出现脏读。Repeatable Read（可重读）也是MySql的默认事务隔离级别，上面的意思是读的时候需要加锁并且保持

• 可串行化（Serializable）
• 解决幻读问题，在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争

  处理死锁

  　　死锁的错误号是1205。

  　　如果一个锁由于另一个锁占有资源而不能完成应该做的清除资源工作，就会导致死锁；反之亦然。当发生死锁时，需要其中的一方赢得这场斗争，所以SQL Server选择一个死锁牺牲者，对死锁牺牲者的事务进行回滚，并且通过1205错误来通知发生了死锁。另外一个事务将继续正常地运行。

  　　1、判断死锁的方式

  　　每隔5秒钟，SQL Server就会检查所有当前的事务，了解他们在等待什么还未被授予的锁。然后再一次重新检查所有打开的锁请求的状态，如果先前请求中有一个还没有被授予，则它会递归地检查所有打开的事务，寻找锁定请求的循环链。如果SQL Server找到这样的村换连，则将会选择一个或更多的死锁牺牲者。

  　　2、选择死锁牺牲者的方式

  　　默认情况下，基于相关事务的"代价"，选择死锁牺牲者。SQL Server将会选择回滚代价最低的事务。在一定程度上，可以使用SQL Server中的DEADLOCK_PRIORITY SET选项来重写它。

  　　3、避免死锁

  避免死锁的常用规则

  • 按相同的顺序使用对象
  • 使事务尽可能简短并且在一个批处理中。
  • 尽可能使用最低的事务隔离级别。
  • 在同一事务中不允许无限度的中断。
  • 在控制环境中，使用绑定连接。

  　　1、按相同的顺序使用对象

  　　例如有两个表：Suppliers和Products。假设有两个进程将使用这两个表。进程1接受库存输入，用手头新的产品总量更新Products表，接下来用已经购买的产品总量来更新Suppliers表。进程2记录销售数据，它在Supperlier表中更新销售产品的总量，然后在Product中减少库存数量。

  　　如果同时运行这两个进程，那么就有可能碰到麻烦。进程1试图获取Product表上的一个排他锁。进程2将在Suppliers表上获取一个排他锁。然后进程1将试图获取Suppliers表上的一个锁，但是进程1必须等到进程2清除了现有的锁。同时进程2也在等待进程1清除现有锁。

  　　上面的例子是，两个进程用相反的顺序，锁住两个表，这样就很容易发生死锁。

  　　如果我们将进程2改成首先在Products减少库存数量，接着在Suppliers表中更新销售产品的总数量。两个进程以相同的顺序访问两张表，这样就能够减少死锁的发生。

  　　2、使事务尽可能简短

  　　保持事务的简短将不会让你付出任何的代价。在事务中放入想要的内容，把不需要的内容拿出来，就这么简单。它的原理并不复杂-事务打开的时间越长，它触及的内容就越多，那么其他一些进程想要得到正在使用的一个或者多个对象的可能性就越大。如果要保持事务简短，那么就将最小化可能引起死锁的对象的数量，还将减少锁定对象的时间。原理就如此简单。

  　　3、尽可能使用最低的事务隔离级别

  　　使用较低的隔离级别和较高的隔离级别相比，共享锁持续的时间更短，因此会减少锁的竞争。

  　　4、不要采用允许无限中断的事务

  　　当开始执行某种开放式进程时间，不要创建将一直占有资源的锁。通常，指的是用户交互，但它也可能是允许无限等待的任何进程。

   

  参考文章:

  • https://blog.csdn.net/yen_csdn/article/details/51814912
  • https://www.cnblogs.com/kissdodog/p/3170036.html
  • https://blog.csdn.net/C_J33/article/details/79487941