SQLAlchemy with_for_update row locking not working

Question

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

In postgres

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;

In session 2 I concurrently run:

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

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.

---

In Flask-SQLAlchemy

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

In session 1:

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

In session 2:

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

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.

---

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()

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).

Would like to know why this happens.

Answer 1

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

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.

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.

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