MySQL Bugs: #40505: INSERT DELAYED and transactions, duplicate keys

Bug #40505	INSERT DELAYED and transactions, duplicate keys
Submitted:	4 Nov 2008 16:09	Modified:	21 Nov 2008 10:31
Reporter:	Vladimir Kolesnikov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
INSERT DELAYED opens per-handler transactions which causes some unexpected/non-deterministic behaviors. delayed.test contains the following example (reduced):

CREATE TABLE t1 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
SET INSERT_ID = 14;
INSERT DELAYED INTO t1 VALUES (NULL);
INSERT DELAYED INTO t1 VALUES (NULL);
...
SET INSERT_ID = 14;
INSERT DELAYED INTO t1 VALUES (NULL);
INSERT DELAYED INTO t1 VALUES (NULL);

Notice that depending on server state this whole script can be handled by one or more insert-delayed-handlers. Every handler opens a separate transaction for all statements that it processes, which effectively means that the script can be split into a random number of transactions. This causes some problems - e.g. if a statement fails, then the entire transaction is rolled back. In particular ha_extra(HA_EXTRA_IGNORE_DUP_KEY) is not called, so if you got a duplicate key like in the example above and you have a transactional engine (PBXT in my case) then zero or more valid inserts are rolled back - this is what I mean by non-deterministic.

It would be nice at least to call ha_extra(HA_EXTRA_IGNORE_DUP_KEY) so that an engine that is otherwise INSERT DELAYED-compatible (see e.g. comment in handler.h) will deal correctly with this very common error case.

Also this is not clear why insert-delayed-handler opens a "long" transaction, provided that INSERT DELAYED is not intended to be fast and that AFAIK it is supported only by non-transactional engines.

It would also be nice to add SQLCOM_INSERT_DELAYED into thd_sql_command set, so that handlers could do other INSERT DELAYED-specific handling if needed.

How to repeat:
see description

Suggested fix:
see description

the behavior is, basically, intentional. There's a lot of non-determinism in INSERT DELAYED (a simplest one - you can never know what you will see if you SELECT shortly after INSERT DELAYED).

As for HA_EXTRA_IGNORE_DUP_KEY - try INSERT DELAYED IGNORE.

As for "why insert-delayed-handler opens a long transaction" - for example, in certain engines it may be faster to insert rows in batches instead of committing every single write_row().

But I do agree that committing every now and then sounds useful. I could turn this into a feature request "INSERT DELAYED should commit periodically", okay ?
Don't yet know how often, a config variable @@delayed_commits ? or at the end of every INSERT DELAYED statement ?

Making a separate transaction for every INSERT would be in my understanding the only valid option for a transactional engine. Otherwise as I said - one can get random number of valid statements rolled back together with invalid ones. Provided that one of primary INSERT DELAYED usages is logging this can be also a severe security problem. OTOH for non-trx engines this would slow down the operation, so maybe it makes sense to add separate logics for trx engines, and keep existing code for non-trx.

Thank you for the feedback.

Verified as feature request.