mysql innodb 死锁_MySQL Innodb的死锁问题

引用

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.

分享到：

2010-05-28 14:30

浏览 3764

分类:数据库

评论