记一次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