有时,我的应用程序中出现以下死锁错误:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
有问题的表格如下:
CREATE TABLE `oauth_access_token` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `access_token` text COLLATE utf8_unicode_ci NOT NULL, `oauth_client_id` int(10) unsigned NOT NULL, `instance_id` int(10) unsigned DEFAULT NULL, `expires` datetime NOT NULL, PRIMARY KEY (`id`), KEY `FK_oauth_access_token_instance_idx` (`instance_id`), KEY `FK_oauth_access_token_oauth_client_idx` (`oauth_client_id`), CONSTRAINT `FK_oauth_access_token_instance` FOREIGN KEY (`instance_id`) REFERENCES `instance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_oauth_access_token_oauth_client` FOREIGN KEY (`oauth_client_id`) REFERENCES `oauth_client` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
我有一个运行这些查询的事务,我相信它们会导致死锁:
DELETE oauth_access_token_scope FROM oauth_access_token_scope INNER JOIN oauth_access_token ON (oauth_access_token_scope.oauth_access_token_id = oauth_access_token.id) WHERE oauth_access_token.expires <= UTC_TIMESTAMP() DELETE FROM oauth_access_token WHERE expires <= UTC_TIMESTAMP() DELETE oauth_jti FROM oauth_jti WHERE oauth_jti.expires <= UTC_TIMESTAMP()
如果我运行SHOW ENGINE INNODB STATUS
,记录的死锁信息是:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2014-02-03 10:36:45 7ffc5ded2700 *** (1) TRANSACTION: TRANSACTION 3228803, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1248, 15 row lock(s) MySQL thread id 2692, OS thread handle 0x7ffc5dcca700, query id 36603 localhost 127.0.0.1 root updating *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 20338 page no 3 n bits 88 index `PRIMARY` of table `app`.`oauth_access_token` trx id 3228803 lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 3228804, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 15 row lock(s) MySQL thread id 2693, OS thread handle 0x7ffc5ded2700, query id 36604 localhost 127.0.0.1 root updating DELETE FROM oauth_access_token WHERE expires <= UTC_TIMESTAMP() *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 20338 page no 3 n bits 88 index `PRIMARY` of table `app`.`oauth_access_token` trx id 3228804 lock mode S *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 20338 page no 3 n bits 88 index `PRIMARY` of table `app`.`oauth_access_token` trx id 3228804 lock_mode X waiting *** WE ROLL BACK TRANSACTION (2)
虽然我理解为什么会出现死锁的理论,但我不确定为什么会出现这种情况.我认为当两个同时发生的用户请求导致这些语句同时执行时会发生死锁.
为什么在这种情况下发生死锁?可以做些什么(除了重试事务)以使其"安全"以便不会发生死锁?