CODEWITHSUNDEEP
multithreadingpostgresqljdbcsql-updatedeadlock
Vamsidhar
Vamsidhar

Reputation: 832

How to Avoid update deadlock

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.

Upvotes: 9

Views: 3198

Answers (2)

Vamsidhar
Vamsidhar

Reputation: 832

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.

Upvotes: 7

JosMac
JosMac

Reputation: 2302

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.

Upvotes: 2

Related Questions