Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.19
-
None
-
Linux
Description
An INSERT INTO SELECT, followed by a DROP of the table being selected from, sometimes leads to a deadlock as both queries are executed in parallel on a secondary slave, and both end up waiting forever. The INSERT thread is waiting on metadata lock, while the DROP thread is waiting on prior transaction commit. Replication is using binlog_format=STATEMENT
PROCESSLIST output:
346 system user NULL Connect 847613 Waiting for master to send event NULL 0.000
|
387 system user some_host Connect 34328 Waiting for table metadata lock INSERT IGNORE INTO some_table select * from other_temp_table 0.000
|
392 system user NULL Connect 34328 Waiting for prior transaction to commit NULL 0.000
|
395 system user NULL Connect 34328 Waiting for prior transaction to commit NULL 0.000
|
402 system user NULL Connect 34328 Waiting for prior transaction to commit NULL 0.000
|
415 system user some_host Connect 34328 Waiting for prior transaction to commit DROP TABLE IF EXISTS `other_table` 0.000
|
427 system user NULL Connect 34359 Waiting for room in worker thread event queue NULL 0.000
|
Related Binlog events (filtered to only show event headers with GTID and the two locking queries):
[...]
|
#160808 23:59:33 server id 187214013 end_log_pos 11324685 GTID 0-187214013-3384686406 cid=533036010
|
#160808 23:59:33 server id 187214013 end_log_pos 11325461 GTID 0-187214013-3384686407 cid=533036010
|
INSERT IGNORE INTO some_table select * from other_table
|
#160808 23:59:33 server id 187214013 end_log_pos 11325720 GTID 0-187214013-3384686408 cid=533036010
|
#160808 23:59:33 server id 187214013 end_log_pos 11326182 GTID 0-187214013-3384686409 cid=533036010
|
#160808 23:59:33 server id 187214013 end_log_pos 11326949 GTID 0-187214013-3384686410 cid=533036010
|
#160808 23:59:33 server id 187214013 end_log_pos 11327246 GTID 0-187214013-3384686411
|
DROP TABLE IF EXISTS `other_table`
|
[...]
|
@@gtid_slave_pos shows 0-187214013-3384686406, the event right before the INSERT