What causes a deadlock, and how would you prevent one?

A deadlock needs four conditions at once: mutual exclusion, hold and wait, no preemption, and a circular wait. Break any one and it cannot happen. In practice you break the circular wait by always acquiring locks in the same global order, and you add lock timeouts so a stuck transaction fails fast rather than hanging. Keeping transactions short and touching rows in a consistent order gets you most of the way there.

Deadlock needs four things together, and the one I can actually control is the circular wait. So the rule I put in place is that every code path takes locks in the same order. If a transfer touches two accounts, I sort by account id and lock the lower one first, and then it does not matter which direction the money is going, no cycle can form. The second lever is time. I keep transactions short, so I never hold a row lock across a network call to a payment provider, which is a classic way to turn a hundred millisecond transaction into a five second one. Then I set a lock timeout and retry with jitter, because sooner or later the database will detect a cycle, pick a victim and abort it, and I would rather that path be handled than surfaced as a 500. In Postgres the deadlock log entries name both statements involved, and both times I have used them the fix was ordering.