We had quite a few deadlock issues with some old code at a job I had a few years ago. I feel that occasional deadlocks aren't catastrophic, though a pain, especially if you don't have retry logic in your app code.

Deadlocks aren't some random event you can't solve. They usually occur when you have two or more operations that lock a table and then try to update it but are trying to update it in different order. Then, each request is waiting on the other, and you have a deadlock. It's easy to create these in development, as you may only be making one request at a time. In production, you frequently have two requests at the same exact time.

Order matters. Deadlocks may happen if you update rows dynamically, so one user's request may update the same rows as another request, but since those updates are built dynamically and without a repeatable order, then you might have trouble.

Even hard-coding the order doesn't guarantee resolution, if the rows can be updated elsewhere in the code.

Deadlocks can be resolved by the database, and frequently are, but that involves finding one of the two (the victim) and terminating the attempt. Not ideal. And it requires you to implement retry functionality.

Example:

-- Connection from user 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Connection from user 2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

In this case, one request is waiting on the other and the deadlock will be resolved by the db. The main issue here is if you don't retry, you could be in a sticky situation with the victim update. And, if you have a situation where you get deadlocks, they could stack up and cause your application to cease to serve altogether.

And, if for some reason the database software can't find a victim you may end up with bad data or delays in completing the request.