MySQL Bugs: #40819: INSERT DELAYED documentation request

Bug #40819	INSERT DELAYED documentation request
Submitted:	18 Nov 2008 12:22	Modified:	9 Apr 2009 18:31
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.67 - likely any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
I find this
http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html
.. too much 'insiderish' and not clear from the point of user who is not interested in server internals!

I particular:

1) Different behaviour with LOCKs and INSERT/INSERT DELAYED seems not to be documented

2) This "INSERT DELAYED works only with MyISAM, MEMORY, and ARCHIVE tables" is not enough.  Because what does it then do with other engines? Raise error/warning?  It does not seem to be totally ignored as the different LOCK behaviour with INSERT/INSERT DELAYED is reproducable on InnoDB too (is this a bug?)

How to repeat:
drop table if exists lt1;

create table `lt1` (                                                                                    
`id` int(11) NOT NULL auto_increment,
`t` varchar(50) default1 NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

lock table lt1 read;
insert into lt1 (t) values ('abc'); 
-- Error Code : 1099
-- Table 'lt1' was locked with a READ lock and can't be updated

unlock tables;
lock table lt1 write;
insert into lt1 (t) values ('xyz'); 
-- success

unlock tables;
lock table lt1 read;
insert delayed into lt1 (t) values ('123');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt1' because it is locked with LOCK TABLES

unlock tables;
lock table lt1 read;
insert  delayed into lt1 (t) values ('789'); 
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt1' because it is locked with LOCK TABLES

select * from lt1;
/*
    id  t     
------  ------
     1  xyz   
*/

drop table if exists lt2;

create table `lt2` (                                                                                    
`id` int(11) NOT NULL auto_increment,
`t` varchar(50) default2 NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

lock table lt2 read;
insert into lt2 (t) values ('abc'); 
-- Error Code : 1099
-- Table 'lt2' was locked with a READ lock and can't be updated

unlock tables;
lock table lt2 write;
insert into lt2 (t) values ('xyz'); 
-- success

unlock tables;
lock table lt2 read;
insert delayed into lt2 (t) values ('123');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked with LOCK TABLES

unlock tables;
lock table lt2 read;
insert  delayed into lt2 (t) values ('789'); 
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked with LOCK TABLES

select * from lt2;
/*
    id  t     
------  ------
     1  xyz   
*/

drop table if exists lt2;

create table `lt2` (                                                                                    
`id` int(11) NOT NULL auto_increment,
`t` varchar(50) default2 NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

lock table lt2 read;
insert into lt2 (t) values ('abc'); 
-- Error Code : 1099
-- Table 'lt2' was locked with a READ lock and can't be updated

unlock tables;
lock table lt2 write;
insert into lt2 (t) values ('xyz'); 
-- success

unlock tables;
lock table lt2 read;
insert delayed into lt2 (t) values ('123');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked with LOCK TABLES

unlock tables;
lock table lt2 read;
insert  delayed into lt2 (t) values ('789'); 
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked with LOCK TABLES

select * from lt2;
/*
    id  t     
------  ------
     1  xyz   
*/

Suggested fix:
not sure!

my mistake when posting!

Please replace 'innodb' with 'myisam' in one of the create stmts!

Thank you for a reasonable documentation request.

grrr .. I made one more mistake when posting!

unlock tables;
lock table lt2 read;
insert delayed into lt2 (t) values ('123');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked
with LOCK TABLES

unlock tables;
lock table lt2 read;
insert  delayed into lt2 (t) values ('789');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked
with LOCK TABLES

should have been

unlock tables;
lock table lt2 read;
insert delayed into lt2 (t) values ('123');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked
with LOCK TABLES

unlock tables;
lock table lt2 write;
insert  delayed into lt2 (t) values ('789');
-- Error Code : 1165
-- INSERT DELAYED can't be used with table 'lt2' because it is locked
with LOCK TABLES

this is the culprit:
lock table lt2 write;
insert  delayed into lt2 (t) values ('789');
.. and different from plain INSERT.  It is understandable (for me too now!) wiht MyISAM as the INSERT is executed in another thread (the delayed_handler_thread for that table).  But it should be documented explicitly.

But I still think that with InnoDB it should simply insert (maybe with a warning). As DELAYED is ignored anyway it will execute in *this thread* and LOCK TABLE WRITE should not prevent! This is non-consistent and I think it is a bug.

Your request to add explanation of LOCK TABLE's infuence on INSERT DELAYED to that manual page is definitely reasonable. 

As for bug in case of InnoDB, I do not think it is a bug. You can not successfully execute INSERT DELAYED on InnoDB table with table lock or without:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert delayed into lt1 (t) values ('123');
ERROR 1031 (HY000): Table storage engine for 'lt1' doesn't have this option
mysql> lock table lt1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> insert delayed into lt1 (t) values ('123');
ERROR 1165 (HY000): INSERT DELAYED can't be used with table 'lt1' because it is
locked with LOCK TABLES

If you want to see the first error message instead of the second when table is locked, feel free to report a different feature request. I'd prefer for this to be documentation request only.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Update to INSERT DELAYED section (5.1 manual version):

INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and (as of
MySQL 5.1.19) BLACKHOLE tables. For engines that do not support
DELAYED, an error occurs.

An error occurs for INSERT DELAYED if used with a table that has been
locked with LOCK TABLES because the insert must be handled by a
separate thread, not by the session that holds the lock.