Database Deadlock in SELECT FOR UPDATE

Question

I'm getting deadlock intermittently in my application. My application has 1 table e.g EMPLOYEE (ID (PK), NAME, SAL) and there are 2 sessions.

Session 1:

SELECT ID, NAME, SAL FROM EMPLOYEE WHERE SAL = (SELECT MIN(SAL) FROM 
EMPLOYEE) FOR UPDATE
Let say the query return EMPLOYEE ROW having ID=2
then application does some processing like rs.updateInt(ID_SAL, 10);

Session 2: (for other business logic)

SELECT ID, NAME, SAL FROM EMPLOYEE WHERE ID=2 FOR UPDATE.

So, in the application both sessions try to update the same row (in example row with ID=2) Such situation is expected and hence I thought SELECT .. FOR UPDATE will help.

Am I doing something wrong? I'm assuming that SELECT FOR UPDATE will lock the row and when other session will try to update the same row, it will wait until session 1 completes execution.

Impassible

Answer 1

I'm assuming that SELECT FOR UPDATE will lock the row and when other session will try to update the same row, it will wait until session 1 completes execution.

That is exactly. But you need to close transaction when you finish with this row or close session. The possible situation for your issue is the next:

Process 1 locks row with ID=2, updates it and going to the next record with ID=1 (but session and transaction is still active) Process 2 already locked row with ID=1 and going to lock row with ID=2 (but session and transaction is still active)

So Process 1 is waiting for record ID=1 and hold record ID=2

Process 2 is waiting for record ID=2 and hold record ID=1

This is a dead lock. You have to complete transaction after finished work with record to free it for other processes.

If you need several record to update in one transaction just lock them all together and free after work is finished.

Blowzed

Thanks Nicolai for quick response. Yes, you are correct in the explanation. In both the sessions, I'm doing commit/rollback the transaction once processing is done i.e. rs.updateRow(). In Session 1, my application process only 1 row (note: session 1 query can return multiple rows but I'm processing only 1 row). And session 2 query will always return 1 row. – Impassible

The most important thing is free (complete transaction or close session) record or bulk of records before switch to other record(s) in both processes. – Blowzed