MySQL Innodb的死锁问题

今天遇到了数据库的死锁问题，导致应用无法提供服务。当时只好重启tomcat，现在还是会不断的提示死锁，只是服务还能跑。

引用

100528 14:16:03
*** (1) TRANSACTION:
TRANSACTION 1 720592357, ACTIVE 0 sec, process no 3588, OS thread id 1337043264 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 4380151, query id 549555384 192.168.0.98 root update
insert into by_useronline(byid,logintime,nickname,sex,birthday,area) values(31936074,now(),'????',1,'1989-11-14',119)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 827664 n bits 1056 index `byid` of table `byread/by_useronline` trx id 1 720592357 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 400 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 01e74ed9; asc   N ;; 1: len 4; hex 16d9283d; asc   (=;;

*** (2) TRANSACTION:
TRANSACTION 1 720592355, ACTIVE 0 sec, process no 3588, OS thread id 1178097984 setting auto-inc lock, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216
MySQL thread id 4380129, query id 549555386 192.168.0.98 root update
insert into by_useronline(byid,logintime,nickname,sex,birthday,area) values(35707122,now(),'?,1,'1991-06-22',1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 827664 n bits 1056 index `byid` of table `byread/by_useronline` trx id 1 720592355 lock_mode X locks gap before rec
Record lock, heap no 400 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 01e74ed9; asc   N ;; 1: len 4; hex 16d9283d; asc   (=;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `byread/by_useronline` trx id 1 720592355 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)

InnoDB会自动的检测死锁进行回滚，或者终止死锁的情况。

引用

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

如果参数innodb_table_locks=1并且autocommit=0时，InnoDB会留意表的死锁，和MySQL层面的行级锁。另外，InnoDB不会检测MySQL的Lock Tables命令和其他存储引擎死锁。
你应该设置innodb_lock_wait_timeout来解决这种情况。

innodb_lock_wait_timeout是Innodb放弃行级锁的超时时间。

引用

InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise, InnoDB cannot detect deadlocks where a table lock set by a MySQL LOCK TABLES  statement or a lock set by a storage engine other than InnoDB is involved. You must resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.