[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>

[Django]

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

Reporter: David | Owner: MANAS
Type: | MADESHIYA
Cleanup/optimization | Status: assigned

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
-------------------------------------+-------------------------------------

Changes (by MANAS MADESHIYA):

* owner: (none) => MANAS MADESHIYA
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36915#comment:2>

[Django]

#36915: Add support for select_for_update for implicit transactions
-------------------------------------+-------------------------------------
Reporter: David | Owner: MANAS
Type: | MADESHIYA
Cleanup/optimization | Status: assigned
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 MANAS MADESHIYA):

PR: https://github.com/django/django/pull/20773

Problem:
select_for_update() raises TransactionManagementError when used
as a subquery inside .update() even though the DML statement
creates an implicit transaction at the database level.

Example:
MyModel.objects.filter(
pk__in=MyModel.objects.filter(...).select_for_update()
).update(my_col=...)
# Previously raised TransactionManagementError
# Now works correctly

Fix:
Modified the check in compiler.py to skip the error when
select_for_update is used as a subquery inside a DML statement
by adding "and not self.query.subquery" condition.

Changes:
- django/db/models/sql/compiler.py
- tests/select_for_update/tests.py
--
Ticket URL: <https://code.djangoproject.com/ticket/36915#comment:3>

[Django]

#36915: Add support for select_for_update for implicit transactions
-------------------------------------+-------------------------------------
Reporter: David | Owner: MANAS
Type: | MADESHIYA
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by MANAS MADESHIYA):

* has_patch: 0 => 1

Comment:

https://github.com/django/django/pull/20773
--
Ticket URL: <https://code.djangoproject.com/ticket/36915#comment:4>

[Django]

#36915: Add support for select_for_update for implicit transactions
-------------------------------------+-------------------------------------
Reporter: David | Owner: MANAS
Type: | MADESHIYA

Cleanup/optimization | Status: closed

Component: Database layer | Version: 6.0
(models, ORM) |

Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* has_patch: 1 => 0
* resolution: => invalid
* status: assigned => closed

Comment:

I think the docs adequately cover this.
--
Ticket URL: <https://code.djangoproject.com/ticket/36915#comment:5>