Bug #93954 | A deadlock error occurs when partition is added | ||
---|---|---|---|
Submitted: | 17 Jan 2019 3:58 | Modified: | 5 Feb 2019 1:08 |
Reporter: | kazushige uratani | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.6.40, 8.0.13 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any | |
Tags: | deadlock, innodb, partition |
[17 Jan 2019 3:58]
kazushige uratani
[17 Jan 2019 15:23]
MySQL Verification Team
Hi , Thank you for your report. Deadlocks are to be expected with the transactional storage engines, like with InnoDB storage engine. Most likely, since you do not have an adequate index, deadlock occurs due to the scanning of some partition. Hence, next time when you get a deadlock report, please send us: * The output from SHOW ENGINE INNODB STATUS * Last couple of pages of the MySQL error log * Output of the EXPLAIN for all the statements involved in the deadlock Thank you very much in advance.
[22 Jan 2019 6:21]
kazushige uratani
Hi Sinisa quick response Thank you I checked the following information in the production environment, but there was no information * The output from SHOW ENGINE INNODB STATUS * Last couple of pages of the MySQL error log So. We reproduced the error with a more simple procedure. Also, I cite the data model from the MySQL document. Please refer to the following URL. https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html ##################################### Please follow the procedure below. There should be a deadlock error ##################################### 【CASE1】 <Preparation> CREATE DATABASE db_test; USE db_test CREATE TABLE `tr` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */; <Procedure> ■TransactionA USE db_test BEGIN; SELECT id, name, purchased FROM tr WHERE id = 11; ■TransactionB USE db_test ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2019)); ■TransactionA INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 【CASE2】 <Preparation> USE db_test CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE ) PARTITION BY RANGE( YEAR(dob) ) ( PARTITION p0 VALUES LESS THAN (1970), PARTITION p1 VALUES LESS THAN (1980), PARTITION p2 VALUES LESS THAN (1990) ); <Procedure> ■TransactionA USE db_test BEGIN; SELECT id, fname, lname, dob FROM members WHERE id = 1; ■TransactionB USE db_test ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)); ■TransactionA INSERT INTO members (id, fname, lname, dob) VALUES (1, 'fname', 'lname', now()); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ##################################### A meta data lock occurs when ALTER TABLE is executed After that, when you execute the "INSERT" it will be an Deadlock ##################################### 【CASE1】 *************************** 3. row *************************** Id: 23 User: root Host: localhost db: db_test Command: Query Time: 153 State: Waiting for table metadata lock Info: ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2019)) 【CASE2】 *************************** 3. row *************************** Id: 23 User: root Host: localhost db: db_test Command: Query Time: 7 State: Waiting for table metadata lock Info: ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000))
[22 Jan 2019 13:38]
MySQL Verification Team
Hi, Seems that you are using partitions engine and not native InnoDB partitioning. This would explain MDL deadlocks, as in this case it is expected behaviour for the set of parallel commands that you are using. Please, check it out.
[23 Jan 2019 6:09]
kazushige uratani
Hi. All the above steps are the result of running on MySQL 8.0. So I do not use the partition storage engine. Is my procedure incorrect? Can you tell me how I can not get an error? Can you tell me whether it is a bug or a specification? ------------------------------------------------------ mysql> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.13 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT -> PLUGIN_NAME as Name, -> PLUGIN_VERSION as Version, -> PLUGIN_STATUS as Status -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_TYPE='STORAGE ENGINE'; +--------------------+---------+----------+ | Name | Version | Status | +--------------------+---------+----------+ | binlog | 1.0 | ACTIVE | | CSV | 1.0 | ACTIVE | | MEMORY | 1.0 | ACTIVE | | InnoDB | 8.0 | ACTIVE | | MyISAM | 1.0 | ACTIVE | | MRG_MYISAM | 1.0 | ACTIVE | | PERFORMANCE_SCHEMA | 0.1 | ACTIVE | | TempTable | 1.0 | ACTIVE | | ARCHIVE | 3.0 | ACTIVE | | BLACKHOLE | 1.0 | ACTIVE | | FEDERATED | 1.0 | DISABLED | +--------------------+---------+----------+ 11 rows in set (0.00 sec) ------------------------------------------------------
[23 Jan 2019 13:20]
MySQL Verification Team
HI, When you get this error: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction you should run SHOW ENGINE INNODB STATUS and then send us last 4 - 5 pages of the error log. If there is nothing useful in the error log, then you should enable performance_schema and us the outputs from the tables that display MetaDataLocks. Thanks in advance.
[24 Jan 2019 2:51]
kazushige uratani
Hi. I got a log. Just to be sure we will get "SHOW ENGINE INNODB STATUS". Please check the attached file. Despite waiting due to "MDL" when "ALTER" is executed, Is not it a bug that an error occurs when "INSERT" is executed? There is no problem if all the transactions wait for lock, I think that it is a problem that INSERT is canceled by error. mysql> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.13 | +-----------+ 1 row in set (0.00 sec) mysql> USE db_test Database changed mysql> CREATE TABLE `tr` ( -> `id` int(11) DEFAULT NULL, -> `name` varchar(50) DEFAULT NULL, -> `purchased` date DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50100 PARTITION BY RANGE ( YEAR(purchased)) -> (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, -> PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB, -> PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB, -> PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB, -> PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */; Query OK, 0 rows affected (0.15 sec) mysql> USE db_test Database changed mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT id, name, purchased FROM tr WHERE id = 11; Empty set (0.01 sec) mysql> USE db_test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2019)); Query OK, 0 rows affected (12.89 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql> mysql> SHOW ENGINE INNODB STATUS\G Please check the attached file. mysql> SELECT * FROM performance_schema.metadata_locks\G *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks COLUMN_NAME: NULL OBJECT_INSTANCE_BEGIN: 139917553584208 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5744 OWNER_THREAD_ID: 77 OWNER_EVENT_ID: 154 1 row in set (0.00 sec)
[24 Jan 2019 13:38]
MySQL Verification Team
HI, A deadlock did not occur within InnoDB storage engine at all. Actually, you have either restarted MySQL server or you did not have InnoDB deadlock at all. It is possible that this is MDL deadlocks. Which is why we have asked, that when ever you get that error, you send us the output from all Performance_Schema tables related to Metadata Locks. This is what we need for full diagnosis. Thanks in advance.
[25 Jan 2019 3:27]
kazushige uratani
Hi. I knew that this error is not a broad deadlock before issuing this bug report. And until now I have provided a procedure to reproduce 100% of this error, but there was no word as to whether this error could be improved or not. I would like you to conclude, Do you say that MySQL does not intend to improve this error? I will give up on this error if I am not planning to improve this error or if I can not improve it.
[25 Jan 2019 13:50]
MySQL Verification Team
Hi, Deadlock is a consequence of the concurrent statements that are issued by your application. Deadlocks can not be avoided, except by careful design of the SQL statements. This is not an error in MySQL. Deadlocks are occurring in all transactional databases and are simply a consequence of the wrong order. Transaction X first locks object A and then goes for B. Transaction Y first locks object B and goes for A. Hence, a deadlock. In your case, it does not seem to be an InnoDB deadlock, unless your status was taken after server reboot. Hence, that is why we asked for the output from the relevant tables in P_S.
[25 Jan 2019 13:54]
MySQL Verification Team
Here is the exact output that we require in order to diagnose this MDL deadlock. However, this is not a bug. We are doing it just to help you: select OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks where OBJECT_SCHEMA like 'db_test'; Change db_test to the real name of your schema.
[25 Jan 2019 13:56]
MySQL Verification Team
Please, run this SQL IMMEDIATELY BEFORE AND AFTER you get that deadlock error.
[25 Jan 2019 14:00]
MySQL Verification Team
Hi, We have analysed the entire scenario based on your test case. Looking at the simplified cases towards the end of the report, we see that transaction A does a SELECT, which will grab a read lock on the table, then, concurrently in another transaction, we run ALTER, which will first acquire a shared upgradable lock, which it will get, and continue until it tries to upgrade it to an exclusive lock. At that point, it will wait for the lock held by transaction A to be released, which will not happen until A commits or rolls back. At the same time, in transaction A, we issue an INSERT, which will wait for a write lock. Thus we have a deadlock, which is detected, and the INSERT is chosen as victim, and hence the resulting error. Transaction A thus will rollback, the ALTER will get its request for an exclusive lock granted, and it will commit. Hence, this is all expected behaviour and is documented in our Reference Manual, chapter on Metadata locks. Not a bug.
[28 Jan 2019 3:47]
kazushige uratani
Hi. Finally I got the advice I am seeking. Actually, I was also verifying that the solution to this deadlock can be solved by adding "FOR UPDATE". (I also wrote in "Error avoidance" which first described this report) Since we thought that if the metadata lock could completely lock ALTER, we did not need to deadlock with INSERT, Is it an error? I thought. If I could improve it I wanted to improve. However, if it says that this is not a bug, we assume that errors occurring in the production environment can not be helped. Correspondence so far. Thank you very much.
[28 Jan 2019 13:53]
MySQL Verification Team
You are truly welcome.
[31 Jan 2019 8:40]
kazushige uratani
Hi. Finally, There is one question, Is there planning to support Online DDL with "ADD PARTITION"?
[31 Jan 2019 13:42]
MySQL Verification Team
Hi, To answer your question ... Yes, there are plans, but we truly do not know anything yet about the scheduling .....
[1 Feb 2019 1:35]
kazushige uratani
Hi. It was good just to find out that we are considering the correspondence of "Online DDL". By the way, is not it not only "ADD PARTITION" but is it being considered for all partitioning operations? For example, "DROP PARTITION".
[1 Feb 2019 1:59]
kazushige uratani
Hi. By introducing ONLINE DDL into PARTITION operation, Do you think you can avoid deadlocks?
[1 Feb 2019 13:53]
MySQL Verification Team
Hi, First of all, ONLINE DDL for partitions would not prevent deadlocks. Second, deadlocks are to be expected in transactional engines, so you have to deal with them in your application. Regarding the feature requests, technical discussions are still ongoing ......
[5 Feb 2019 1:08]
kazushige uratani
Hi. Thank you for the advice. We deal with this deadlock if we can deal with the application. And on improving ONLINE DDL on partitioning I will look forward to it.
[7 Feb 2019 17:32]
MySQL Verification Team
Hi, Just for your information. This report can not be treated as a feature request, for which there exists a very good reason. We consider that not involving SQL-layer at all in such ALTER is possible, in theory, such approach contradicts to current direction in which our server evolves - e.g. using unified data-dictionary for both SQL-layer and storage engines. In our opinion, the problem can be solved by implementing support for fully non-blocking DDL. But it is really long path to get there. Exempli gratia, to achieve this we need to support several versions of table at the same time in several places. Among others, in data dictionary, in memory, in InnoDB and binlog and so on and so forth. I hope that we have been clear enough.