带有两个相同查询(不同参数)的MySQL InnoDB死锁问题 [英] MySQL InnoDB deadlock problem with two same queries (different parameters)
问题描述
我有下表
CREATE TABLE IF NOT EXISTS `task` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`job_id` int(10) unsigned NOT NULL COMMENT 'The id of the related job',
`server_id` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'job/task owner',
`jobtype_id` int(10) unsigned NOT NULL DEFAULT '0',
`node_id` int(10) unsigned NOT NULL COMMENT 'The id of the user currently executing this task',
`status` enum('QUEUED','EXECUTING','COMPLETED','CANCELED','TERMINATED','PAUSED','FAILED') NOT NULL COMMENT 'Current status of the task',
`last_updated` int(11) NOT NULL COMMENT 'When was the last status change of this task. Used in requeueing hung tasks',
`data_in` blob NOT NULL COMMENT 'An input data to the task. Sets when the task is created.',
`data_out` blob NOT NULL COMMENT 'An output data of the task. Sets upon task completion. Can be absent.',
`speed` bigint(20) unsigned NOT NULL DEFAULT '0',
`time_spent` int(11) NOT NULL DEFAULT '0',
`has_data_out` tinyint(1) NOT NULL COMMENT 'Shows if the task has any output data. Serves caching purposes. Used by Summarizers to quickly find out what tasks of the job yielded data.',
`comment` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_task_job_id` (`job_id`),
KEY `index_has_data_out` (`has_data_out`),
KEY `index_last_updated` (`last_updated`),
KEY `index_status` (`status`),
KEY `fk_task_userid` (`node_id`),
KEY `jobtype_id` (`jobtype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This table holds all subjobs - tasks' AUTO_INCREMENT=1081595 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `task`
--
ALTER TABLE `task`
ADD CONSTRAINT `task_ibfk_5` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `task_ibfk_7` FOREIGN KEY (`jobtype_id`) REFERENCES `jobtype` (`id`),
ADD CONSTRAINT `task_ibfk_8` FOREIGN KEY (`node_id`) REFERENCES `node` (`id`);
以及以下死锁问题:
------------------------
LATEST DETECTED DEADLOCK
------------------------
110831 14:23:56
*** (1) TRANSACTION:
TRANSACTION 102B4D2, ACTIVE 0 sec, OS thread id 5480
mysql tables in use 2, locked 1
LOCK WAIT 7 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 3
MySQL thread id 74315, query id 2364347 192.168.1.120 usr_sl3 Sending data
select `usr_sl3`.`task`.`id` from `usr_sl3`.`task` where (`usr_sl3`.`task`.`node_id` = 103 and `usr_sl3`.`task`.`status` = 'EXECUTING') for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 2303 n bits 576 index `index_status` of table `usr_sl3`.`task` trx id 102B4D2 lock_mode X locks rec but not gap waiting
Record lock, heap no 471 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 02; asc ;;
1: len 4; hex 00107dac; asc } ;;
*** (2) TRANSACTION:
TRANSACTION 102B4D3, ACTIVE 0 sec, OS thread id 5692 starting index read, thread declared inside InnoDB 500
mysql tables in use 2, locked 1
7 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 3
MySQL thread id 74354, query id 2364348 192.168.1.120 usr_sl3 Sending data
select `usr_sl3`.`task`.`id` from `usr_sl3`.`task` where (`usr_sl3`.`task`.`node_id` = 95 and `usr_sl3`.`task`.`status` = 'EXECUTING') for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 2303 n bits 576 index `index_status` of table `usr_sl3`.`task` trx id 102B4D3 lock_mode X locks rec but not gap
Record lock, heap no 471 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 02; asc ;;
1: len 4; hex 00107dac; asc } ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 2303 n bits 576 index `index_status` of table `usr_sl3`.`task` trx id 102B4D3 lock_mode X locks rec but not gap waiting
Record lock, heap no 481 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 02; asc ;;
1: len 4; hex 00107dab; asc } ;;
*** WE ROLL BACK TRANSACTION (2)
能否请您帮助我了解这种僵局的机制?
Can you please help me to understand the mechanism of this deadlock?
这两个查询是从不同的线程发出的.每个线程在查询中都有其自己的node_id.没有两个查询具有相同的node_id.
Those two queries are issued from different threads. Each thread has it's own node_id in query. No two queries have same node_id.
我怀疑,可以通过在字段(node_id,status)上创建复合索引来解决这种情况,但这并不是一个好的解决方案.我需要了解问题的本质.
I suspect, that I can solve the situation by creating a compound index on fields (node_id, status), but this isn't a good solution, I think. I need to understand the nature of the problem.
这些在同一查询上的死锁是定期发生的,而不是一次或两次.
These deadlocks on the same query occure periodically, not once or twice.
对受影响的查询进行解释会得出有趣的结果:
EXPLAIN on affected queries gives interesting results:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE task index_merge index_status,fk_task_userid index_status,fk_task_userid 1,4 NULL 1 Using intersect(index_status,fk_task_userid); Using where; Using index
MySQL版本为5.5.
MySQL version is 5.5.
此外,在出现死锁时,表中不包含与受影响查询的条件匹配的行(例如,从usr_sl3
.task
中选择usr_sl3
.task
.id
,其中(usr_sl3
. task
.node_id
= 95和usr_sl3
.task
.status
='EXECUTING')以进行更新
完全不产生任何行.)
Also, at the moment of the deadlock, tables contain no rows matching condition of the affected query (e.g. select usr_sl3
.task
.id
from usr_sl3
.task
where (usr_sl3
.task
.node_id
= 95 and usr_sl3
.task
.status
= 'EXECUTING') for update
yields no rows at all).
谢谢.
推荐答案
查询使用index_status索引而不是fk_task_userid(node_id上的索引).这就是它使用其他node_id锁定记录的原因.
The query uses the index_status index instead of fk_task_userid (the index on node_id). This is the reason it locks records with other node_ids.
您可以在查询中运行解释,以查看实际锁定了多少记录(在检查的行中)以及需要锁定多少记录(返回的行)
You can run explain on your query to see how much records are actually locked (in rows examined) vs how much of them you need to lock (rows returned)
我怀疑,可以通过在字段(node_id,status)上创建复合索引来解决这种情况,但这并不是一个好的解决方案.我需要了解问题的本质.
I suspect, that I can solve the situation by creating a compound index on fields (node_id, status), but this isn't a good solution, I think. I need to understand the nature of the problem.
为什么?我觉得您的索引仍然不是最佳...在node_id,status上创建索引,它应该可以解决问题
Why? I feel your indexes are not optimal anyway... Create index on node_id,status, it should resolve the problem
这篇关于带有两个相同查询(不同参数)的MySQL InnoDB死锁问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!