| Bug #99768 | Unnecessary Deadlock running INSERT LOW_PRIORITY IGNORE | ||
|---|---|---|---|
| Submitted: | 3 Jun 2020 14:11 | Modified: | 4 Jun 2020 12:23 |
| Reporter: | L Willis | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.29 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any | |
[3 Jun 2020 14:13]
L Willis
Output from SHOW ENGINE INNODB STATUS
Attachment: innodb_status.txt (text/plain), 20.15 KiB.
[4 Jun 2020 12:23]
MySQL Verification Team
Hi Mr. Willis, Thank you for your bug report. However, this is not a bug, at all. This is a very well documented behaviour. Documented in our Reference Manual. You are using InnoDB storage engine. That means, that even if you do not start explicitly a transaction, or even if you are using autocommit mode only, transactions are automatically started. Your INSERT is translated, with InnoDB, like this: START TRANSACTION INSERT INTO ...... COMMIT Deadlock that you get is a classical deadlock due to the lock escalation. Since you have two parallel INSERT... vying for the same X locks, it is normal that you get a deadlock. Deadlock is only a proof that InnoDB SE functions properly. You should change your application, so that rolled back transaction is committed. This is all fully explained in our Reference Manual. Not a bug.
Description: I have the following table definition: CREATE TABLE `wc_say_what_available_strings` ( `orig_string` text COLLATE utf8mb4_bin NOT NULL, `domain` varchar(255) COLLATE utf8mb4_bin NOT NULL, `context` text COLLATE utf8mb4_bin NOT NULL, `translated_string` text COLLATE utf8mb4_bin NOT NULL, `orig_string_lc` text COLLATE utf8mb4_bin NOT NULL, `translated_string_lc` text COLLATE utf8mb4_bin NOT NULL, UNIQUE KEY `arg_index` (`orig_string`(110),`domain`(24),`context`(32),`translated_string`(25)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERTS into this table are being carried out by an INSERT LOW_PRIORITY IGNORE, inserting multiple rows at once. I'm seeing deadlocks reported even though I can't see a reason why they should be. I'm not explicitly starting any transactions. An example INSERT that's failing is: INSERT LOW_PRIORITY IGNORE INTO wc_say_what_available_strings( orig_string, domain, context, translated_string, orig_string_lc, translated_string_lc ) VALUES ('Indicates whether all the conditions should be true for the resulting set, or if any one of them is sufficient. Match affects the following parameters: products, orders, username, ip_address.','woocommerce','sw-default-context','Indicates whether all the conditions should be true for the resulting set, or if any one of them is sufficient. Match affects the following parameters: products, orders, username, ip_address.','indicates whether all the conditions should be true for the resulting set, or if any one of them is sufficient. match affects the following parameters: products, orders, username, ip_address.','indicates whether all the conditions should be true for the resulting set, or if any one of them is sufficient. match affects the following parameters: products, orders, username, ip_address.'),('The REST API can no longer be completely disabled, the %s filter can be used to restrict access to the API, instead.','default','sw-default-context','The REST API can no longer be completely disabled, the %s filter can be used to restrict access to the API, instead.','the rest api can no longer be completely disabled, the %s filter can be used to restrict access to the api, instead.','the rest api can no longer be completely disabled, the %s filter can be used to restrict access to the api, instead.') SHOW ENGINE INNODB STATUS output attached. How to repeat: Create table as per bug report, and run simultaneous inserts as per bug report.