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.
Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE. When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw.
Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.
One method of avoiding deadlock is using application-consistent logic. In the above-given example, Transactions that access Students and Grades should always access the tables in the same order.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With