Locking Rows

ForUpdate() locks the rows a query selects until the end of the current transaction, so no other transaction can update, delete or lock them in between reading and updating them:

using var trans = db.OpenTransaction();

var account = db.Single(db.From<Account>().Where(x => x.Id == id).ForUpdate());
account.Balance -= amount;
db.Update(account);

trans.Commit();

SELECT "Id", "Balance"
FROM "Account" WITH (UPDLOCK, ROWLOCK)
WHERE ("Id" = @0)
-- @0 = 1

UPDATE "Account" SET "Balance"=@Balance WHERE "Id"=@Id
-- @Balance = 75, @Id = 1

ForUpdate(skipLocked: true) skips rows that are already locked instead of waiting for them, which lets multiple workers take different items from the same queue table:

var jobs = db.Select(db.From<Job>()
    .Where(x => x.Status == "Queued")
    .OrderBy(x => x.Id)
    .Take(10)
    .ForUpdate(skipLocked: true));

SELECT TOP 10 "Id", "Status"
FROM "Job" WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE ("Status" = @0)
ORDER BY "Id"
-- @0 = 'Queued'

Preventing lost updates​

Without locking, two requests that read and then update the same row can overwrite each other's changes:

1. Request A reads a balance of 100
2. Request B reads a balance of 100
3. Request A adds 10 and saves 110
4. Request B adds 10 and saves 110, losing A's deposit

With ForUpdate(), request B's read waits until request A's transaction ends, then reads the updated balance:

using (var trans = db.OpenTransaction())
{
    var account = db.Single(db.From<Account>().Where(x => x.Id == id).ForUpdate());
    account.Balance += 10;
    db.Update(account);
    trans.Commit(); // releases the lock, other requests now read 110
}

SELECT "Id", "Balance"
FROM "Account" WITH (UPDLOCK, ROWLOCK)
WHERE ("Id" = @0)
-- @0 = 1

UPDATE "Account" SET "Balance"=@Balance WHERE "Id"=@Id
-- @Balance = 110, @Id = 1

Locks are held until the transaction is committed or rolled back, so keep these transactions short. Outside a transaction the lock is released as soon as the query completes, so ForUpdate() should always be used inside one.

Work queues​

Multiple workers can take work from the same table without taking the same items. Each worker locks the next queued items, skipping any items locked by other workers, and marks them as being processed:

using var trans = db.OpenTransaction();

var jobs = db.Select(db.From<Job>()
    .Where(x => x.Queue == "emails" && x.Status == "Queued")
    .OrderBy(x => x.Id)
    .Take(10)
    .ForUpdate(skipLocked: true));

foreach (var job in jobs)
{
    db.UpdateOnly(() => new Job { Status = "Processing", ClaimedBy = workerId },
        where: x => x.Id == job.Id);
}

trans.Commit();

Workers don't wait for each other, and each queued item is only taken by one worker. After the transaction commits, the items are no longer Queued so they won't be selected again.

Compared to taking items with DeleteReturning(), this keeps the items in the table while they're processed, so they can be retried if a worker fails.

Queries with joins​

Only rows of the query's table are locked where supported:

// Lock the orders of a customer, the Customer row isn't locked
var orders = db.Select(db.From<Order>()
    .Join<Customer>((o, c) => o.CustomerId == c.Id)
    .Where<Customer>(c => c.Email == email)
    .ForUpdate());

PostgreSQL uses FOR UPDATE OF the query's table and SQL Server adds the lock hint to the query's table only. MySQL, MariaDB and Oracle lock the selected rows of all joined tables.

Async​

var account = await db.SingleAsync(db.From<Account>().Where(x => x.Id == id).ForUpdate());
var jobs = await db.SelectAsync(db.From<Job>().Where(x => x.Status == "Queued").Take(10).ForUpdate(skipLocked: true));

RDBMS support​

SQLite only has database-level locks, so ForUpdate() is ignored, which lets the same code run on SQLite, e.g. in development and tests. As rows aren't locked, concurrent SQLite transactions using skipLocked can select the same rows.

ForUpdate() can't be combined with set operations like Union(), WithRecursive() queries or queries of a system-versioned table's previous versions like AsOf(), which throw a NotSupportedException. PostgreSQL also rejects locks on queries with DISTINCT, GROUP BY or aggregates.