Skip to content

Close the take-over race of the row lock on SQLite, and say what a busy database is #3186

Open

Description

@pournasserian

Problem

#3182 (#3176) made a lock holder on SQLite keep its lock through a long write transaction. Its review measured what is left, on the real DatabaseDistributedLock:

  • A waiter can take the lock of a live holder at the end of a long transaction. The holder's lease on the row has run out while its transaction was open; a waiter reads the expired lease and waits for the writer beside the holder's renewal; after the commit the waiter can get the writer first. With a waiter present it won 4 of 16 rounds, and the holder's Lost fired 64 to 119 ms after the commit; when the holder began its next write at once, the waiter won 0 of 16. Before Keep a SQLite lock holder's lease while a write transaction blocks it #3182 the waiter usually ended with SqliteException; now it survives to the end of every long transaction. The worst overlaps: a schema apply whose change is applied and whose history is not written (a second run records it), and in theory a host starting with schema sync between two transactions of a schema apply that rebuilds and then renames, which leaves an added empty column beside the old one.
  • A busy file reads as "lock held". With another connection keeping the writer while the lock row is free, a take with no wait returned null after 35 seconds with no log event; schema sync then waits 15 minutes and tells the operator to check the owner and lease_until of a row that is free, and an import answers that an import into the site is running.
  • A lost lock in schema apply gives the generic error. SchemaApplyCommand.cs around lines 115 to 117 catches DbException, DbUpdateException and InvalidOperationException; a cancelled Lost token throws TaskCanceledException, so the operator gets FCMS3302 ... TaskCanceledException and not FCMS3340, which says to run again.
  • Event 9160 can misreport. It logs from the owner read before the wait for the writer and says that holder stopped without releasing it; it fires for a live holder in the race above, and when the holder released during the waiter's wait.

Proposal

  • The waiter defers: it never takes over an owned row on the first sighting of an expired lease, or when its wait for the writer was long or its previous attempt was busy; it answers "held" and takes only if a later poll, at least 250 ms on, finds the same owner and lease_until with a write that did not wait.
  • A log event when a wait ends in timeout with its last attempt busy, and the same case in the text of the schema sync timeout.
  • schema apply catches the lost lock and says to run again.
  • Event 9160 logs from a read inside the take's transaction.

Acceptance

  • With a waiter present at the end of a holder's long transaction, the holder keeps the lock, in a test that fails today
  • A wait that times out on a busy database says so in the log and in the schema sync error
  • A schema apply that loses its lock after its change says to run again
  • Event 9160 names a holder only when that holder's lease was taken over

Size: M. Goes to main, as bugs do. After #3177, which works in the same files.

Activity

  1. pournasserian commented on Nov 16, 2026

    @pournasserian
    MemberAuthor

    The decision and its argument, as built in #3195, corrected after its review measured the class. The race is narrowed, not closed. This issue stays open for the rest.

    The rule for a waiter (part 1). A take reads the lock's row again inside its write transaction. On a database with one writer, which a new provider member IDataProvider.HasOneWriter names (true on SQLite, false on PostgreSQL and SQL Server), a take-over of an owned row whose lease is over is confirmed:

    • The first sighting answers "held", and the next try comes a second later.
    • That try takes the lock over only if it finds the same owner and lease_until, and its BEGIN took at most 50 ms. The BEGIN is timed after the connection is opened.
    • A try that waited longer starts the confirmation again, and a busy try drops it.
    • A confirmation keeps its full second and goes on for up to 5 s past the caller's timeout, so TimeSpan.Zero callers (export and import) take over a holder that stopped.
    • A free row, a released row and the first use of a name are taken at once.
    • The update is conditional on the row as read inside the transaction, so 9160 names a holder only when its lease was taken over.

    What it closes. A long transaction alone no longer lets a waiter in: a take that waited for it starts again.

    What remains. Two hosts on one file, a holder lease of 1.5 s, the lease over inside a long write transaction, and other writers running 5 ms transactions with 10 ms pauses. The table gives the rounds in which the waiter took the live holder's lock. My counts were measured after merging main with #3210. The last row is the holder's own back-to-back transactions (a table rebuild).

    Other writers First review Second review Second review, main before #3195 Mine
    0 0 of 44 0 of 50 22 of 30 0 of 20
    2 0 of 24
    4 0 of 24
    8 3 of 55 3 of 50 17 of 30 0 of 20
    16 2 of 23 7 of 50 16 of 30 1 of 20
    Back-to-back transactions 0 of 30, twice 12 and 19 of 30

    The cause: a renewal that has waited longer than about 228 ms polls every 100 ms. Every fresh writer, the confirming take included, polls after 1, 2, 5, 10 and 15 ms, so the waiter gets several tries against the renewal's one or none. A backlog of requests queued behind the long transaction is that load.

    The holder's Lost fires 64 to 119 ms after a take-over only with no other writers. Under write load the renewal that would notice the loss is starved by the same writers: the second review saw Lost fire 63 ms to 1.73 s after the take-over in 11 of 14 take-overs, and not yet after 1.6 to 2.1 s in the other 3.

    Hardening tried and dropped. Requiring three tries spread over the second, each with a BEGIN under 2 ms, did not help: 4 of 60 rounds with 16 writers and 3 of 40 with 32, measured before the merge on a file database, which the stall of #3200 on in-memory SQLite does not touch. A longer confirmation does not help under sustained load either.

    Liveness. A holder that stopped is taken over about 2 s after its lease runs out on a free writer, plus a second for each try whose BEGIN waited. Of two waiters, exactly one takes the lock. While other connections keep the writer the whole time, nothing is taken over, as before.

    PostgreSQL and SQL Server. Nothing is confirmed there. They get one more round trip on a take of a free or expired row. One edge changes: a holder whose lease is over and that releases between the take's read and its update leaves the update 0 rows. The take answers "held" for one poll, and a TimeSpan.Zero caller gets null where it used to take the lock.

    Next step. The deterministic fix is for the holder to renew its row inside its own transaction before it commits. No other writer can come between them then. It changes the lock's callers, so it is left here.

    Parts 2 to 4, done in #3195:

    • 9166 is logged when a wait times out busy, and the schema sync timeout text says this can also be the cause.
    • schema apply catches the lost lock before the database's own exceptions. It says the lock was lost, naming a provider exception's type when there is one, and answers FCMS3339 during planning or the operations and FCMS3340 before the history row, each saying to run again.
    • 9160 logs from the in-transaction read. Its text says that on a database with one writer, the holder may only have been unable to renew behind other writes.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    area:storageEF Core model, providers and migrationstype:bugDefect

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions