MySQL Bugs: #36964: INSERT ... ON DUPLICATE KEY UPDATE does nothing but does not return error

Bug #36964	INSERT ... ON DUPLICATE KEY UPDATE does nothing but does not return error
Submitted:	26 May 2008 13:29	Modified:	27 Feb 2010 8:50
Reporter:	Valeriy Zmiyevskoy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.22rc	OS:	Any (insert into into transaction don't insert record but don't return error)
Assigned to:		CPU Architecture:	Any

Description:
I have 2 tables with relationships one-to-many. Record b can has many a records.

Two tables. With relationship one to many. I start transaction, insert record and in next query I don't see inserted record. This occurs before COMMIT or ROLLBACK request in single transaction.

How to repeat:
Two tables with relationship one-to-many.
<code>
CREATE TABLE `territory_state_cache` (
  `territory_state_` bigint(20) NOT NULL AUTO_INCREMENT,
  `territory_` bigint(20) NOT NULL,
  `battle_` bigint(20) DEFAULT NULL,
  `battle_state` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`territory_state_`),
  KEY `battle_` (`battle_`),
  KEY `territory_` (`territory_`),
  CONSTRAINT `territory_state_cache_fk` FOREIGN KEY (`territory_`) REFERENCES `territory` (`territory_`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `territory_user_state_cache` (
  `territory_state_` bigint(20) NOT NULL,
  `user_` bigint(20) NOT NULL,
  `user_state` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`territory_state_`,`user_`),
  KEY `user_` (`user_`,`user_state`),
  CONSTRAINT `territory_user_state_cache_fk` FOREIGN KEY (`territory_state_`) REFERENCES `territory_state_cache` (`territory_state_`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

My actions:

start transaction;
/* get record. Nested request used to lock only one record. */
SELECT territory_state_ from
territory_state_cache where territory_state_ =
(SELECT
  MIN(territory_state_) as territory_state_
  FROM
  territory_state_cache
    WHERE territory_ = 1)
LOCK IN SHARE MODE;
/* Next action if I have no record, I throw error in script language. */

/* {$territoryUserRecordId} get from previous request, {$userRecordId} predefined */
INSERT INTO `territory_user_state_cache` 
            (territory_state_, user_, user_state)
            values (
            {$territoryUserRecordId},
            {$userRecordId},
            0)
            ON DUPLICATE KEY UPDATE user_ = values(user_)
/* Make next and get no record. */
SELECT * 
FROM territory_user_state_cache 
WHERE user_ = {$userRecordId}
  AND territory_state_ = {$territoryUserRecordId};
COMMIT;
</code>

This reproduced once in 10000 requests in huge load and I can't suggest steps to reproduce more precise.

I have found some additional conditions.

For first, last_insert_id() and affected_rows() returns 0.

I think that is because firstly you check insert, than before update occurs in other transaction executes delete of found duplicate record, update is waiting while delete proceed and second transaction committed, and when update occurs there are no records to update.

Hi!

Do you use the default REPEATABLE READ isolation level?

Are you sure that the INSERT ... ON DUPLICATE KEY UPDATE happens in the same transaction as:
SELECT * 
FROM territory_user_state_cache 
WHERE user_ = {$userRecordId}
  AND territory_state_ = {$territoryUserRecordId};
COMMIT;

?

That is, you have SET AUTOCOMMIT=0?

ON DUPLICATE KEY UPDATE should lock the record if it sees a duplicate. It is a bug if the duplicate record disappears on the fly.

The bug might be that MySQL or InnoDB for some reason COMMITs the transaction in the middle of the ON DUPLICATE KEY processing.

If you rewrite the code to NOT use ON DUPLICATE KEY, does the problem disappear?

Regards,

Heikki

Yes I'm using REPEATABLE READ isolation level and AUTOCOMMIT = 0.
Select query in the same transaction.

If I use insert without ON DUPLICATE KEY UPDATE all will be fine, but It doesn't satisfy me, because I need this record present in next query so I need to lock it if present. And if I catch 1062 error record won't be locked.

I solve this problem by fake UPDATE, that doesn't affect any changes but lock record if present and not lock if not, and in next query insert record and catch duplicate record error if needed.

It looks like delete transaction injects after INSERT part of query while it checks for existence and before UPDATE part, locks the key and delete record, so update don't lock any record.

I have checked, INSERT ON DUPLICATE KEY UPDATE when record present and don't updated (affected_rows = 0) lock record properly.

http://bugs.mysql.com/bug.php?id=37183 may be associated with this.

http://bugs.mysql.com/bug.php?id=37183
is probably a MySQL bug: MySQL does not handle a deadlock error in ON DUPLICATE. This bug is probably another instance of the same bug.

Thank you for the report.

Bug #37183 which could cause this behavior has fixed now. Could you please try version 5.1.42 where it is fixed and inform us if problem still exists?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".