`
uule
  • 浏览: 6351438 次
  • 性别: Icon_minigender_1
  • 来自: 一片神奇的土地
社区版块
存档分类
最新评论

MYSQL数据库死锁的原因和解决方法

 
阅读更多

Mysql并发时经典常见的死锁原因及解决方法

总结线上遇到的MySQL死锁问题

 

数据库锁分类(重要)

 

 

===================================================================================

例4:(死锁的发生)-----------------------------------------------------------------------------------------------------------------

T1:begin tran select * from table lock in share mode update table set column1='hello'

T2:begin tran select * from table lock in share mode update table set column1='world'

 

假设 T1 和 T2 同时达到 select,T1 对 table 加共享锁,T2 也对 table 加共享锁,当 T1 的 select 执行完,准备执行 update 时,根据锁机制,T1 的共享锁需要升级到排他锁才能执行接下来的 update.在升级排他锁前,必须等 table 上的其它共享锁(T2)释放,同理,T2 也在等 T1 的共享锁释放。于是死锁产生了。

 

例5:-------------------------------------------------------------------------------------------------------------------------------------

T1:begin tran update table set column1='hello' where id=10

T2:begin tran update table set column1='world' where id=20

 

这种语句虽然最为常见,很多人觉得它有机会产生死锁,但实际上要看情况

|--如果id是主键(默认有主键索引),那么T1会一下子找到该条记录(id=10的记录),然后对该条记录加排他锁,T2,同样,一下子通过索引定位到记录,然后对id=20的记录加排他锁,这样T1和T2各更新各的,互不影响。T2也不需要等。

|--如果id是普通的一列,没有索引。那么当T1对id=10这一行加排他锁后,T2为了找到id=20,需要对全表扫描。但因为T1已经为一条记录加了排他锁,导致T2的全表扫描进行不下去(其实是因为T1加了排他锁,数据库默认会为该表加意向锁,T2要扫描全表,就得等该意向锁释放,也就是T1执行完成),就导致T2等待。

 

死锁怎么解决呢?一种办法是,如下:

例6:-------------------------------------------------------------------------------------------------------------------------------------

T1:begin tran select * from table for update update table set column1='hello'

T2:begin tran select * from table for update update table set column1='world'

 

这样,当 T1 的 select 执行时,直接对表加上了排他锁,T2 在执行 select 时,就需要等 T1 事物完全执行完才能执行。排除了死锁发生。但当第三个 user 过来想执行一个查询语句时,也因为排他锁的存在而不得不等待,第四个、第五个 user 也会因此而等待。在大并发情况下,让大家等待显得性能就太友好了。

所以,有些数据库这里引入了更新锁(如Mssql,注意:Mysql不存在更新锁)。

 

===================================================================================

数据库和操作系统一样,是一个多用户使用的共享资源。当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。加锁是实现数据库并 发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严 重影响应用的正常执行。

 

在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Locks,即S锁)。当数据对象被加上排它锁时,其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取,但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。

 

死锁的第一种情况

 

一个用户A 访问表A(锁住了表A),然后又访问表B;另一个用户B 访问表B(锁住了表B),然后企图访问表A;这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B才能继续,同样用户B要等用户A释放表A才能继续,这就死锁就产生了。

 

解决方法:

 

这种死锁比较常见,是由于程序的BUG产生的,除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑,对于数据库的多表操作时,尽量按照相同的顺序进 行处理,尽量避免同时锁定两个资源,如操作A和B两张表时,总是按先A后B的顺序处理, 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。

 

死锁的第二种情况

 

用户A查询一条纪录,然后修改该条纪录;这时用户B修改该条纪录,这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁,而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁这种死锁比较隐蔽,但在稍大点的项 目中经常发生。如在某项目中,页面上的按钮点击后,没有使按钮立刻失效,使得用户会多次快速点击同一按钮,这样同一段代码对数据库同一条记录进行多次操 作,很容易就出现这种死锁的情况。

 

解决方法:

 

1、对于按钮等控件,点击后使其立刻失效,不让用户重复点击,避免对同时对同一条记录操作。

2、使用乐观锁进行控制。乐观锁大多是基于数据版本(Version)记录机制实现。即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。乐观锁机制避免了长事务中的数据 库加锁开销(用户A和用户B操作过程中,都没有对数据库数据加锁),大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是,由于乐观锁机制是在我们的系统中实现,来自外部系统的用户更新操作不受我们系统的控制,因此可能会造 成脏数据被更新到数据库中。

3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现,如Oracle的Select … for update语句,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。如一个金融系统, 当某个操作员读取用户的数据,并在读出的用户数据的基础上进行修改时(如更改用户账户余额),如果采用悲观锁机制,也就意味着整个操作过程中(从操作员读 出数据、开始修改直至提交修改结果的全过程,甚至还包括操作员中途去煮咖啡的时间),数据库记录始终处于加锁状态,可以想见,如果面对成百上千个并发,这 样的情况将导致灾难性的后果。所以,采用悲观锁进行控制时一定要考虑清楚。

 

死锁的第三种情况

 

如果在事务中执行了一条不满足条件的update语句,则执行全表扫描,把行级锁上升为表级锁,多个这样的事务执行后,就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候,使得经常发生全表扫描,最终应用系统会越来越慢,最终发生阻塞或死锁。

 

解决方法:

 

SQL语句中不要使用太复杂的关联多表的查询;使用“执行计划”对SQL语句进行分析,对于有全表扫描的SQL语句,建立相应的索引进行优化。

 

 

案例:

 

在开发中,经常会做这类的判断需求:根据字段值查询(有索引),如果不存在,则插入;否则更新。

 

以id为主键为例,目前还没有id=22的行

Session1:
select * from t3 where id=22 for update;

Empty set (0.00 sec)
 

session2:
select * from t3 where id=23  for update;

Empty set (0.00 sec)
 

Session1:
insert into t3 values(22,'ac','a',now());
锁等待中……
 
Session2:
insert into t3 values(23,'bc','b',now());

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

 

对存在的行进行锁的时候(主键),mysql就只有行锁。

对不存在的行进行锁的时候(即使条件为主键),mysql是会锁住一段范围(有gap锁)

 

锁住的范围为:

(无穷小或小于表中锁住id的最大值,无穷大或大于表中锁住id的最小值)

 

如:如果表中目前有已有的id为(11 , 12)

那么就锁住(12,无穷大)

 

如果表中目前已有的id为(11 , 30)

那么就锁住(11,30)

 

对于这种死锁的解决办法是:

 

insert into t3(xx,xx) on duplicate key update `xx`='XX';

 

 

用mysql特有的语法来解决此问题。因为insert语句对于主键来说,插入的行不管有没有存在,都会只有行锁。

 

5.小结

总体上来说,产生内存溢出与锁表都是由于代码写的不好造成的,因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现,有BUG时再在测试阶段进 行修正,这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的,而不是质量检测时决定的,软件的质量在设计与编码阶段就已经决定了,测试只是 对软件质量的一个验证,因为测试不可能找出软件中所有的BUG。

分享到:
评论

相关推荐

    MYSQL 数据库死锁

    MySQL数据库死锁是数据库系统中常见的问题,它发生在两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。了解和处理死锁是数据库管理员和开发人员必备的技能。...

    mysql死锁的一些案例

    MySQL数据库在处理并发事务时,可能会遇到一种特殊的情况,即死锁。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。死锁是数据库系统中常见的问题,...

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

    MySQL数据库死锁是数据库管理系统中常见的一种问题,它发生在两个或更多个事务之间,当它们互相等待对方释放资源,导致所有事务都无法继续执行。死锁的发生通常与资源的竞争和事务的执行顺序有关。 死锁产生的主要...

    查看数据库死锁信息

    在数据库管理中,死锁是一个常见的问题,尤其是在高...通过上述方法,我们可以有效地管理和预防MySQL数据库中的死锁,保障系统的稳定性和高效运行。对于开发者而言,理解并掌握这些知识是确保数据库健康运行的关键。

    MySQL死锁的产生原因以及解决方案

    MySQL数据库在多用户并发操作中,为了保证数据的一致性和完整性,使用了锁机制来管理事务的并发控制。死锁是并发控制中的一种异常状态,通常发生在两个或多个事务之间,由于相互等待对方释放资源而形成僵局。本文将...

    MySQL数据库原理及设计方法.pdf

    MySQL数据库是一种广泛使用的开源关系型数据库管理系统,其原理和设计方法是数据库管理员和开发者必须掌握的基础知识。本文将深入探讨MySQL的逻辑架构、并发控制、事务处理等方面。 首先,MySQL的逻辑架构分为三层...

    mysql死锁解决

    本文将详细介绍MySQL中的死锁现象、其产生的原因以及如何有效地检测与解决死锁问题。 #### 一、死锁定义 死锁是指两个或两个以上的事务在执行过程中,由于每个事务都持有某些资源且又都在等待其他事务释放其持有的...

    数据库死锁检测工具

    总的来说,"数据库死锁检测工具"对于数据库管理至关重要,它能帮助我们及时发现和解决死锁问题,提高数据库的并发性能和整体稳定性。了解其工作原理和使用方法,对于优化数据库性能和保证服务连续性具有深远意义。

    MySQL数据库基础与实例教程所有资源

    MySQL数据库是一种广泛使用的开源关系型数据库管理系统,以其高效、可靠和易于学习的特性深受开发者喜爱。本教程将全面介绍MySQL的基础知识,并通过实例帮助你掌握其核心操作。 首先,我们来了解一下MySQL的基本...

    《MySQL数据库原理及应用》教案.rar

    《MySQL数据库原理及应用》是一门深入探讨关系型数据库管理系统MySQL的课程,旨在教授学生如何设计、创建和管理数据库,以及如何在实际应用场景中高效利用MySQL。教案详细涵盖了该课程的所有章节,为教学提供了全面...

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

    MySQL数据库中的死锁是数据库管理系统中常见的问题,特别是在并发环境下,多事务操作可能导致死锁的发生。本文主要讨论了在使用`SELECT ... FOR UPDATE`语句时遇到的死锁情况,并通过具体的例子深入分析了死锁的原因...

    避免或最小化mysql死锁deadlock一些方法.docx

    数据库死锁是一个复杂的技术概念,需要了解死锁的原因和解决方法。只有通过大量的实践和经验,才能更好地理解和避免死锁。同时,需要全面理解事务隔离级别和数据库死锁之间的关系,并掌握各种解决方法。

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

    在本文中,我们将详细介绍Purge死锁问题的原因和解决方法,并提供一些有用的建议和实践经验,以便大家更好地理解和解决Purge死锁问题。 一、Purge死锁问题的原因 Purge死锁问题的原因有多种,包括记录锁、gap锁、...

    数据库死锁

    数据库死锁sql语句

    mysql死锁分析

    #### 死锁原因深入剖析 ##### Delete操作的加锁逻辑 在InnoDB中,`DELETE`操作会根据当前的事务隔离级别和查询条件的不同而采取不同的加锁策略。在`RR`(Repeatable Read)隔离级别下,`DELETE`操作默认使用`next...

    mysql数据库DBA宝典手册

    10. **故障排查与问题诊断**:提供解决常见问题的步骤和方法,如死锁检测、慢查询日志分析等。 11. **MySQL最新版本特性**:可能会涉及MySQL的新特性,如InnoDB Cluster、JSON支持、窗口函数等。 通过《mysql_...

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

    MySQL数据库死锁** 数据库死锁通常发生在多事务环境中,由于事务间的锁冲突导致双方互相等待对方释放资源,从而形成僵局。在InnoDB存储引擎中,锁是行级别的,分为共享锁(S)和排他锁(X)。当事务A持有共享锁并...

Global site tag (gtag.js) - Google Analytics