How to Avoid update deadlock

Question

While working on JDBC with Postgres...

Isolationlevel="Read committed"

I got the same deadlock in a multithreaded environment when I tried updating the table after some operations. So I tried using multiple queries as shown below

  ps = con.prepareStatement("UPDATE TableA SET column1=column1-? WHERE column2=? and column3=?;"  
                          + "UPDATE TableA SET column1=column1+? WHERE column2=? and column3=?;");

Here are the postgresql logs for the error

2016-12-19 12:25:44 IST STATEMENT:  UPDATE TableA SET column1=column1+$1 WHERE column2=$2 and column3=$3
2016-12-19 12:25:44 IST FATAL:  connection to client lost
2016-12-19 12:25:45 IST ERROR:  deadlock detected
2016-12-19 12:25:45 IST DETAIL:  Process 8524 waits for ShareLock on transaction 84942; blocked by process 12520.
    Process 12520 waits for ShareLock on transaction 84940; blocked by process 20892.
    Process 20892 waits for ExclusiveLock on tuple (1,5) of relation 25911 of database 24736; blocked by process 8524.
    Process 8524: UPDATE TableA SET column1=column1-$1 WHERE column2=$2 and column3=$3
    Process 12520: UPDATE TableA SET column1=column1-$1 WHERE column2=$2 and column3=$3
    Process 20892: UPDATE TableA SET column1=column1-$1 WHERE column2=$2 and column3=$3
2016-12-19 12:25:45 IST HINT:  See server log for query details.
2016-12-19 12:25:45 IST CONTEXT:  while locking tuple (1,12) in relation "TableA"
2016-12-19 12:25:45 IST STATEMENT:  UPDATE TableA SET column1=column1-$1 WHERE column2=$2 and column3=$3
2016-12-19 12:25:45 IST LOG:  could not send data to client: No connection could be made because the target machine actively refused it.

In this multithreaded environment, I was expecting TableA's rows to get locked for the 2 statements and avoid deadlock.

I see similar scenario explained in Postgres Docs

I could not find any method to avoid this kind of deadlock. Any help is appreciated. Thanks

P.S: Autocommit is set FALSE already and tried using preparedStatements with single UPDATE query.

Regarding multiple queries -> Multiple queries in one Preparedstatement and this shows that postgres doesnt need any additional configurations.

Answer 1

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.