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
28010	Add support for SELECT...FOR UPDATE OF	Ran Benita	nobody	"Using `select_for_update()` on a queryset appends ` FOR UPDATE` to the query. If the query joins other tables, the effect is that all tables are locked. I believe this is not what most people using `select_for_update()` want, instead they only want to lock the row from the main table.

== Example

Consider a scenario like this:

{{{#!python
class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.PositiveIntegerField()


class Order(models.Model):
    product = models.ForeignKey(Product, on_delete=models.PROTECT)
    status = models.CharField(max_length=100, choices=(
        ('NEW', 'New'),
        ('APPROVED', 'Approved'),
        ('CANCELED', 'Canceled'),
    )

    ...

    @classmethod
    def approve(cls, id):
        with transaction.atomic():
            order = Order.objects.select_related('product').select_for_update().get(pk=id)
            # ...Check order.product.price & stuff...
            order.status = 'APPROVED'
            order.save()

    @classmethod
    def cancel(cls, id):
        with transaction.atomic():
            # Similar...
}}}

Here, locking is needed in order to serialize `approve()` and `cancel()` on the same Order, to avoid race conditions.

I have also used `select_related` in `approve()`, in order to avoid an extra query. Potentially, there are many related objects, but to keep the example simple I added just one.

The interaction between `select_related` and `select_for_update` has the unintended consequence (IMO) of also locking the related `Product` row. Hence, if there is some concurrent task which updates product prices for example, it can cause conflicts, slowdowns, deadlocks, and other bad things.

The fix is to remove the `select_related`. But then extra queries are needed for each related object access without a reasonable workaround (I think?).

== Possible solutions

PostgreSQL (which I am using) has an option to specify exactly which tables are to be locked, instead of locking all tables in the query: https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE. The syntax is `SELECT ... FOR UPDATE OF orders_order`.

Oracle also supports this with similar syntax, however it allows specifying which *columns* to lock: http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj31783.html. I guess there it is possible to lock specific columns of specific rows, while postgres only locks full rows.

I am not familiar with MySQL but it doesn't seem like it supports refining the `FOR UPDATE`.

Here are some possible solutions I can think of:

1. Add support for `select_for_update` to specify which models to lock, e.g. `select_for_update('self', 'product')`. This will use the `OF` syntax.

2. Only support the `select_for_update('self')` behavior, and make it implicit, i.e. `select_for_update` only ever locks the main model of the QuerySet.

3. Add a way to do `select_related` on an already-existing object, something like a standalone `select_related_objects` after the existing `prefetch_related_objects` function: https://docs.djangoproject.com/en/1.10/ref/models/querysets/#prefetch-related-objects, and keep `select_for_update` as-is. Then it should at least be possible to do one extra query instead of one per related object.

Thanks for reading this far :)"	New feature	closed	Database layer (models, ORM)	dev	Normal	fixed		adam@… Adam Chidlow	Accepted	1	0	0	0	0	0