Deadlock found when trying to get lock

[daveroberts]

We have millions of queries on this DB per day, but about once per day, we get a deadlock error with a query where we're not using locks. The query:

UPDATE some_table SET last_run_timestamp = NOW() WHERE id=? AND (last_run_timestamp IS NULL OR last_run_timestamp < NOW() - INTERVAL 3 MINUTE AND last_run_timestamp = ?)

The error: Deadlock found when trying to get lock; try restarting transaction

Our code uses no manually created transactions, but we're getting a transaction error, which leads me to believe the library is creating one on our behalf, and doing so incorrectly.

[sidorares]

Are you using this driver directly or via orm / query builder? This driver does not start transactions implicitly, maybe some other layer adds that?

[daveroberts]

Directly

[vlasky]

We have millions of queries on this DB per day, but about once per day, we get a deadlock error with a query where we're not using locks. The query:

The error: Deadlock found when trying to get lock; try restarting transaction

Our code uses no manually created transactions, but we're getting a transaction error, which leads me to believe the library is creating one on our behalf, and doing so incorrectly.

By default, autocommit mode is enabled in MySQL and each SQL statement forms a single transaction on its own.

It's normal to experience occasional deadlocks when you have multiple connections performing lots of updates targeting a single table. Your code should be written to detect these deadlock errors when they occur and re-run the statement.