#30375: Use "NO KEY" when doing select_for_update for PostgreSQL
-------------------------------------+-------------------------------------
Reporter: mudetz | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, lock, | Triage Stage:
database, operation | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by mudetz):
* version: 1.11 => 2.2
Old description:
> Currently Django compiles select for update into one of
> - FOR UPDATE
> - FOR UPDATE NOWAIT
> - FOR UPDATE SKIP LOCKED
>
> All of these acquire a lock which conflicts with `KEY SHARE` locks. This
> means that updates on tables which references a row locked by `FOR
> UPDATE` will have to wait until the lock is released. This is to avoid
> any unexpected changes on `PRIMARY KEY` fields but, **in Django, primary
> key fields are read-only, so locking them makes no sense**.
>
> There is no need to acquire these kind of locks, instead, we should (at
> least be able to) use
> - FOR NO KEY UPDATE
> - FOR NO KEY UPDATE NOWAIT
> - FOR NO KEY UPDATE SKIP LOCKED
>
> which can be easily done by overriding BaseOperation.for_update_sql in
> Postgres DatabaseOperation
>
> {{{
> def for_update_sql(self, nowait=False, skip_locked=False):
> """
> Returns the FOR UPDATE SQL clause to lock rows for an update
> operation.
> """
> if nowait:
> return 'FOR NO KEY UPDATE NOWAIT'
> elif skip_locked:
> return 'FOR NO KEY UPDATE SKIP LOCKED'
> else:
> return 'FOR NO KEY UPDATE'
> }}}
>
> Not doing so causes lock contention in our use case.
New description:
Currently Django compiles select for update into one of
- FOR UPDATE
- FOR UPDATE NOWAIT
- FOR UPDATE SKIP LOCKED
All of these acquire a lock which conflicts with `KEY SHARE` locks. This
means that updates on tables which references a row locked by `FOR UPDATE`
will have to wait until the lock is released. This is to avoid any
unexpected changes on `PRIMARY KEY` fields but, **in Django, primary key
fields are read-only, so locking them makes no sense**.
There is no need to acquire these kind of locks, instead, we should (at
least be able to) use
- FOR NO KEY UPDATE
- FOR NO KEY UPDATE NOWAIT
- FOR NO KEY UPDATE SKIP LOCKED
which can be easily done by overriding BaseOperation.for_update_sql in
Postgres DatabaseOperation
For 1.11
{{{
def for_update_sql(self, nowait=False, skip_locked=False):
"""
Returns the FOR UPDATE SQL clause to lock rows for an update
operation.
"""
if nowait:
return 'FOR NO KEY UPDATE NOWAIT'
elif skip_locked:
return 'FOR NO KEY UPDATE SKIP LOCKED'
else:
return 'FOR NO KEY UPDATE'
}}}
For 2.2
{{{
def for_update_sql(self, nowait=False, skip_locked=False, of=()):
"""
Return the FOR UPDATE SQL clause to lock rows for an update operation.
"""
return 'FOR NO KEY UPDATE%s%s%s' % (
' OF %s' % ', '.join(of) if of else '',
' NOWAIT' if nowait else '',
' SKIP LOCKED' if skip_locked else '',
)
}}}
Not doing so causes lock contention in our use case.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30375#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.6fb0ede73bda3aff2142e8a991d96584%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
