Bug #51838 Innodb gets X lock for INSERT ON DUPLICATE KEY UPDATE but not INSERT SELECT
Submitted: 8 Mar 2010 17:29 Modified: 19 Mar 2010 18:24
Reporter: Mark Callaghan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.84 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, deadlock, duplicate, innodb, key, UPDATE

[8 Mar 2010 17:29] Mark Callaghan
Description:
Per http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html, when INSERT ... ON DUPLICATE KEY UPDATE is executed, InnoDB gets an X lock on the row during INSERT processing when there is a duplicate key.

Alas, the same is not done for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE. In that case only an S lock is obtained and this sequence leads to a deadlock:
1) trx 1 does the insert step for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE and gets S lock on row A
2) trx 2 does UPDATE and gets pending X lock on row A
3) trx 1 then does the update step and needs to upgrade from S to X lock

There is a pending feature request for this, but the changes in the feature request are more significant than what is needed here.
http://bugs.mysql.com/bug.php?id=21356

I think the fix for this is to change innobase_query_is_update to treat SQLCOM_INSERT and SQLCOM_INSERT_SELECT the same -- get an X lock.

This appears to have been fixed in 5.1

How to repeat:

Code from 5.1 (plugin and non-plugin innodb are similar) for the function row_ins_duplicate_error_in_clust and this should work as long as table->file->extra(HA_EXTRA_IGNORE_DUP_KEY) is called in both cases

                        if (trx->duplicates & TRX_DUP_IGNORE) {

                                /* 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_REC_NOT_GAP,
                                        btr_cur_get_block(cursor),
                                        rec, cursor->index, offsets, thr);
                        } else {

                                err = row_ins_set_shared_rec_lock(
                                        LOCK_REC_NOT_GAP,
                                        btr_cur_get_block(cursor), rec,
                                        cursor->index, offsets, thr);
                        }

Suggested fix:
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index e423f9c..8d9c3a2 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -7709,7 +7709,8 @@ innobase_query_is_update(void)
                return(1);
        }
 
-       if (thd->lex->sql_command == SQLCOM_INSERT &&
+       if ((thd->lex->sql_command == SQLCOM_INSERT ||
+            thd->lex->sql_command == SQLCOM_INSERT_SELECT) &&
            thd->lex->duplicates  == DUP_UPDATE) {
 
                return(1);
[19 Mar 2010 18:25] Omer Barnir
5.0 is EOL and issue is addressed in 5.1