How to Avoid update deadlock

As @Nick Barnes quoted in comment from the link I shared.

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Especially for update deadlocks as mentioned, the order of update leads to deadlock.

Example:

UPDATE Table SET ... WHERE id= 1;UPDATE Table SET ... WHERE id= 2;

and

UPDATE Table SET ... WHERE id= 2;UPDATE Table SET ... WHERE id= 1;

The general solution is to order the updates based on id. This is what the consistent order meant.

I didn't understand that till I struggle with this deadlock.

By my experience deadlock in PostgreSQL most likely happens in "real life" when you "cross" updates in 2 long running transactions. (explanation follows). And it is quite hard to even simulate deadlock on PG. Here is an example - http://postgresql.freeideas.cz/simulate-deadlock-postgresql/So classical deadlock means:

• You start transaction 1 + do update on row 1. Your procedure continues but transaction 1 is still opened and not commited.
• Then you start transaction 2 + do update on row 2. Procedure continues but transaction 2 is still opened and not commited.
• Now you try to update row 2 from transaction 1 which causes this operation to wait.
• Now you try to update row 1 from transaction 2 - in this moment PG reports deadlock and ends this transaction.

So I recommend you to commit transactions as soon as possible.