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
CodePudding user response:
First things first
You actually did not perform a SELECT FOR UPDATE
query.
record = MyModel.objects.select_for_update().filter(pk='1234')
returns aQuerySet
, no query is executed.record.delete()
only executes aDELETE
command.
- A
SELECT FOR UPDATE
query would have acquired arelation
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 by executing the
- 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 anyDELETE
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
- The
relation
AccessShareLock
appears to be acquired for aSELECT
query that you did not show in your code sample, e.g.MyModel.objects.filter(pk='1234').first()
. - The
relation
RowExclusiveLock
is acquired for theDELETE
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
andACCESS EXCLUSIVE
lock modes.The commands
UPDATE
,DELETE
, andINSERT
acquire this lock mode on the target table (in addition toACCESS SHARE
locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.