`

记一次MySQL死锁(对同一张表update和insert)的解决

阅读更多
问题场景
    每次节假日之前,公司的业务人员要通过我们开发的短信平台发送大量短信,导致数据库发生死锁。直接结果就是部分更新状态的操作对应的事务回滚,导致月底和移动公司对不平账。

获取死锁详细信息
通过show engine innodb status(在这里要感谢去年12月份世界末日前后吧,张瑞组织的AskHelloDBA数据库技术论坛,基本上那几场除了慢查询日志之外,我就记住了这句,不过还真挺有用。总算公司给报销的60大元+来往杭州的路费没白花,吼吼),获取最近一次发生的死锁详细信息。
------------------------
LATEST DETECTED DEADLOCK
------------------------
130426 15:01:53
*** (1) TRANSACTION:
TRANSACTION 0 2428709, ACTIVE 0 sec, process no 22752, OS thread id 139964634302208 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 20 lock struct(s), heap size 3024, 862 row lock(s)
MySQL thread id 1211536, query id 12990681 localhost 127.0.0.1 db Sending data

update语句更新状态和状态变更时间这两个字段的值,where子句的谓词是判断主键id的值是不是在子查询的到的结果集中。即形如update 表A set .... where id in (一个范围)
此时对应输出(截取部分)为
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2479 n bits 136 index `PRIMARY` of table `db`.`lock_occured_table` trx id 0 2428709 lock_mode X waiting


*** (2) TRANSACTION:
TRANSACTION 0 2428707, ACTIVE 1 sec, process no 22752, OS thread id 139964634904320 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1216, 6 row lock(s), undo log entries 63
MySQL thread id 1211510, query id 12990732 localhost 127.0.0.1 db update

一条普通的insert语句
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2479 n bits 136 index `PRIMARY` of table `db`.`lock_occured_table` trx id 0 2428707 lock_mode X locks rec but not gap
Record lock, heap no 57 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 30; hex 30333464373737362d376535622d346435392d613638342d383231373036; asc 034d7776-7e5b-4d59-a684-821706;...(truncated); 1: len 6; hex 000000250f23; asc    % #;; 2: len 7; hex 8000000b41034c; asc     A L;; 3: len 11; hex 3135383430353039343531; asc 15840509451;; 4: len 30; hex 64633965613930382d643066312d396264382d626563392d353135323939; asc dc9ea908-d0f1-9bd8-bec9-515299;...(truncated); 5: SQL NULL; 6: len 2; hex 2d31; asc -1;; 7: len 8; hex 8000124ef4ffbe48; asc    N   H;; 8: len 30; hex 64333066323435312d336339312d346230352d383032622d383762346335; asc d30f2451-3c91-4b05-802b-87b4c5;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2447 n bits 136 index `PRIMARY` of table `db`.`lock_occured_table` trx id 0 2428707 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 30; hex 30303138393466332d363262652d343365622d626266312d643265633462; asc 001594f3-62be-43eb-bbf1-d2ec4b;...(truncated); 1: len 6; hex 000000110936; asc      6;; 2: len 7; hex 80000009820454; asc       T;; 3: len 11; hex 3138373231303433353639; asc 18721043569;; 4: len 30; hex 63353563366632332d656238352d613735382d393466362d353132303633; asc c35c6f23-ec85-a758-94f6-512063;...(truncated); 5: len 8; hex 8000124eee70f814; asc    N p  ;; 6: len 1; hex 31; asc 1;; 7: len 8; hex 8000124eee70e769; asc    N p i;; 8: len 30; hex 38653966386339362d393266372d343638662d626536352d353863323035; asc 8c9f8d96-92f7-468f-be65-58c205;...(truncated);



InnoDB处理方式:
*** WE ROLL BACK TRANSACTION (1)


也就是在同一张表上操作的事务1与事务2冲突了,结果回滚了更新状态的事务1。
其实理解这短短几句话,费了我很长时间——因为看着眼晕,我瞟两眼就不看了,隔几天再瞟两眼又晕又不看了。这会再看看,其实问题描述得已经很明显了嘛。

最后我的理解是update和insert都想对同一张表的主键索引加排它锁。

根据MySQL 5.1(这会才出版本号,XD,哼哼 具体为5.1.67)的文档对InnoDB事务隔离级别的描述(见http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html),偶终于知道原因了,解决方法也很明显了。

于是乎,做个测试(先说明,偶们的库采用的是默认的事务隔离级别Repeatable Read,其实对这个场景来说哪个隔离级别都一样)
事务1:
start transaction
update 表A set 状态=3 where id=55;

事务2:
start transaction
向表A insert 一条记录

现在不管这两个事务的执行顺序如何,最后都可以再执行commit而不像之前那样show engine innodb status中会看到由于等待时间长死锁或直接死锁。

告诉开发同事,先将要update的记录的id都取出来(select不会像事务1那样对主键索引加排它锁哦),然后对每个id单独执行update(更新指定的单条记录也不会像原来那样哦),这样就能避免事务1(update操作)与事务2(批量insert)发生冲突了。

从此天下太平,五一又要到了,终于不用担心这个问题了。

P.S.记得SQL Server的事务隔离级别也是默认重复读。改天试试会不会有类似问题。

(
思路参考 http://www.chriscalender.com/?p=426
关于innodb的概述,可查看 http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
)

下面转一个DB2和 Oracle的 DB2和 Oracle的并发控制(锁)比较:
http://sunxboy.iteye.com/blog/387625
分享到:
评论

相关推荐

    mysql死锁检测机制初探1

    在 MySQL 中,死锁检测机制是一种重要的机制,用于检测和解决事务之间的死锁问题。在本文中,我们将详细介绍 MySQL 死锁检测机制的原理和实现。 一、死锁的定义和原理 在 MySQL 中,死锁是指两个或两个以上的事务...

    记一次排查线上MySQL死锁过程,不能只会curd,还要知道加锁原理.doc

    标题和描述中提到的事件是一次在线MySQL数据库发生的死锁问题,这突显了了解数据库加锁原理的重要性,而不仅仅是基础的CRUD操作。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无...

    收集一些常见的 MySQL 死锁案例

    在工作过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,...对每一个死锁场景,我都会定义一个死锁名称(实际上就是事务等待和持有的锁),每一篇分析,我都分成了 死锁特征、死锁日志、表结构、重现步骤、分析

    何登成 - 管中窥豹——MySQL(InnoDB)死锁分析之道

    在实际中,解决MySQL(InnoDB)的死锁问题需要深入了解死锁产生的原因、锁的类型以及加锁策略,进而对业务逻辑进行优化或调整数据库配置和代码逻辑以减少死锁的可能性。 ### 个人简介 何登成作为阿里巴巴DBA团队的...

    MySQL死锁套路之唯一索引下批量插入顺序不一致

    【MySQL死锁套路之唯一索引下批量插入顺序不一致】 死锁是数据库管理系统中常见的问题,它发生在两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。在MySQL中,...

    pt-osc在线重建表导致死锁的分析及对应的优化方案1

    - 接着,在原表上创建insert、update、delete触发器,用于将增量数据迁移到新表,所有操作在一个事务内。 - 工具会按预设的块大小(chunk-size)从原表复制数据到新表。 - 数据复制完成后,进行原子操作的表名...

    新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar

    │ 9_MySQL Insert课堂练习和Update命令.mp4 │ ├─新版MySQL DBA综合实战班 第03天 │ 1_课堂作业讲解.mp4 │ 2_MySQL Delete语法讲解.mp4 │ 3_MySQL Select语法讲解.mp4 │ 4_MySQL Select多表连接讲解.mp4 │ ...

    记一次 MySQL 并发时插入重复数据的解决方案

    本篇文章探讨了一次在MySQL并发操作中遇到的插入重复数据问题及其解决方案。 问题起源于一个名为`coolq_qq_group_message_receiver`的表,用于存储QQ群消息接收者的信息,其中`qq_group_number`字段具有唯一性约束...

    MySQL实现两张表数据的同步

    首先,触发器是一种数据库对象,它在特定的数据库事件(如INSERT、UPDATE或DELETE)发生时自动执行预定义的SQL语句。在这里,我们的目标是当向表A或表B插入新记录时,确保另一张表也会相应地插入匹配的记录。 假设...

    由不同的索引更新解决MySQL死锁套路

    在MySQL数据库中,死锁是两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。本文将深入探讨如何通过调整索引来解决一个具体的线上死锁案例。 首先,让我们看下...

    mysql5.1手册中文HTML版

    3. 死锁检测与解决:自动检测和解除死锁情况。 六、存储引擎 1. InnoDB:支持事务处理、外键和行级锁定,适合大量并发操作。 2. MyISAM:非事务处理,速度快,但不支持外键。 3. Memory:数据存储在内存中,速度极...

    mysql基础知识和mysql优化整理

    3. SQL语言:SQL(结构化查询语言)是操作MySQL的核心工具,包括SELECT(查询数据)、INSERT(插入数据)、UPDATE(更新数据)、DELETE(删除数据)等语句。 4. 关系模型:MySQL基于关系数据库模型,强调数据之间的...

    通过唯一索引S锁与X锁来了解MySQL死锁套路

    在MySQL数据库中,死锁是并发操作中可能出现的一种情况,当两个或多个事务相互等待对方释放资源时,就形成了死锁。唯一索引在死锁问题中扮演着重要的角色,因为它们经常涉及到并发插入和更新操作。本文将深入探讨...

    MySQL面试题及答案.docx

    DML 主要用于对数据库进行插入、更新和删除操作,例如 INSERT、UPDATE 和 DELETE 语句。DCL 主要用于控制数据库的访问权限,例如 GRANT 和 REVOKE 语句。 5. MySQL 分库分表 MySQL 分库分表是指将原来独立的数据库...

    浅析MYSQL中的并发操作与锁定

    并发操作是指多个线程或用户同时访问和操作同一个数据库表的能力,而锁定则是为了避免数据不一致和数据丢失所采取的一种机制。 MYSQL中的锁定机制有三种级别:页级、表级、行级。MyISAM和MEMORY存储引擎采用的是表...

    MySQL王者晋级之路_MySql王者晋级之路_

    学习MySQL时,你需要熟悉其安装配置、基本的SQL语句,如SELECT、INSERT、UPDATE和DELETE,以及数据库表的创建和管理。 进阶阶段,你需要深入学习SQL的高级用法,如联接(JOIN)、子查询、窗口函数(Window ...

    MySQL 文档

    4. **事务和并发控制**:介绍MySQL的事务处理机制,包括ACID属性(原子性、一致性、隔离性和持久性),以及死锁检测和解决。 5. **视图和存储过程**:讲解如何使用视图来简化复杂查询,以及创建和调用存储过程以...

Global site tag (gtag.js) - Google Analytics