What rules should I follow to prevent database deadlocks?

Short answer: what you’re hitting is a classic lock-ordering cycle: A locks X then Y, B locks Y then X. Break the cycle and you solve the deadlock at the root.

Short answer

The real issue is this: a deadlock isn’t bad luck, it’s the mathematical consequence of inconsistent lock ordering. When two transactions request locks in different orders, a cycle forms and each waits on the other. Since you’ll absorb the irreducible remainder with retries, the operation has to be idempotent — I covered the payment-side version of that same discipline in the duplicate-notification and idempotency answer.

Why

1. A deadlock is the consequence of inconsistent ordering. If both transactions request locks in the same order, a cycle mathematically cannot form.

2. A long transaction widens the collision window. The longer a lock is held, the more likely someone else runs into it.

3. Raising the isolation level isn’t the fix. It’s the common mistake; it usually doesn’t help and instead adds more locks.

What to do

1. Always acquire locks in the same global order. The most important rule. Lock rows in the same deterministic order everywhere — for example, always by ascending primary key.

2. Keep the transaction short and narrow. Move slow/external work (API calls, file writes, waiting on the user) outside the transaction; acquire the lock as late as possible and hold it briefly.

3. Touch the fewest rows, lock targeted. Apply SELECT ... FOR UPDATE only to the row you need.

4. Use a single statement where possible. With a single UPDATE / INSERT ... ON CONFLICT the DB manages the lock ordering for you.

5. Accept that deadlocks will happen anyway, and retry. The DB picks one transaction as the “victim” and aborts it; treat this as an expected condition, make the operation idempotent and retry with backoff.

Bottom line: I’d set up the trio of consistent lock order + short transactions + retry-on-deadlock. A common mistake: trying to solve deadlocks by raising the isolation level. That usually doesn’t help and instead adds more locks. Break the cycle, don’t force the isolation level.