SQLAlchemy with_for_update行锁定不起作用? [英] SQLAlchemy with_for_update row locking not working?

问题描述

有一个学生的type属性为4，而type属性的最小值可以为1.

There is a student whose type attribute is 4 and the minimum value for type attribute can be 1.

在Postgres中

在会话1中，我专门锁定并更新了学生表中的一行:

In session 1, I exclusively lock and update a row in the student table:

BEGIN;
LOCK TABLE students IN ROW EXCLUSIVE MODE;
SELECT * FROM students WHERE id = 122  FOR UPDATE;
UPDATE students SET type = 1 WHERE id = 122;
END;

在会话2中，我同时运行:

In session 2 I concurrently run:

UPDATE students SET type = type - 1 WHERE id = 122;

我得到的结果是一个例外，即在会议2中学生的type不能低于 1 ，因为我已经将同一学生的type设置为 1 ，并且由于该会话对于该学生处于独占锁定状态，因此会话2必须等待.

The result I get is an exception, i.e student's type can't be lower than 1 in session 2 since I already set the same student's type to value of 1 and since session was in exclusive lock for that student, session 2 had to wait.

在Flask-SQLAlchemy中

我尝试使用用户将默认设置为4的type属性重新创建相同的结果.

I tried to recreate the same result with user, type attribute set to 4 by default.

在会话1中:

user = Student.query.with_for_update(of=Student, nowait=True).filter(Student.id == 122).first()
user.type = 1
db.session.commit()

在会话2中:

user = Student.query.filter(Student.id == 122).first()
user.type -= 1
db.session.commit()

我得到的结果是用户的type等于3，而我应该得到一个异常. 会话2中的事务更改将覆盖会话1中的事务更改，即使在会话2中的事务db.session.commit()之后，它会等到会话1中的事务结束.

The result I get is that user's type equals 3, whereas I should get an exception. Transaction changes in session 1 are overridden by those in session 2, even though after db.session.commit() in transaction in session 2 it waits till transaction in session 1 is over.

但是在会话2中，当我与会话1同时运行时:

But in session 2 when I run this with session 1 concurrently:

user = Student.query.filter(Student.id == 122).update({"type": Student.type - 1})
db.session.commit()

我得到正确的输出，即完整性错误显示尝试将学生122s type属性设置为0(不覆盖会话1的结果).

I get the right output, i.e integrity error showing an attempt to set student 122s type attribute to 0 (not overriding session 1 result).

想知道为什么会这样.

推荐答案

为使FOR UPDATE正常工作，所有涉及的事务(打算更新该行)都需要使用它.

In order for FOR UPDATE to work properly, all involved transactions which intend to update the row need to use it.

在您的(编辑后)示例中，会话2没有使用with_for_update.由于您没有告诉它使用FOR UPDATE，因此可以自由读取该行的旧值(因为尚未提交新值，并且锁不阻止纯读取器)，然后对其进行修改，以便-内存值，然后将其写回.

In your (post-edit) example, session 2 is not using with_for_update. Since you didn't tell it to use FOR UPDATE, it is free to read the old value of the row (since the new value has not yet been committed, and locks do not block pure readers), then modify it that in-memory value, then write it back.

如果您不想在要更改行的任何地方都使用FOR UPDATE，则可以在任何地方都使用isolation level serializable.但是，如果这样做，事情可能不会被阻塞，而是会在提交之前似乎成功，然后引发需要捕获和处理的序列化错误.

If you don't want to use FOR UPDATE everywhere that you read row with the intention of changing it, you could instead use isolation level serializable everywhere. However if you do, things might not block, but rather will appear to succeed until the commit, then throw serialization errors that will need to be caught and dealt with.

您的预编辑示例应该已经成功运行，因为两个会话均标有with_for_update.

Your pre-edit example should have worked as both sessions were labelled with with_for_update.

这篇关于SQLAlchemy with_for_update行锁定不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！