Support SELECT FOR UPDATE / UPDLOCK (pessimistic concurrency)

[Lobosque]

I want to garantee that only one Api Request (Database Transaction) can modify an entity at a given time. this could be done by selecting a row with "SELECT FOR UPDATE" inside a transaction.

Something like:

context.Books
                        .Where(b => b.Id == 1)
                        .SelectForUpdate(skipLocked: true)
                        .FirstOrDefault();

Would generate something like:

                SELECT *
                FROM books b
                WHERE b.id = 1
                FOR UPDATE SKIP LOCKED
                LIMIT 1";

I think that EF itself does not need to track at any level that the entity was selected with a table hint, it only need to be able to express the SQL.

[roji]

This may be covered by #6717, and may be achievable today by using raw SQL and composing your LINQ operators over it (see the docs), assuming SQL Server allows SELECT FOR UPDATE in a subquery.

[roji]

Note: use table annotations to represent this (#26858).

[roji]

Cross-database investigation on SELECT FOR UPDATE

The FOR UPDATE clause makes the database lock rows which are selected by the query; other transactions which query a locked row with FOR UPDATE (or UPDATE it) will wait until the first transaction ends.

Comparison to optimistic concurrency:

• This removes the need to handle optimistic concurrency exceptions and retry, simplifying user code significantly.
• No need for a concurrency token, and no need to select it back when updating.
• If two transactions select rows in reverse order, a deadlock occurs, causing an error. This is similar to the deadlock that can happen with UPDATE (docs). This can be mitigated by using the same ordering, or if not feasible, catching the deadlock exception and retrying.
• If transactions are long (not recommended), then competing transactions may wait for a long time. With optimistic concurrency this does not occur.
• In disconnected scenarios, selection and update typically cannot occur in the same transaction. Unless the database row is selected a second time just before update, SELECT FOR UPDATE isn't relevant.

PostgreSQL

Documentation

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (bar INT);
INSERT INTO foo VALUES (1), (2), (3), (4), (5);
CREATE TABLE foo2 (bar INT);
INSERT INTO foo2 VALUES (1), (2), (3), (4), (5);

-- Simple, top-level scenario
SELECT * FROM foo FOR UPDATE;

-- On a specific table with JOIN:
SELECT * FROM foo
JOIN foo2 ON foo2.bar = foo.bar FOR UPDATE;

-- Inside a subquery:
SELECT * FROM (
    SELECT * FROM foo FOR UPDATE
) x;

• Instead of blocking on locked rows, you can specify NOWAIT (to get an error) or SKIP LOCKED (to skip locked rows altogether).
• Weaker lock strengths are available, e.g. SELECT ... FOR SHARE which blocks updates but allows other FOR SHARE locks (read-write lock).

SQL Server

No SELECT FOR UPDATE syntax - the UPDLOCK table hint is used instead (documentation);

CREATE TABLE foo (bar INT);
INSERT INTO foo VALUES (1), (2), (3), (4), (5);
CREATE TABLE foo2 (bar INT);
INSERT INTO foo2 VALUES (1), (2), (3), (4), (5);

-- Simple, top-level scenario
SELECT * FROM foo WITH (UPDLOCK);

-- On a specific table with JOIN:
SELECT * FROM foo
JOIN foo2 WITH (UPDLOCK) ON foo2.bar = foo.bar;

-- Inside a subquery:
SELECT * FROM (
    SELECT * FROM foo (UPDLOCK)
) x;

MariaDB

MariaDB documentation
MySQL documentation

SQL: identical to PostgreSQL above

• Also supports IN SHARE MODE for read/write locking, like PostgreSQL FOR SHARE.

SQLite

Not supported (no concurrency/row locking)

Oracle

Documentation

Firebird

Documentation (some interaction with WITH LOCK feature)

---

Based on the above, we could consider doing the following:

• Building on table annotations, we could introduce a relational-level ForUpdate().
• This would be generated as FOR UPDATE by default, with SQL Server overriding to generate UPDLOCK instead.
• For Include, the metadata extension could be specified inside the Include (i.e. context.Blogs.Include(b => b.Posts).ForUpdate()). This may require additional work in Query: improve TableExpressionBase extensibility by adding annotations #26858.
• PostgreSQL, MariaDB would have an additional ForShare() and other operators.

/cc @maumar

[benlongo]

Another use case is FOR UPDATE SKIP LOCKED to implement a queue in postgres.

[n0099]

For anyone who can't wait for this feature, check out this simple command interceptor or try Linq2Db.

[roji]

Another use-case: atomically update something with ExecuteUpdate. This can be done today with optimistic locking, but that requires retrying when the update fails; that means some sort of random delay + backoff strategy is needed, where pessimistic locking can solve this much more easily.