Skip to content

Duplicate entry on INSERT for concurrent database sessions #7458

@davidsteinsland

Description

@davidsteinsland

I'm jumping right to the case:

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9f70oia4imp4uiuvfntf4c7770' for key 'PRIMARY'

Using CakePHP 2.6.8, the error is thrown from Model.php line 1928:

if (!$db->create($this, $fields, $values)) {

Right up until the point of the db->create() call, every find() query returns nothing on that ID. I.e., it should not exist, but it does.

This leads me to all but one fix:

try {
    if (!$db->create($this, $fields, $values)) {
        $success = false;
    } else {
        $created = true;
    }
} catch (PDOException $e) {
  if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
    /* perform UPDATE instead */
  }
  throw $e;
}

The 1062 is PDO's error code for DUPLICATE ENTRY.

Performing an UPDATE in the catch-block fixes the errors.

Before you say anything: This has nothing do to with model cache, db cache or any other caching. This is not a bug, but it should be fixed anyways.

The $exists variable in _doSave() is set very early on. This gives room for another thread that inserts the session before create() gets called.

If the try-catch block is not your cup of tea, then you must wrap the _doSave in a table lock.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions