Bug #93954 A deadlock error occurs when partition is added
Submitted: 17 Jan 3:58 Modified: 5 Feb 1:08
Reporter: kazushige uratani Email Updates:
Status: Not a Bug Impact on me:
None 
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 3:58] kazushige uratani
Description:
Hi!

We are executing the PARTITION addition (deletion) batch in inactive time zones,
The following "Deadlock Error" occurs in PARTITION operation
An event occurs in which a user who is playing forcibly gets an error.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Verified version with:
MySQL Server 5.6.40 (fails)
MySQL Server 8.0.13 (fails)
MariaDB10.3.7 (fails)

transaction_isolation:
READ-COMMITTED

How to repeat:
【Table SetUp】
CREATE DATABASE db_test
;
CREATE TABLE `tbl_deadlock_test` (
  `inc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'inc_id',
  `a_id` bigint(20) unsigned NOT NULL COMMENT 'a_id',
  `b_id` bigint(20) unsigned NOT NULL COMMENT 'b_id',
  `c_time` int(10) unsigned NOT NULL,
  `create_time` datetime NOT NULL COMMENT 'create_time',
  `update_time` datetime NOT NULL COMMENT 'update_time',
  `delete_time` datetime NOT NULL DEFAULT '2017-01-01 00:00:00' COMMENT 'delete_time',
  PRIMARY KEY (`inc_id`,`delete_time`),
  KEY `idx_01` (`a_id`,`c_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tbl_deadlock_test'
/*!50500 PARTITION BY RANGE  COLUMNS(delete_time)
(PARTITION p00000000 VALUES LESS THAN ('20180101') COMMENT = 'default partition' ENGINE = InnoDB,
 PARTITION p20181101 VALUES LESS THAN ('20181102') COMMENT = '2018-11-01' ENGINE = InnoDB,
 PARTITION p20181102 VALUES LESS THAN ('20181103') COMMENT = '2018-11-02' ENGINE = InnoDB,
 PARTITION p20181103 VALUES LESS THAN ('20181104') COMMENT = '2018-11-03' ENGINE = InnoDB,
 PARTITION p20181104 VALUES LESS THAN ('20181105') COMMENT = '2018-11-04' ENGINE = InnoDB,
 PARTITION p20181105 VALUES LESS THAN ('20181106') COMMENT = '2018-11-05' ENGINE = InnoDB,
 PARTITION p20181106 VALUES LESS THAN ('20181107') COMMENT = '2018-11-06' ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
;

-------

【Error Reproduction】

1. Simple "Deadlock error" 

<Procedure>
■TransactionA
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 111111 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (111111, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionB
USE db_test
ALTER TABLE tbl_deadlock_test REORGANIZE PARTITION pmax INTO (PARTITION p20181107 VALUES LESS THAN ('20181108') ENGINE=InnoDB COMMENT='2018-11-07', PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB);

■TransactionC
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 222222 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (222222, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionA
COMMIT;(ROLLBACK;)

<Result>
"Transaction C" becomes a "deadlock error" after COMMIT or rollback in transaction A

-------

2. All transactions executed after PARTITION addition result in "Deadlock error" 

<Procedure>
■TransactionA
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 111111 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (111111, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionB
USE db_test
ALTER TABLE tbl_deadlock_test REORGANIZE PARTITION pmax INTO (PARTITION p20181107 VALUES LESS THAN ('20181108') ENGINE=InnoDB COMMENT='2018-11-07', PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB);

■TransactionC
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 222222 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (222222, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionD
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 333333 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (333333, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionE
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 444444 AND delete_time = '0000-00-00 00:00:00';
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (444444, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionA
COMMIT;(ROLLBACK;)

<Result>
"Transaction C,D,E" becomes a "deadlock error" after COMMIT or rollback in transaction A
Reading metadata when executing SELECT is highly likely to trigger an error

-------

【"CASE" which does not result in Deadlock error】

<Action>
Remove SELECT of "Transaction C" 

<Procedure>
■TransactionA
USE db_test
BEGIN;
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (111111, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionB
USE db_test
ALTER TABLE tbl_deadlock_test REORGANIZE PARTITION pmax INTO (PARTITION p20181107 VALUES LESS THAN ('20181108') ENGINE=InnoDB COMMENT='2018-11-07', PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB);

■TransactionC
USE db_test
BEGIN;
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (222222, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionA
COMMIT;(ROLLBACK;)

<Result>
This CASE will not cause an error.
It is strange that this CASE does not result in Deadlock error,
I think that it will solve if you can take the lock of metadata with SELECT.

-------

【Error avoidance】

<Action>
Lock wait with SELECT of "Transaction A, C" 

<Procedure>
■TransactionA
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 111111 AND delete_time = '0000-00-00 00:00:00' FOR UPDATE;
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (111111, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionB
USE db_test
ALTER TABLE tbl_deadlock_test REORGANIZE PARTITION pmax INTO (PARTITION p20181109 VALUES LESS THAN ('20181110') ENGINE=InnoDB COMMENT='2018-11-09', PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB);

■TransactionC
USE db_test
BEGIN;
SELECT inc_id, a_id, b_id, c_time FROM tbl_deadlock_test  WHERE a_id = 222222 AND delete_time = '0000-00-00 00:00:00' FOR UPDATE;
INSERT INTO tbl_deadlock_test  (a_id, b_id, c_time, create_time, update_time) VALUES (222222, 76331346891, 1540946246, '2018-11-07 15:00:00', '2018-11-07 15:00:00');

■TransactionA
COMMIT;(ROLLBACK;)

-------

【source code debug】

<SHOW PROFILE SOURCE;>
+----------------+----------+-----------------------+--------------+-------------+
| Status         | Duration | Source_function       | Source_file  | Source_line |
+----------------+----------+-----------------------+--------------+-------------+
| starting       | 0.002159 | NULL                  | NULL         |        NULL |
| query end      | 0.000010 | mysql_execute_command | sql_parse.cc |        5046 |
===>OK
| closing tables | 0.000006 | mysql_execute_command | sql_parse.cc |        5094 |
===>OK
| freeing items  | 0.000019 | mysql_parse           | sql_parse.cc |        6521 |
===>OK
| cleaning up    | 0.000014 | dispatch_command      | sql_parse.cc |        1812 |
===>NG

  /* Finalize server status flags after executing a command. */
  thd->update_server_status();
  if (thd->killed)
    thd->send_kill_message();
  thd->protocol->end_statement();←Error(1799)
  query_cache_end_of_result(thd);
+----------------+----------+-----------------------+--------------+-------------+

<debug>
sql/sql_parce.cc
 1799:thd->protocol->end_statement();←breakpoint

-----Step Over-----

sql/sql_connect.cc
 979:    while (thd_is_connection_alive(thd))
 980:    {
 981:      mysql_audit_release(thd);
 982:      if (do_command(thd))←StepIntoExecute
 983:  break;
 984:    }

sql/sql_parse.cc
 972:packet_length= my_net_read(net);←StepIntoExecute

sql/net_serve.cc
 889:len= net_read_packet(net, &complen);←StepIntoExecute

sql/net_serve.cc
 812:if (net_read_packet_header(net))←StepIntoExecute

sql/net_serve.cc
 747:rc= net_read_raw_loop(net, count);←StepIntoExecute

sql/net_serve.cc
 663:  while (count)
 664:    {
 665:      size_t recvcnt= vio_read(net->vio, buf, count);←StepIntoExecute

vio/viosocket.c
 122:    /* Wait for input data to become available. */
 123:    if ((ret= vio_socket_io_wait(vio, VIO_IO_EVENT_READ)))←StepIntoExecute

vio/viosocket.c
   67:/* Wait for input data to become available. */
event:VIO_IO_EVENT_READ、timeout:28800000
   68:switch (vio_io_wait(vio, event, timeout))←StepIntoExecute

vio/viosocket.c
   838:/* The first argument is ignored on Windows. */
   839:ret= select(fd + 1, &readfds, &writefds, &exceptfds, ←Error occurred!!!!!!(select - System call)
   839:                  (timeout >= 0) ? &tm : NULL);

sql/sql_parse.cc
 1779:
  !thd->is_error()←false
  !thd->killed_errno()←true
[17 Jan 15:23] Sinisa Milivojevic
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 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 13:38] Sinisa Milivojevic
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 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 13:20] Sinisa Milivojevic
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 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 13:38] Sinisa Milivojevic
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 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 13:50] Sinisa Milivojevic
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 13:54] Sinisa Milivojevic
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 13:56] Sinisa Milivojevic
Please, run this SQL IMMEDIATELY BEFORE AND AFTER you get that deadlock error.
[25 Jan 14:00] Sinisa Milivojevic
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 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 13:53] Sinisa Milivojevic
You are truly welcome.
[31 Jan 8:40] kazushige uratani
Hi.

Finally, There is one question,
Is there planning to support Online DDL with "ADD PARTITION"?
[31 Jan 13:42] Sinisa Milivojevic
Hi,

To answer your question ...

Yes, there are plans, but we truly do not know anything yet about the scheduling .....
[1 Feb 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 1:59] kazushige uratani
Hi. 

By introducing ONLINE DDL into PARTITION operation,
Do you think you can avoid deadlocks?
[1 Feb 13:53] Sinisa Milivojevic
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 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 17:32] Sinisa Milivojevic
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.