[Django] #30375: Use "NO KEY" when doing select_for_update for PostgreSQL

[Django]

#30375: Use "NO KEY" when doing select_for_update for PostgreSQL
-------------------------------------+-------------------------------------
               Reporter:  mudetz     |          Owner:  nobody
                   Type:             |         Status:  new
  Cleanup/optimization               |
              Component:  Database   |        Version:  1.11
  layer (models, ORM)                |       Keywords:  postgres, lock,
               Severity:  Normal     |  database, operation
           Triage Stage:             |      Has patch:  1
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 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.

-- 
Ticket URL: <https://code.djangoproject.com/ticket/30375>
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/049.3b11e730499c792dfc8fba69301aab03%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.