Dapper transaction, lock row on read

Question

I'm in need of a read lock for dapper, because I have one specific case when I need to read row from database and lock it and then perform some other operations.

This is how I call dapper:

    protected async Task<List<T>> LoadData<T, U>(string sql, U parameters)
    {
        var rows = await getConnection().QueryAsync<T>(sql, parameters, commandType: CommandType.StoredProcedure, transaction: getTransaction());

        return rows.ToList();
    }

But no rows are locked. Interestingly transaction on saving data works just fine.

    protected async Task<int> SaveData<U>(string sql, CommandType commandType, U parameters)
    {
        return await getConnection().ExecuteAsync(sql, parameters, commandType: commandType, transaction: getTransaction());
    }

Connection is opened before calling dapper and transaction is started...like this:

        if (connection.State != ConnectionState.Open)
            connection.Open();

        transaction = connection.BeginTransaction(IsolationLevel.Serializable);

SQL query is a plain select statement like this:

    SELECT * FROM [dbo].[Tab01] WHERE [Id] = @Id

Locking is entirely up to the database engine and the SQL you submit, Dapper has nothing to do with this. Can you post your query and the DB engine you're using? — Alejandro, Mar 08 '21 at 19:45
And, as you have noted, it gets set at the connection level (well above Dapper's involvement) — Flydog57, Mar 08 '21 at 19:52
Ive taged the db and added the sql query, thanks for reminder. — DavidWaldo, Mar 08 '21 at 19:55

David Browne - Microsoft · Accepted Answer · 2021-03-11T14:33:40.677

In SQL Server a SERIALIZABLE transaction won't use exclusive or restrictive locks on SELECT queries. Instead shared locks are taken and held for the duration of the transaction corresponding to your SELECT queries. So if you run

 SELECT * FROM [dbo].[Tab01] WHERE [Id] = @Id

Your transaction will receive Shared (S) key range lock on the Id index of Tab01 covering the value of @Id. This will prevent any other session from changing that row, or inserting a row with that ID.

If two SERIALIZABLE transactions both run

 SELECT * FROM [dbo].[Tab01] WHERE [Id] = @Id

for the same Id, and then both attempt to INSERT a row with that value, or update that row, one will fail with a deadlock. So SERIALIZABLE does prevent the sessions from overwriting each others data, it uses a deadlock error to do it, which can be inconvenient.

SERIALIZABLE guarantees that the committed results are the same as if sessions had been serialized (executed one-at-a-time), but it doesn't actually perform that serialization. It optimistically allows concurrent transactions to read the same data, but more than one session attempts to update the data that was read, it will cause a deadlock.

In SQL Server to place a restrictive lock on read requires a lock hint. Typically you use:

 SELECT * FROM [dbo].[Tab01] with (updlock,serializable) WHERE [Id] = @Id

which forces the read to use a more restrictive update (U) lock, and to use SERIALIZABLE-style key range locking in case there are currently no rows with that Id.

If you use the lock hint (updlock,serializable), do you still have to initialize transaction? What happens if you do not initialize transaction (no Begin TRAN)? — DavidWaldo, Mar 08 '21 at 20:18
`SERIALIZABLE` does not by itself cause *deadlocks* (although it can cause them more often due to more and longer locking), it causes *blocking* and *lock timeouts* which are absolutely not the same thing. And recommending holding a lock for the duration of a client-side operation is a really bad idea. — Charlieface, Mar 08 '21 at 20:28
Without a transaction all your locks are released after each statement. — David Browne - Microsoft, Mar 08 '21 at 20:40

Charlieface · Answer 2 · 2021-03-08T20:42:33.200

It's a really, really bad idea to hold onto locks for any extended period of time. Because you have no control over the granularity and length of locking, you may end up with half your database locked up.

What you should be doing is one of two options

Either do your "operation" in a single batch with a transaction
Or lock the row using another mechanism. For this second option, you could possibly have a LockedBy column, or perhaps make use of sp_getapplock.

There is another concern with your code:

All indications are that you are creating and caching connection and transaction objects elsewhere, and are not disposing with using.

Your code should instead look something like this:

    protected Task DoStuff()
    {
        using(var connection = new SqlConnection(getConnectionString())
        {
            LoadData(conn, ....)
            
        }
    }

    protected async Task<List<T>> LoadData<T, U>(SqlConnection conn, string sql, U parameters)
    {
        using(var tran = conn.BeginTransaction())
        {
            var rows = await conn.QueryAsync<T>(sql, parameters, commandType: CommandType.StoredProcedure, transaction: tran);
            tran.Commit();
            return rows.ToList();
        }
    }

    protected async Task<int> SaveData<U>(SqlConnection conn, string sql, CommandType commandType, U parameters)
    {
        using(var tran = conn.BeginTransaction())
        {
            var result = await conn.ExecuteAsync(sql, parameters, commandType: commandType, transaction: tran);
            tran.Commit();
            return result;
        }
    }

See also C# Data Connections Best Practice?

I'm disposing all transactions and connections, but getting that into the question would only complicated it. My problem is, that I have an operation that happens on notification from external source. When notification comes, I select a row from my database, sync it with external source, then if conditions are met I also do other important stuff. Problem is, notification sometimes comes twice. — DavidWaldo, Mar 08 '21 at 20:40

Dapper transaction, lock row on read

2 Answers2