SELECT FOR UPDATE 中的数据库死锁 [英] Database Deadlock in SELECT FOR UPDATE

问题描述

我的应用程序间歇性地陷入僵局.我的申请有 1 个表，例如 EMPLOYEE(ID(PK)、NAME、SAL)，并且有 2 个会话.

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.

第 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);

会话 2:(用于其他业务逻辑)

Session 2: (for other business logic)

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

因此，在应用程序中，两个会话都尝试更新同一行(在 ID=2 的示例行中)这种情况是意料之中的，因此我认为 SELECT .. 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.

我做错了吗?我假设 SELECT FOR UPDATE 将锁定该行，当其他会话尝试更新同一行时，它将等待会话 1 完成执行.

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.

推荐答案

我假设 SELECT FOR UPDATE 将锁定该行，当其他会话尝试更新同一行时，它将等待会话 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:

进程 1 锁定 ID=2 的行，更新它并转到下一个 ID=1 的记录(但会话和事务仍处于活动状态)进程 2 已经锁定了 ID=1 的行，并将锁定 ID=2 的行(但会话和事务仍然处于活动状态)

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)

所以进程 1 正在等待记录 ID=1 并保持记录 ID=2

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

进程 2 正在等待记录 ID=2 并保持记录 ID=1

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.

这篇关于SELECT FOR UPDATE 中的数据库死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！