[b]问题场景[/b]
每次节假日之前,公司的业务人员要通过我们开发的短信平台发送大量短信,导致数据库发生死锁。直接结果就是部分更新状态的操作对应的事务回滚,导致月底和移动公司对不平账。
[b]获取死锁详细信息[/b]
通过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事务隔离级别的描述(见[url]http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html[/url]),偶终于知道原因了,解决方法也很明显了。
于是乎,做个测试(先说明,偶们的库采用的是默认的事务隔离级别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的事务隔离级别也是默认重复读。改天试试会不会有类似问题。
(
思路参考 [url]http://www.chriscalender.com/?p=426[/url]
关于innodb的概述,可查看 [url]http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/[/url]
)
下面转一个DB2和 Oracle的 DB2和 Oracle的并发控制(锁)比较:
[url]http://sunxboy.iteye.com/blog/387625[/url]
每次节假日之前,公司的业务人员要通过我们开发的短信平台发送大量短信,导致数据库发生死锁。直接结果就是部分更新状态的操作对应的事务回滚,导致月底和移动公司对不平账。
[b]获取死锁详细信息[/b]
通过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事务隔离级别的描述(见[url]http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html[/url]),偶终于知道原因了,解决方法也很明显了。
于是乎,做个测试(先说明,偶们的库采用的是默认的事务隔离级别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的事务隔离级别也是默认重复读。改天试试会不会有类似问题。
(
思路参考 [url]http://www.chriscalender.com/?p=426[/url]
关于innodb的概述,可查看 [url]http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/[/url]
)
下面转一个DB2和 Oracle的 DB2和 Oracle的并发控制(锁)比较:
[url]http://sunxboy.iteye.com/blog/387625[/url]