How to avoid update blocking

Question

While working on JDBC with Postgres ...

Isolationlevel = "Reading Read"

I have the same deadlock in a multi-threaded environment when I tried to update the table after some operations. So I tried using a few 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 multi-threaded environment, I expected TableA tables to be locked for 2 statements and avoid deadlocks.

I see a similar scenario is explained in Postgres Docs

I could not find any way to avoid such a dead end. Any help is appreciated. Thanks

PS: Autocommit is already set to FALSE and tried using prepared Statements with one UPDATE query.

Regarding multiple queries -> Several queries in one preliminary sentence, and this shows that postgres does not need any additional configurations.

Answer 1

As @Nick Barnes is quoted in a comment on a link I shared.

The best protection against deadlocks, as a rule, avoids them, that all applications using the database have several objects in a consistent manner .

Especially for deadlock updates, as mentioned, the update order leads to a deadlock.

An 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; 

A common solution is to order updates based on identifier. This implied a sequential order.

I did not understand this until I was struggling with this dead end.

Answer 2

In my experience, the PostgreSQL deadlock most likely occurs in “real life” when you “cross” updates in two long transactions. (explanation follows). And it's pretty hard to even simulate a dead end on PG. Here is an example - http://postgresql.freeideas.cz/simulate-deadlock-postgresql/ Thus, the classic dead end means:

• You start transaction 1 + update line 1. Your procedure continues, but transaction 1 is still open and not completed.
• Then you start transaction 2 + update line 2. The procedure continues, but transaction 2 is still open and not completed.
• Now you are trying to update row 2 from transaction 1, which makes this operation wait.
• Now you are trying to update row 1 from transaction 2 - at this point the PG reports a deadlock and ends this transaction.

Therefore, I recommend making transactions as soon as possible.