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 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.