INNODB LOCKING REGRESSION FOR INSERT IGNORE

Our application attempts to INSERT IGNORE the same row of data from many different connections to the same InnoDB table. In our test runs, we noticed that the 5.0 code created S locks while the 5.1 code created X locks for the same set of actions.

In the code paths for 5.0 and below, INSERT IGNORE processing would take
S-locks on any duplicate rows. The X-lock was reserved for only new rows
added to the table.

In 5.1 the locking logic was rewritten and all rows touched by INSERT IGNORE
are X-locked instead. This can turn a parallel data merge process into
essentially a single-threaded process because the connections that are not
actually adding a row to the table must wait for their X-lock which requires
the termination of the other locking thread(s).

the fault can be traced to this logic (5.1+):

if (allow_duplicates) {

/* If the SQL-query will update or replace
duplicate key we will take X-lock for
duplicates ( REPLACE, LOAD DATAFILE REPLACE,
INSERT ON DUPLICATE KEY UPDATE). */

err = row_ins_set_exclusive_rec_lock(
LOCK_ORDINARY, rec, index, offsets, thr);

This means that all INSERT IGNORE will take an X-lock because earlier this
flag was set:

case HA_EXTRA_IGNORE_DUP_KEY:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_IGNORE;
break;

This defect has been reported to MySQL.