1

I have a simple table with a bunch of rows. There are no foreign keys. There is one unique key on several of the columns.

Here is an example of the insert statement:

INSERT INTO das_args (a,b,c,d) VALUES (1,2,3,4) ON DUPLICATE KEY UPDATE VALUES(1,2,3,4)

The unique key is (a,b,c).

The error is:

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

I've read How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction' which is an excellent discussion of the same error message being triggered with a different use case.

Autocommit is true. This is the only statement. There is no BEGIN or COMMIT.

I don't see a way that I can refactor this. There are simply two clients that appear to be updating the same unique key at the same time (which shouldn't happen, but does). My code just re-runs the statement and it succeeds, but I would like to remove the deadlock errors.

vy32
  • 28,461
  • 37
  • 122
  • 246

1 Answers1

0

First, I would expect the syntax to be

INSERT INTO das_args (a,b,c,d)
    VALUES (1,2,3,4)
    ON DUPLICATE KEY UPDATE  d = VALUES(d);

As for the deadlock -- It sounds like there is more than the one statement in the transaction. Let's see the BEGIN through the COMMIT.

What is the value of autocommit?

Rick James
  • 135,179
  • 13
  • 127
  • 222