MySQL Bugs: #81002: Concurrent DML via LOCK=NONE does not error on tables with foreign keys.

Bug #81002	Concurrent DML via LOCK=NONE does not error on tables with foreign keys.
Submitted:	8 Apr 2016 3:00	Modified:	19 Apr 2016 19:02
Reporter:	Derek Downey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	foreign keys

Description:
Using LOCK=NONE on a child table (table that contains a foreign key constraint) should return an error. Instead it allows the statement but blocks until transactions are committed.

From the documentation regarding FK limitations of Online DDL:

"Concurrent DML is disallowed during online DDL operations on such child tables. (This restriction is being evaluated as a bug and might be lifted.)"

src: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

This means that LOCK=NONE should not be allowed, if explicitly called. If LOCK=NONE is not allowed, there is an error called:

"LOCK = NONE
If supported, permit concurrent reads and writes. Otherwise, return an error message.
"
src: https://dev.mysql.com/doc/refman/5.6/en/alter-table.html

How to repeat:
Create schema:

CREATE TABLE foo (
  id int unsigned PRIMARY KEY auto_increment
);

CREATE TABLE bar (
  id int unsigned PRIMARY KEY auto_increment,
  foo_id int unsigned,
  FOREIGN KEY (foo_id) 
    REFERENCES foo(id)
    ON DELETE CASCADE
);

INSERT INTO bar (foo_id) VALUES (1);

===

In second session, open a transaction to read from table:
  SET autocommit=0;
  SELECT * FROM bar;

In first session, attempt to alter the table with online DDL:

  ALTER TABLE `bar`
  ADD COLUMN `foobar` tinyint,
  ALGORITHM=INPLACE, LOCK=NONE;

This will block until second transaction is committed or rolled back.

Suggested fix:
Return an error if LOCK=NONE is not supported on foreign key tables (either parent or child)

Actually, after some sleep and some coffee, the 'how to repeat section' only shows that MDL locks are holding up the operation, not that concurrent DML is being blocked or disallowed.

Reduced severity to S3. 

Further tests show that I am able to do concurrent inserts during the operation to add a column, so it appears concurrent DML is allowed on an online DDL to add a column to the table. 

If this is the case, the documentation regarding concurrent DML on tables containing FKs is unclear (or outdated).

Hi Derek,

I have done some tests and I agree with you. This is not properly documented.

Verified as a documentation bug.

Posted by developer:
 
Verified support for concurrent DML in this scenario back to an earlier version of 5.6. The problematic information in the Online DDL documentation was removed. The change should appear online soon.
Thank you for the bug report.