- 浏览: 115928 次
文章分类
问题场景
每次节假日之前,公司的业务人员要通过我们开发的短信平台发送大量短信,导致数据库发生死锁。直接结果就是部分更新状态的操作对应的事务回滚,导致月底和移动公司对不平账。
获取死锁详细信息
通过show engine innodb status(在这里要感谢去年12月份世界末日前后吧,张瑞组织的AskHelloDBA数据库技术论坛,基本上那几场除了慢查询日志之外,我就记住了这句,不过还真挺有用。总算公司给报销的60大元+来往杭州的路费没白花,吼吼),获取最近一次发生的死锁详细信息。
update语句更新状态和状态变更时间这两个字段的值,where子句的谓词是判断主键id的值是不是在子查询的到的结果集中。即形如update 表A set .... where id in (一个范围)
此时对应输出(截取部分)为
一条普通的insert语句
InnoDB处理方式:
也就是在同一张表上操作的事务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
每次节假日之前,公司的业务人员要通过我们开发的短信平台发送大量短信,导致数据库发生死锁。直接结果就是部分更新状态的操作对应的事务回滚,导致月底和移动公司对不平账。
获取死锁详细信息
通过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中如何重命名数据库 转自运维人生http://opkeep.com
2013-06-24 11:19 822来源:http://opkeep.com/database/m ... -
64位Ubuntu 安装MySQL 5.5.32 错误记录与解决
2013-06-21 19:50 10861. dev.mysql.com/downloads 下载ta ... -
[转]mysql忘记root密码怎么办?重设Mysql密码的方法
2013-06-21 11:36 1121转自:http://it.oyksoft.com/ ... -
其他机器连不上本地infobright,报错'reading initial communication packet', system error: 0
2013-05-20 14:59 1886装好infobright之后,无法在其他机器连接本地ib(ro ... -
非正常关机;[ERROR 2002 (HY000);mysqld无法启动;innodb recovery失败
2013-05-17 16:24 1608os:ubuntu 11.10 db: mysql 旧5.1. ... -
[整理]一行变多行(Oracle)
2013-04-10 10:27 2517来源:http://www.itpub.net/thread- ... -
获取行号
2013-04-10 10:10 832MySQL select @rownum:=@rownum+1 ... -
行转列,列转行
2013-04-09 10:23 853SQL Server http://www.cnblogs.c ... -
多行(结果集)拼接字符串 (多行变一行)
2013-04-09 10:18 1902SQL Server版: select stuff( ... -
[转载]ubuntu安装(ubuntu 下架设LAMP+phpmyadminphpmyadmin)
2013-03-14 16:05 902转载 http://forum.ubuntu.org.cn/v ... -
MySQL慢查询专题
2013-02-25 16:00 803相关命令: mysql> show variables ... -
mssql len datalength oracle:length lengthb mysql: length char_length
2012-12-10 17:18 1868字符长度:oracle length(),mysql char ... -
mysql修改列
2012-11-16 13:40 745alter table t_feedback change u ...
相关推荐
在工作过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,...对每一个死锁场景,我都会定义一个死锁名称(实际上就是事务等待和持有的锁),每一篇分析,我都分成了 死锁特征、死锁日志、表结构、重现步骤、分析
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11...
│ 9_MySQL Insert课堂练习和Update命令.mp4 │ ├─新版MySQL DBA综合实战班 第03天 │ 1_课堂作业讲解.mp4 │ 2_MySQL Delete语法讲解.mp4 │ 3_MySQL Select语法讲解.mp4 │ 4_MySQL Select多表连接讲解.mp4 │ ...
案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。两个sql语句如下:(1)insert into backup_table select * from source_table...
前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况。因为线上的表结构比较复杂,做了一些简化以后如下 CREATE ...
疫情期间在家工作时,同事使用了 insert into on duplicate key update 语句进行插入去重,但是在测试过程中发现了死锁现象: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting ...
2. SELECT和UPDATE涉及到的数据为同一张表中的同一记录 3. 在并发为10的情况下就会触发数据库锁等待和死锁的情况 问题分析 在问题分析之前引入几个概念 事务隔离级别 参考文章《事务的ACID特性》 共享锁(S锁) ...
day012-子查询 INSERT UPDATE DELETE REPLACE day013-作业讲解一 Rank 视图 UNION 触发器上 day014-触发器下 存储过程 自定义函数 MySQL 执行计划与优化器 day015-索引 B+树 上 day016-索引 B+树 下 Explain 1 day...
业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录。 2、背景场景简化如下(数据库引擎InnoDb,数据隔离级别RR[REPEATABLE]) -- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_...
分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题。 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新...
SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。 SQL语言主要包含5个部分 数据定义...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...
.NET 2.0 泛型在实际开发中的一次小应用 C#2.0 Singleton 的实现 .Net Framwork 强类型设计实践 通过反射调用類的方法,屬性,字段,索引器(2種方法) ASP.NET: State Server Gems 完整的动态加载/卸载程序集的解决方案 ...
尤其是那些和我一样初学Java的朋友们,看看哪一节对你有用,不妨过来讨论一下哦! 第1章 Java概述 1.1 Java的发展史 1.1.1 Java起源 1.1.2 Java发展 1.2 Java是什么 1.2.1 Java语言 1.2.2 Java平台 1.2.3 ...
锁机制 NOLOCK和READPAST的区别。...NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修改。 SELECT * FROM Customer 这条语句将一直死锁,直到排他锁解除或者锁超时为止。(注:设置锁超时
134_hive同hbase集成,统计hbase数据表信息% Q/ R! Z1 J3 J) k+ H! {6 D# M 135_使用TableInputFormat进行MR编程! m& C6 B/ v6 N" `, I' O& }4 u 136_使用phoenix交互hbase& h* s5 S- ~6 ]: u7 \ 137_squirrel工具. ...