Lock selected sql resources using Entity Framework

Question

I am trying to write a SELECT... FOR UPDATE query using Entity Framework (for a Postgresql db). The problem is that using any IsolationLevel, the second request will only block after updating the data (step 2 in the picture) and not from the first (getting the response step in the picture). Chaos level is not supported (not sure why).

I need the resources to be locked even from the selection, otherwise both requests will take the same resources!

I can't find a solution using EF, but this will work using a SQL function (+migration) which is not the preferred choice!

thanks

// Serializable Summary
//     A range lock is placed on the System.Data.DataSet, preventing other users from
//     updating or inserting rows into the dataset until the transaction is complete.

await using var transaction = await _dbContext.Database.BeginTransactionAsync(
    System.Data.IsolationLevel.Serializable,
    cancellationToken);

try
{
    var query = _dbContext.ValidationRequests
            .Where(vr => vr.State == Statuses.New)
            .OrderBy(vr => vr.Id);

    var response = await PagedList<Model>.GetPagedListAsync(
        query,
        page,
        pageSize,
        cancellationToken
    );

    await UpdateRangeAsync(response.Items, cancellationToken);

    await transaction.CommitAsync(cancellationToken);

    return response;
}
catch (Exception)
{
    await transaction.RollbackAsync(cancellationToken);

    throw;
}

GetPagedListAsync method:

public static async Task<PagedList<T>> GetPagedListAsync(
        IQueryable<T> query,
        int page,
        int pageSize,
        CancellationToken cancellationToken = default)
    {
        int totalCount = await query.CountAsync(cancellationToken);

        var items = await query
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync(cancellationToken);

        return new(page, pageSize, items, totalCount);
    }

and the update method:

private async Task UpdateRangeAsync(List<Model> requests, CancellationToken cancellationToken = default)
{
    if (!requests.Any())
    {
        return;
    }

    requests.ForEach(item => item.State = Statuses.Pending);

    _dbContext.UpdateRange(requests);
    await _dbContext.SaveChangesAsync(cancellationToken);
}

Answer 1

You can use the new ExecuteUpdateAsync feature to do a joined UPDATE statement. This means you don't need a transaction as the whole statement is atomic anyway.

Something like this would work, although I'm not entirely clear on your object model.

var baseQuery = _dbContext.ValidationRequests
    .Where(vr => vr.State == Statuses.New)
    .OrderBy(vr => vr.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize);

await baseQuery.ExecuteUpdateAsync(setters => setters
    .SetProperty(item => item.State, Statuses.Pending),
    cancellationToken);

Note that there is no ToList anywhere, because that would cause client-side evaluation and doesn't work with ExecuteUpdate.

Do note that Skip Take can be highly inefficient even with a supporting index, and you would be better off using Keyset Pagination.

---

If you need the actual rows then you probably want a RETURNING or OUTPUT clause (depending on your DBMS), in which case a raw SQL query is probably best.

Unfortunately, Postgres does not allow LIMIT directly on the target table so you need a self join.

var results = await _dbContext.ValidationRequests.FromSql($@"
UPDATE ValidationRequests
SET State = 'New'
FROM (
    SELECT vr.*
    FROM ValidationRequests vr
    ORDER BY vr.Id
    LIMIT {pageSize} OFFSET {(page - 1) * pageSize}
    FOR UPDATE
) vr
  ON vr.Id = ValidationRequests.Id
RETURNING *;
").ToListAsync(cancellationToken);

In SQL Server you could update the subquery directly

var results = await _dbContext.ValidationRequests.FromSql($@"
UPDATE vr
SET State = 'New'
OUTPUT inserted.*
FROM (
    SELECT vr.*
    FROM ValidationRequests vr
    ORDER BY vr.Id
    OFFSET {(page - 1) * pageSize} ROWS
    FETCH NEXT {pageSize} ROWS ONLY
) vr;
").ToListAsync(cancellationToken);