id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30375	"Use ""NO KEY"" when doing select_for_update for PostgreSQL"	Manuel Weitzman	nobody	"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."	Cleanup/optimization	new	Database layer (models, ORM)	2.2	Normal		postgres, lock, database, operation		Unreviewed	1	0	0	0	0	0