PostgreSQL - ERROR: deadlock detected

ERROR: deadlock detected
DETAIL: Process 209 waits for ShareLock on transaction 1034; blocked by process 201.
Process 201 waits for ShareLock on transaction 1035; blocked by process 209.
HINT: See server log for query details.
CONTEXT: while locking tuple (0,1) in relation "accounts"

 

 

The cause is the foreign key reference.

Solution:

Change

-- name: GetAccountForUpdate :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1
FOR UPDATE;

to

-- name: GetAccountForUpdate :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1
FOR NO KEY UPDATE;

 

Another deadlock caused by concurrent inserts:

Solution :

Order the inserts:

        // This statement will cause deadlock error.
        // result.FromAccount, result.ToAccount, err = addMoney(ctx, q, arg.FromAccountID, -arg.Amount, arg.ToAccountID, arg.Amount)
        // Changing it as below can avoid the deadlock:
        if arg.FromAccountID < arg.ToAccountID {
            result.FromAccount, result.ToAccount, err = addMoney(ctx, q, arg.FromAccountID, -arg.Amount, arg.ToAccountID, arg.Amount)
        } else {
            result.ToAccount, result.FromAccount, err = addMoney(ctx, q, arg.ToAccountID, arg.Amount, arg.FromAccountID, -arg.Amount)
        }