Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database Deadlock in SELECT FOR UPDATE

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.

like image 591
Danny Zen Avatar asked Oct 11 '13 17:10

Danny Zen


People also ask

Can deadlock happen on SELECT?

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.

How can we avoid deadlock while updating SQL Server?

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.

How do you handle a deadlock situation in a database?

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.


1 Answers

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.

like image 63
Nicolai Avatar answered Oct 02 '22 11:10

Nicolai