MySQL Bugs: #21483: Server abort or deadlock on INSERT DELAYED with another implicit insert

Bug #21483	Server abort or deadlock on INSERT DELAYED with another implicit insert
Submitted:	7 Aug 2006 15:37	Modified:	27 May 2007 9:34
Reporter:	Tomash Brechko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.24, 5.1	OS:	Linux (FC3)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
INSERT DELAYED will abort or deadlock if the statement tries to implicitly insert into the same table.

How to repeat:
Abort:

CREATE TABLE t1 (i INT);
delimiter |
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO t1 VALUES (1);
  RETURN 1;
END |
delimiter ;

-- This error is correct.
INSERT INTO t1 VALUES (f1());
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

-- However:
INSERT DELAYED INTO t1 VALUES (f1());
mysqld: sql_base.cc:2095: int open_tables(THD*, TABLE_LIST**, uint*, uint): Assertion `thd->lex->query_tables == *start' failed.

Deadlock:

CREATE TABLE t1 (i INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
  INSERT INTO t1 VALUES (NEW.i);

-- This error is correct.
INSERT INTO t1 VALUES (1);
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

-- However:
INSERT DELAYED INTO t1 VALUES (1);
-- We are in a deadlock.

Suggested fix:
Revise table locking with respect to INSERT DELAYED.

Verified, see attached test cases

mysqltest test cases

Attachment: bug21483.tgz (application/x-gtar, text), 995 bytes.

Bug#21714 relates to this bug.

Bug#20497 and bug#21714 are duplicates of this bug.

The patch was erroneously linked to bug#21714, which is a duplicate of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10781

ChangeSet@1.2235, 2006-08-23 21:12:02+04:00, kroki@moonlight.intranet +4 -0
  BUG#21483: Server abort or deadlock on INSERT DELAYED with another
             implicit insert
  
  If the statement operates on several tables via functions or triggers,
  it is vital to execute all operations at one time, none of the actions
  can be delayed, because all tables should be locked simultaneously.
  
  The solution is to downgrade INSERT DELAYED to normal INSERT if the
  statement uses functions that access tables or triggers, or is called
  from a function or a trigger.  This also fixes bug#20497 (Trigger with
  INSERT DELAYED causes Error 1165) and bug#21714 (Wrong NEW.value and
  server abort on INSERT DELAYED to a table with a trigger).
  
  REPLACE DELAYED is handled by the same code.

Sent review comments by email.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19009

ChangeSet@1.2387, 2007-01-30 16:20:17+03:00, kroki@moonlight.home +6 -0
  BUG#21483: Server abort or deadlock on INSERT DELAYED with another
             implicit insert
  
  If the statement operates on several tables via functions or triggers,
  it is vital to execute all operations at one time, none of the actions
  can be delayed, because all tables should be locked simultaneously.
  
  The solution is to downgrade INSERT DELAYED to normal INSERT if the
  statement uses functions that access tables or triggers, or is called
  from a function or a trigger.  This also fixes bug#20497 (Trigger with
  INSERT DELAYED causes Error 1165) and bug#21714 (Wrong NEW.value and
  server abort on INSERT DELAYED to a table with a trigger).
  
  REPLACE DELAYED is handled by the same code.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19027

ChangeSet@1.2387, 2007-01-30 21:08:07+03:00, kroki@moonlight.home +6 -0
  BUG#21483: Server abort or deadlock on INSERT DELAYED with another
             implicit insert
  
  If the statement operates on several tables via functions or triggers,
  it is vital to execute all operations at one time, none of the actions
  can be delayed, because all tables should be locked simultaneously.
  
  The solution is to downgrade INSERT DELAYED to normal INSERT if the
  statement uses functions that access tables or triggers, or is called
  from a function or a trigger.  This also fixes bug#20497 (Trigger with
  INSERT DELAYED causes Error 1165) and bug#21714 (Wrong NEW.value and
  server abort on INSERT DELAYED to a table with a trigger).
  
  REPLACE DELAYED is handled by the same code.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26487

ChangeSet@1.2477, 2007-05-11 14:28:53+04:00, kostja@vajra.(none) +6 -0
  A fix and a test case for 
  Bug#21483 "Server abort or deadlock on INSERT DELAYED with another
  implicit insert"
  Also fixes and adds test cases for bugs:
  20497 "Trigger with INSERT DELAYED causes Error 1165"
  21714 "Wrong NEW.value and server abort on INSERT DELAYED to a
  table with a trigger"
  
  Problem:
  In MySQL INSERT DELAYED is a way to pipe all inserts into a
  given table through a dedicated thread. This is necessary for
  simplistic storage engines like MyISAM, which do not have internal
  concurrency control or threading and thus can not
  achieve efficient INSERT throughput without support from SQL layer.
  DELAYED INSERT works as follows:
  For every distinct table, which can accept DELAYED inserts and has
  pending data to insert, a dedicated thread is created to write data
  to disk. All user connection threads that attempt to
  delayed-insert into this table interact with the dedicated thread in
  producer/consumer fashion: all records to-be inserted are pushed
  into a queue of the dedicated thread, which fetches the records and 
  writes them.
  In this design, client connection threads never open or lock
  the delayed insert table.
  This functionality was introduced in version 3.23 and does not take 
  into account existence of triggers, views, or pre-locking.
  E.g. if INSERT DELAYED is called from a stored function, which,
  in turn, is called from another stored function that uses the delayed
  table, a deadlock can occur, because delayed locking by-passes
  pre-locking. Besides:
   * the delayed thread works directly with the subject table through
     the storage engine API and does not invoke triggers
   * even if it was patched to invoke triggers, if triggers,
     in turn, used other tables, the delayed thread would
     have to open and lock involved tables (use pre-locking).
   * even if it was patched to use pre-locking, without deadlock
     detection the delayed thread could easily lock out user 
     connection threads in case when the same table is used both
     in a trigger and on the right side of the insert query: 
     the delayed thread would not release locks until all inserts 
     are complete, and user connection can not complete inserts 
     without having locks on the tables used on the right side of the
     query.
  
  Solution:
  
  These considerations suggest two general alternatives for the
  future of INSERT DELAYED:
   * it is considered a full-fledged alternative to normal INSERT
   * it is regarded as an optimisation that is only relevant 
     for simplistic engines.
  Since we missed our chance to provide complete support of new
  features when 5.0 was in development, the first alternative
  currently renders infeasible.
  However, even the second alternative, which is to detect
  new features and convert DELAYED insert into a normal insert, 
  is not easy to implement.
  The catch-22 is that we don't know if the subject table has triggers
  or is a view before we open it, and we only open it in the
  delayed thread. We don't know if the query involves pre-locking
  until we have opened all tables, and we always first create
  the delayed thread, and only then open the remaining tables.
  This patch detects the problematic scenarios and converts
  DELAYED INSERT to a normal INSERT:
   * if the statement is executed under pre-locking (e.g. from
     within a stored function or trigger) or the right
     side may require prelocking, we detect the situation
     before creating a delayed insert thread and convert the statement
     to a conventional INSERT.
    * if the subject table is a view or has triggers, we shutdown
     the delayed thread and convert the statement to a conventional
     INSERT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26620

ChangeSet@1.2477, 2007-05-14 19:23:31+04:00, kostja@vajra.(none) +6 -0
  Recommit with diff-p-helper
  A fix and a test case for 
  Bug#21483 "Server abort or deadlock on INSERT DELAYED with another
  implicit insert"
  Also fixes and adds test cases for bugs:
  20497 "Trigger with INSERT DELAYED causes Error 1165"
  21714 "Wrong NEW.value and server abort on INSERT DELAYED to a
  table with a trigger"
  
  Problem:
  In MySQL INSERT DELAYED is a way to pipe all inserts into a
  given table through a dedicated thread. This is necessary for
  simplistic storage engines like MyISAM, which do not have internal
  concurrency control or threading and thus can not
  achieve efficient INSERT throughput without support from SQL layer.
  DELAYED INSERT works as follows:
  For every distinct table, which can accept DELAYED inserts and has
  pending data to insert, a dedicated thread is created to write data
  to disk. All user connection threads that attempt to
  delayed-insert into this table interact with the dedicated thread in
  producer/consumer fashion: all records to-be inserted are pushed
  into a queue of the dedicated thread, which fetches the records and 
  writes them.
  In this design, client connection threads never open or lock
  the delayed insert table.
  This functionality was introduced in version 3.23 and does not take 
  into account existence of triggers, views, or pre-locking.
  E.g. if INSERT DELAYED is called from a stored function, which,
  in turn, is called from another stored function that uses the delayed
  table, a deadlock can occur, because delayed locking by-passes
  pre-locking. Besides:
   * the delayed thread works directly with the subject table through
     the storage engine API and does not invoke triggers
   * even if it was patched to invoke triggers, if triggers,
     in turn, used other tables, the delayed thread would
     have to open and lock involved tables (use pre-locking).
   * even if it was patched to use pre-locking, without deadlock
     detection the delayed thread could easily lock out user 
     connection threads in case when the same table is used both
     in a trigger and on the right side of the insert query: 
     the delayed thread would not release locks until all inserts 
     are complete, and user connection can not complete inserts 
     without having locks on the tables used on the right side of the
     query.
  
  Solution:
  
  These considerations suggest two general alternatives for the
  future of INSERT DELAYED:
   * it is considered a full-fledged alternative to normal INSERT
   * it is regarded as an optimisation that is only relevant 
     for simplistic engines.
  Since we missed our chance to provide complete support of new
  features when 5.0 was in development, the first alternative
  currently renders infeasible.
  However, even the second alternative, which is to detect
  new features and convert DELAYED insert into a normal insert, 
  is not easy to implement.
  The catch-22 is that we don't know if the subject table has triggers
  or is a view before we open it, and we only open it in the
  delayed thread. We don't know if the query involves pre-locking
  until we have opened all tables, and we always first create
  the delayed thread, and only then open the remaining tables.
  This patch detects the problematic scenarios and converts
  DELAYED INSERT to a normal INSERT using the following approach:
   * if the statement is executed under pre-locking (e.g. from
     within a stored function or trigger) or the right
     side may require pre-locking, we detect the situation
     before creating a delayed insert thread and convert the statement
     to a conventional INSERT.
    * if the subject table is a view or has triggers, we shutdown
     the delayed thread and convert the statement to a conventional
     INSERT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26710

ChangeSet@1.2477, 2007-05-15 18:50:31+04:00, kostja@vajra.(none) +6 -0
  A fix and a test case for 
  Bug#21483 "Server abort or deadlock on INSERT DELAYED with another
  implicit insert"
  Also fixes and adds test cases for bugs:
  20497 "Trigger with INSERT DELAYED causes Error 1165"
  21714 "Wrong NEW.value and server abort on INSERT DELAYED to a
  table with a trigger".
  Post-review fixes.
  
  Problem:
  In MySQL INSERT DELAYED is a way to pipe all inserts into a
  given table through a dedicated thread. This is necessary for
  simplistic storage engines like MyISAM, which do not have internal
  concurrency control or threading and thus can not
  achieve efficient INSERT throughput without support from SQL layer.
  DELAYED INSERT works as follows:
  For every distinct table, which can accept DELAYED inserts and has
  pending data to insert, a dedicated thread is created to write data
  to disk. All user connection threads that attempt to
  delayed-insert into this table interact with the dedicated thread in
  producer/consumer fashion: all records to-be inserted are pushed
  into a queue of the dedicated thread, which fetches the records and 
  writes them.
  In this design, client connection threads never open or lock
  the delayed insert table.
  This functionality was introduced in version 3.23 and does not take 
  into account existence of triggers, views, or pre-locking.
  E.g. if INSERT DELAYED is called from a stored function, which,
  in turn, is called from another stored function that uses the delayed
  table, a deadlock can occur, because delayed locking by-passes
  pre-locking. Besides:
   * the delayed thread works directly with the subject table through
     the storage engine API and does not invoke triggers
   * even if it was patched to invoke triggers, if triggers,
     in turn, used other tables, the delayed thread would
     have to open and lock involved tables (use pre-locking).
   * even if it was patched to use pre-locking, without deadlock
     detection the delayed thread could easily lock out user 
     connection threads in case when the same table is used both
     in a trigger and on the right side of the insert query: 
     the delayed thread would not release locks until all inserts 
     are complete, and user connection can not complete inserts 
     without having locks on the tables used on the right side of the
     query.
  
  Solution:
  
  These considerations suggest two general alternatives for the
  future of INSERT DELAYED:
   * it is considered a full-fledged alternative to normal INSERT
   * it is regarded as an optimisation that is only relevant 
     for simplistic engines.
  Since we missed our chance to provide complete support of new
  features when 5.0 was in development, the first alternative
  currently renders infeasible.
  However, even the second alternative, which is to detect
  new features and convert DELAYED insert into a normal insert, 
  is not easy to implement.
  The catch-22 is that we don't know if the subject table has triggers
  or is a view before we open it, and we only open it in the
  delayed thread. We don't know if the query involves pre-locking
  until we have opened all tables, and we always first create
  the delayed thread, and only then open the remaining tables.
  This patch detects the problematic scenarios and converts
  DELAYED INSERT to a normal INSERT using the following approach:
   * if the statement is executed under pre-locking (e.g. from
     within a stored function or trigger) or the right
     side may require pre-locking, we detect the situation
     before creating a delayed insert thread and convert the statement
     to a conventional INSERT.
    * if the subject table is a view or has triggers, we shutdown
     the delayed thread and convert the statement to a conventional
     INSERT.

Since it's the second version of the patch that was originally submitted by Tomash, I believe one reviewer is satisfactory.

Queued into 5.0, 5.1 -runtime

Pushed into 5.0.42

Pushed into 5.1.19-beta

Noted in 5.0.42, 5.1.19 changelogs.

The server could abort or deadlock for INSERT DELAYED statements for
which another insert was performed implicitly (for example, via a
stored function that inserted a row).