[Django] #36915: Add support for select_for_update for implicit transactions

[Django]

#36915: Add support for select_for_update for implicit transactions
-------------------------------------+-------------------------------------
Reporter: David | Type:
| Cleanup/optimization
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Currently the [https://docs.djangoproject.com/en/6.0/ref/models/querysets
/#select-for-update select_for_update] method is only allowed inside
explicit transactions which have been started using `transaction.atomic`.

However any DML SQL statements can create an "implicit transaction" in
which it may be useful to get control on row-level locks.

What I would like to achieve is a way to perform the following statement
(inspired by [https://stackoverflow.com/a/44669989 this SO answer]):

{{{#!sql
UPDATE my_table
SET my_col = 1
WHERE id IN (
SELECT id
FROM my_table
WHERE ...
FOR UPDATE
);
}}}

As of now it is not possible to perform this kind of query:

{{{#!python
MyModel.objects.filter(pk__in=MyModel.objects.filter(...).select_for_update()).update(my_col=...)
# raises TransactionManagementError: select_for_update cannot be used
outside of a transaction.
}}}

The same can be accomplished by forcing the transaction

{{{#!python
with transaction.atomic():
MyModel.objects.filter(pk__in=MyModel.objects.filter(...).select_for_update()).update(my_col=...)
# works!
}}}

It would be useful to improve locks management in bulk-operations.

If it is too difficult to add this kind of support with the ORM it could,
at least, be documented as a tip in the docs.
--
Ticket URL: <https://code.djangoproject.com/ticket/36915>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36915: Add support for select_for_update for implicit transactions
-------------------------------------+-------------------------------------

Reporter: David | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by jaffar Khan):

I think select_for_update() is working as it has to be because using
select_for_update() in implicit transactions will not make sense as no
atomic boundary is defined, django will not know when to lock the rows and
when to release the lock. It is defined in docs
[https://docs.djangoproject.com/en/6.0/ref/models/querysets/#select-for-
update] as

**Returns a queryset that will lock rows until the end of the
transaction**

Even if you redefine the select_for_update() it will lock the rows and
immidiatley release it before any updates so technically it will behave as
implicit transactions.
--
Ticket URL: <https://code.djangoproject.com/ticket/36915#comment:1>