Handling transaction deadlock

{problem}

When more than one process is running multi-row queries with an isolation level set, a "transaction deadlock" error can be thrown. For example, consider multiple processes running the query.

SET ISOLATION LEVEL READ COMMITTED

UPDATE a SET value = value+1

Because the isolation level is set to READ COMMITTED, the process must ensure it has the latest value before updating the row. NuoDB performs locking on a row-by row basis, so the process must acquire a lock on every row in a to complete this query. If both processes grab some of the rows in a, neither can complete. This deadlock is automatically detected and one of the queries is ended with a "transaction deadlock" error.

{problem}

{solution}

There are four general options for avoiding/handling deadlock.

1. If the queries are run without READ COMMITTED, rows no longer need to be locked so deadlock can't occur.
2. If the query only updates one row at a time (or updates multiple non-conflicting rows) deadlock can't occur.
3. If the application processes communicate so only one process runs the potentially deadlocking query at once, deadlock can't occur.
4. A try/catch block can catch the "transaction deadlock" error and retry the operation.

Note

In versions of NuoDB before 2.6 transaction deadlock was reported as the error "update conflict".

{solution}

Have more questions? Submit a request

Comments