Keep lock on database object after commit

Question

I'm selecting some object for update and then perform operation on it

obj = Model.objects.select_for_update().get(id=someID)
obj.somefield = 1
obj.save()

But I need still to keep FOR UPDATE lock on this object. PostgreSQL documentation says that FOR UPDATE lock will live until the end of transaction, which will be ended, because save will trigger commit. Even if I will manage commit manually, I need to save some info to the database (to do this I need to commit).

So, what I can do in this situation? If I will select object again, some other process may perform changes on this object before I will set a new lock.

(I'm using django 1.7 and postgresql 9.3)

Answer 1

You can't hold a row lock after the transaction commits or rolls back.

This might be a reasonable use case for advisory locks, though it's hard to say with the limited detail provided. Advisory locks can operate at the session level, crossing transaction boundaries.

You can't (ab)use a WITH HOLD cursor:

test=> DECLARE test_curs CURSOR WITH HOLD FOR SELECT * FROM dummy FOR UPDATE;
ERROR:  DECLARE CURSOR WITH HOLD ... FOR UPDATE is not supported
DETAIL:  Holdable cursors must be READ ONLY.

so I think an advisory lock is pretty much your only option.

---

The usual way of handling this, by the way, is to let the other task make changes in between. Make sure that each transaction leaves the object in a sensible state that makes sense. If someone else makes a change between your two changes, design things so that's OK.

To avoid overwriting the changes made in between you can use optimistic concurrency control, otherwise known as optimistic locking, using a row-version column. If you see someone snuck in and made a change you reload the object to get the new version, repeat your change, and try saving again. Unfortunately, unlike more sophisticated ORMs like Hibernate, it doesn't look like Django has built-in support for optimistic concurrency control, but there seem to be extensions that add it.