Can multiple SELECT FOR UPDATES in a single transaction cause a race state (Postgres)?

Question

I am using Postgres 9.1. I am wondering if using multiple SELECT FOR UPDATES in a single transaction could potentially cause a race condition.

2 simultaneous transactions:

transaction 1: choice for updating in table 1 - successfully receives a lock

transaction 2: choice for updating in table 2 - successfully receives a lock

transaction 2: selection for updating in table 1 - waiting for the lock to be released from transaction 1

transaction 1: selection for updating in table 2 - waiting for the lock to be released from transaction 2

What happens in this situation? Does one of the pending transactions time out? If so, is there a way to adjust the timeout duration?

edit: is deadlock_timeout the configuration I'm looking for?

Answer 1

Yes, you should look for deadlock_timeout in the docs .

But your script does not mean there will be a dead end, "cos PostgreSQL uses row-level locks, and it is unclear if your transactions match for the same rows.

Another option is to use a serialization level higher than default READ COMMITTED . But in this case, your application should be ready to receive exceptions with SQLCODE=40001 :

 ERROR: could not serialize access due to concurrent update 

It is expected that you should simply repeat the transaction as is.

A very good overview of the isolation level of Serializable, which you can find on the wiki .

Answer 2

PostgreSQL will detect the deadlock in step 4 and complete the transaction. Here is what happened when I tried it in psql (only showing step 4):

 template1=# SELECT * FROM table2 FOR UPDATE; ERROR: deadlock detected DETAIL: Process 17536 waits for ShareLock on transaction 166946; blocked by process 18880. Process 18880 waits for ShareLock on transaction 166944; blocked by process 17536. HINT: See server log for query details. template1=# 

This happens after 1 second, which is the default timeout. Another answer contains additional information about this.