526互联

PostgreSQL - ERROR: deadlock detected

发布时间 2023-12-02 23:05:06作者: ZhangZhihuiAAA

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 1:

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;

 

Solution 2:

Change the order of the INSERT statements.