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.

Updates don't lock the whole table, only the row in question. From the docs you linked: `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`. — Nick Barnes, Dec 19 '16 at 09:20
Sorry, my mistake @NickBarnes. I meant rows only. I need to update two rows in that table in a multithreaded environment. How can I prevent deadlock in such situation and achieve the functionality? — Vamsidhar, Dec 19 '16 at 09:26
@VaoTsun I am using jdbc.. mentioned my prepared statement with SQL in description. It works fine normally, In multithreaded it gives deadlock. Multiple queries are allowed in Postgres by default. — Vamsidhar, Dec 19 '16 at 09:31
@Elnino: Like the docs say, use a consistent order, e.g. make sure that the first row updated is always the one with the smallest ID. — Nick Barnes, Dec 19 '16 at 10:03
@NickBarnes Yes that may avoid deadlock for some scenarios. But they sure do encounter at some point(which will be a catastrophe for the product). This my friend is for a ledger. Can't take risks on Money. — Vamsidhar, Dec 19 '16 at 10:16
@Elnino if you use two updates in one `prepareStatement`, you should provide correct answer here: http://stackoverflow.com/questions/3366164/can-i-use-multiple-statements-in-a-jdbc-prepared-query - people need to know how it works in fact — Vao Tsun, Dec 19 '16 at 10:22
@VaoTsun Sorry, [Multiple queries in one Preparedstatement](http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement) and https://sourceforge.net/p/art/wiki/Multiple%20Statements/ this shows that postgres doesnt need any additional configurations. — Vamsidhar, Dec 19 '16 at 10:31
@Elnino: A consistent lock ordering will prevent deadlock in **all** scenarios, as long as all processes adhere to it. — Nick Barnes, Dec 19 '16 at 10:32

score 7 · Accepted Answer · answered Dec 19 '16 at 12:15

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.

score 2 · Answer 2 · answered Dec 19 '16 at 10:03

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.

Thanks for the answer. But in real life we can't autoCommit. I need to rollback in case the second update or the later fails. — Vamsidhar, Dec 19 '16 at 10:11
I understand it and I said nothing about "autoCommit". But if you have heavily multi threaded environment and you leave transactions opened for a relatively long time then deadlocks are very probable to happen. This is how transactions work. — JosMac, Dec 19 '16 at 10:18

How to Avoid update deadlock

2 Answers2