django select_for_update acquires relation lock

Question

I have this code sample that should take row (tuple) lock in postgres, however it seems to take table (relation) lock instead:

with transaction.Atomic(savepoint=True, durable=False):
    record = MyModel.objects.select_for_update().filter(pk='1234')
    record.delete()
    time.sleep(5)
    raise Exception

By looking at the pg_locks during the time of the transaction I can see:

select locktype, database, relation::regclass, pid, mode, granted from pg_locks where pid <> pg_backend_pid();

To my knowledge, I should have seen "tuple" in the locktype since I'm only locking specific row/s and not the entire table

Answer 1

First things first

You actually did not perform a SELECT FOR UPDATE query.

• record = MyModel.objects.select_for_update().filter(pk='1234') returns a QuerySet, no query is executed.
  • record.delete() only executes a DELETE command.
• A SELECT FOR UPDATE query would have acquired a relation RowShareLock.
  • You can verify this by executing the QuerySet with .first(), i.e. record = MyModel.objects.select_for_update().filter(pk='1234').first().
• You can verify this with log all sql queries.

Row-level (tuple) locks

A row-level FOR UPDATE lock is acquired but not shown in your pg_locks view (it doesn't show on mine too). Instead, we see transactionid ExclusiveLock (and virtualxid ExclusiveLock).

From https://www.postgresql.org/docs/9.3/view-pg-locks.html:

Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.

From https://www.postgresql.org/docs/9.4/explicit-locking.html:

FOR UPDATE

...

The FOR UPDATE lock mode is also acquired by any DELETE on a row ...

You can verify this empirically by running in your psql terminal:

• before record.delete()
  • SELECT FROM mymodel WHERE id='1' FOR UPDATE; works.
  • SELECT FROM mymodel WHERE id='1234' FOR UPDATE; works.
• after record.delete()
  • SELECT FROM mymodel WHERE id='1' FOR UPDATE; works.
  • SELECT FROM mymodel WHERE id='1234' FOR UPDATE; doesn't work.

Table-level (relation) locks

1. The relation AccessShareLock appears to be acquired for a SELECT query that you did not show in your code sample, e.g. MyModel.objects.filter(pk='1234').first().
2. The relation RowExclusiveLock is acquired for the DELETE command.

While these are table-level locks, they only conflict with EXCLUSIVE and/or ACCESS EXCLUSIVE locks, which are not acquired by most other DQL (data query language) and DML (data manipulation language) commands.

From https://www.postgresql.org/docs/9.4/explicit-locking.html:

ACCESS SHARE

Conflicts with the ACCESS EXCLUSIVE lock mode only.

The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

ROW EXCLUSIVE

Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.