5分钟快速了解数据库死锁产生的场景和解决方法

前言

加锁（Locking）是数据库在并发访问时保证数据一致性和完整性的主要机制。任何事务都需要获得相应对象上的锁才能访问数据，读取数据的事务通常只需要获得读锁（共享锁），修改数据的事务需要获得写锁（排他锁）。当两个事务互相之间需要等待对方释放获得的资源时，如果系统不进行干预则会一直等待下去，也就是进入了死锁（deadlock）状态。

以下内容适用于各种常见的数据库管理系统，包括 Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL 等。

死锁是如何产生的？

演示死锁的产生非常简单，我们只需要创建一个包含两行数据的简单示例表：

CREATE TABLE t_lock(id int PRIMARY KEY, col int);
INSERT INTO t_lock VALUES (1, 100);
INSERT INTO t_lock VALUES (2, 200);

SELECT * FROM t_lock;
id|col|
--+---+
 1|100|
 2|200|

如果我们在不同事务中以不同的顺序修改数据，就可能引起事务之间的相互等待。一个事务等待另一个事务释放资源不会产生什么问题，但是如果两个事务互相等待对方的资源，数据库管理系统只有两个选择：无限等待或者中止一个事务并让另一个事务成功执行。

显然无限等待不是解决问题的方法，因此数据库通常是等待一定时间之后中止其中一个事务。

以下是一个死锁的演示案例：

事务一 事务二 备注
BEGIN; BEGIN; 分别开始两个事务
UPDATE t_lock
SET col = col + 100
WHERE id = 1;		 UPDATE t_lock
SET col = col + 200
WHERE id = 2;		 事务一修改 id=1 的数据，事务二修改 id=2 的数据
UPDATE t_lock
SET col = col + 100
WHERE id = 2;		 事务一修改 id=2 的数据，需要等待事务二释放写锁
等待中… UPDATE t_lock
SET col = col + 200
WHERE id = 1;		 事务二修改 id=1 的数据，需要等待事务一释放写锁
死锁 死锁 数据库检测到死锁，选择中止一个事务
更新成功 返回错误

对于 MySQL InnoDB，默认启用了 innodb_deadlock_detect 选项，事务二返回以下错误信息：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如果我们禁用 InnoDB 死锁检测选项，事务二在等待 50 s（innodb_lock_wait_timeout ）后提示等待超时：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Oracle 检测到死锁时返回以下错误：

ORA-00060: 等待资源时检测到死锁

Microsoft SQL Server 检测到死锁时返回的错误如下

消息 1205，级别 13，状态 51，第 7 行
事务(进程 ID 67)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

PostgreSQL 检测到死锁时返回的错误如下：

SQL 错误 [40P01]: 错误: 检测到死锁
  详细：进程32等待在事务 4765上的ShareLock; 由进程16552阻塞.
进程16552等待在事务 4766上的ShareLock; 由进程32阻塞.
  建议：详细信息请查看服务器日志.
  在位置：当更新关系"t_lock"的元组(0, 1)时

如何解决并避免死锁

死锁不是数据库自身的问题，我们无法通过优化数据库配置来解决或者避免死锁，只能通过修改应用程序来解决。简单来说，我们应该在程序中按照相同的顺序修改数据，避免产生相互等待资源的情况发生。例如：

事务一 事务二 备注
BEGIN; BEGIN; 分别开始两个事务
UPDATE t_lock
SET col = col + 100
WHERE id = 1;		 UPDATE t_lock
SET col = col + 200
WHERE id = 1;		 事务一和事务二都修改 id=1 的数据，后执行的事务需要等待
UPDATE t_lock
SET col = col + 100
WHERE id = 2;		 等待中… 事务一修改 id=1 的数据，事务二等待中
COMMIT; 等待中… 事务一提交
UPDATE t_lock
SET col = col + 200
WHERE id = 2;		 事务二继续修改 id=2 的数据
COMMIT; 事务二提交

以上场景不会产生死锁。不过，我们在实际应用中可能无法完全按照相同顺序修改数据。如果出现了不可避免的死锁情况，另一种解决方法就是捕获系统返回的死锁异常并在程序中加入重试机制。

总结

本文简要介绍了数据库死锁产生的原因和解决方法。到此这篇关于5分钟快速了解数据库死锁产生的场景和解决方法的文章就介绍到这了,更多相关数据库死锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • InnoDB数据库死锁问题处理

    场景描述 在update表的时候出现DeadlockLoserDataAccessException异常 (Deadlock found when trying to get lock; try restarting transaction...). 问题分析 这个异常并不会影响用户使用,因为数据库遇到死锁会自动回滚并重试.用户的感觉就是操作稍有卡顿.但是监控老是报异常,所以需要解决一下. 解决方法 在应用程序中update的地方使用try-catch. 我自己封装了一个函数,如下. /** *

  • MySQL数据库的一次死锁实例分析

    1.故事起因于2016年11月15日的一个生产bug.业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录. 2.背景场景简化如下(数据库引擎InnoDb,数据隔离级别RR[REPEATABLE]) -- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hel

  • 简单说明Oracle数据库中对死锁的查询及解决方法

    死锁的原理 当对于数据库某个表的某一列做更新或删除等操作,执行完毕后该条语句不提 交,另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态, 此时的现象是这条语句一直在执行,但一直没有执行成功,也没有报错.    死锁的定位方法 通过检查数据库表,能够检查出是哪一条语句被死锁,产生死锁的机器是哪一台.   1)用dba用户执行以下语句 select username,lockwait,status,machine,program from v$session where sid in

  • MySQL数据库之Purge死锁问题解析

    MySQL数据库之Purge死锁问题解析

    Purge死锁 场景说明 Purge死锁说明 表中存在记录(unique key) 10,20,30,40 (且有 自增主键 ),现在删除记录 20 ,并且已经 提交 了该事物. purge 线程此时还 没有回收 该记录,且此时又 插入 新的记录 20 . +------+------+------+------+ orignal | 10 | 20 | 30 | 40 | unique +------+------+------+------+ delete 20 +------+------

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • 讲解Oracle数据库中结束死锁进程的一般方法

    1.查看死锁 1)用dba用户执行以下语句 select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object) 如果有输出的结果,则说明有死锁,且能看到死锁的机器是哪一台.字段说明: Username:死锁语句所用的数据库用户: Lockwait:死锁的状态,如果有内容表示被死锁. Status: 状态,active表示被死锁 Ma

  • 查询Sqlserver数据库死锁的一个存储过程分享

    使用sqlserver作为数据库的应用系统,都避免不了有时候会产生死锁, 死锁出现以后,维护人员或者开发人员大多只会通过sp_who来查找死锁的进程,然后用sp_kill杀掉.利用sp_who_lock这个存储过程,可以很方便的知道哪个进程出现了死锁,出现死锁的问题在哪里. 创建sp_who_lock存储过程 CREATE procedure sp_who_lock as begin declare @spid int declare @blk int declare @count int de

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • 记一次公司仓库数据库服务器死锁过程及解决办法

    记一次公司仓库数据库服务器死锁过程及解决办法

    死锁的四个必要条件: 互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用. 请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源. 非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺. 循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源. 仓库拣货卡死,排查了数据库的很多地方,都没有头绪,最后到SQL Server 错误日志里查看,终于

  • Mybatis update数据库死锁之获取数据库连接池等待

    Mybatis update数据库死锁之获取数据库连接池等待

    最近学习测试mybatis,单个增删改查都没问题,最后使用mvn test的时候发现了几个问题: 1.update失败,原因是数据库死锁 2.select等待,原因是connection连接池被用光了,需要等待 get: 1.要勇于探索,坚持就是胜利.刚看到错误的时候直接懵逼,因为错误完全看不出来,属于框架内部报错,在犹豫是不是直接睡 觉得了,毕竟也快12点了.最后还是给我一点点找到问题所在了. 2.同上,要敢于去深入你不了解的代码,敢于研究不懂的代码. 3.距离一个合格的码农越来越远了,因为越

随机推荐