tortoise / tortoise/tortoise-orm

`.values_list()` / `.values()` silently drop `select_for_update()` - rows are not locked

Open
#2,250 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Describe the bug

Chaining .values_list() or .values() after .select_for_update() silently loses the FOR UPDATE clause: the generated SQL contains no locking clause and no error or warning is raised, so code that relies on the row locks (e.g. a periodic job selecting ids to update, guarded against a concurrent writer) is racy while looking correct.

The cause is visible in tortoise/queryset.py: QuerySet.values_list() and QuerySet.values() build ValuesListQuery / ValuesQuery by passing an explicit list of attributes, and the _select_for_update* family is not among them (the constructors do not even accept it), so the flag set by select_for_update() is dropped instead of being propagated the way _clone() propagates it. Present on 1.1.7 and on current develop.

To Reproduce

import asyncio

from tortoise import Tortoise, fields
from tortoise.models import Model


class Item(Model):
    id = fields.IntField(primary_key=True)
    name = fields.CharField(max_length=50)


async def main() -> None:
    await Tortoise.init(
        db_url="postgres://postgres:postgres@localhost:5432/postgres",
        modules={"models": ["__main__"]},
    )
    await Tortoise.generate_schemas()

    base = Item.filter(name="x").select_for_update()
    print("plain queryset: ", base.sql())
    print("values_list():  ", base.values_list("id", flat=True).sql())
    print("values():       ", base.values("id").sql())

    await Tortoise.close_connections()


asyncio.run(main())

Output (tortoise-orm 1.1.7, PostgreSQL 18, asyncpg):

plain queryset:  SELECT "name","id" FROM "item" WHERE "name"=$1 FOR UPDATE
values_list():   SELECT "id" "0" FROM "item" WHERE "name"=$1
values():        SELECT "id" "id" FROM "item" WHERE "name"=$1

Expected behavior

Either of:

  1. values_list() / values() keep the FOR UPDATE clause (SELECT "id" "0" FROM "item" WHERE "name"=$1 FOR UPDATE), matching how limit, offset, distinct, order_by and force_index survive the conversion; or
  2. the combination raises, like .values_list() after .only() already does (ValueError(".values_list() cannot be used with .only()")), so the lock can never be lost silently.

Silently dropping a locking clause seems like the worst of the options - we found this only because a pytest-under-PostgreSQL race test failed: a cron doing Model.filter(...).select_for_update().values_list("id", flat=True) inside a transaction did not block a concurrent writer and clobbered its committed update. Workaround we settled on: .select_for_update().only("id") (a plain queryset keeps the clause).

Additional context

  • tortoise-orm: 1.1.7 (also reproducible reading develop sources)
  • Python: 3.14
  • Database: PostgreSQL 18 (asyncpg)
  • Possibly related but distinct: #1297 (subquery loses FOR UPDATE), #702 (update_or_create does not lock) - this one is specifically about the values_list() / values() conversion discarding the flag.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in tortoise/queryset.py with QuerySet.values_list(), QuerySet.values(), and the ValuesListQuery/ValuesQuery constructors; compare their propagated attributes with _clone() and select_for_update(). Add regression coverage for values_list() and values() after select_for_update(), then run the PostgreSQL pytest coverage and verify the generated SQL retains FOR UPDATE.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
74/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.