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);
