CODEWITHSUNDEEP
c#sql-server-2008
Andre Tchernikov
Andre Tchernikov

Reputation: 139

C#, SQLServer - block table row reading, edit if needed and release lock

I need to block database reads on row level while I'm executing update logic for same row. How would nice and clean solution look like? Here is some code I'm working on:

using (SqlConnection conn = new SqlConnection(Configuration.ConnectionString)) {
    conn.Open();
    using (SqlTransaction tran = conn.BeginTransaction(IsolationLevel.Serializable)) {
        //TODO lock table row with ID=primaryKey (block other reads and updates)
        using (SqlCommand cmd = new SqlCommand("SELECT Data FROM MyTable WHERE ID=@primaryKey", conn)) { 
            cmd.Parameters.AddWithValue("@primaryKey", primaryKey);
            using (var reader = cmd.ExecuteReader()) {
                data = PopulateData(reader);
            };
        }

        if (isUpdateNeeded(data)) {
            ChangeExternalSystemStateAndUpdateData(data) //EDIT - concurrent calls not allowed
            WriteUpdatesToDatabase(conn, tran, data);    //EDIT
            tran.Commit();
        }


    } //realease lock and allow other processes to read row with ID=primaryKey
}

EDIT:

I have following constraints:

Upvotes: 2

Views: 501

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1064204

Usually the problem here isn't so much row locking, but rather: other SPIDs acquiring read locks between your read and your update - which can lead to deadlock scenarios; the common fix here is to acquire a write lock in the initial read:

SELECT Data FROM MyTable WITH (UPDLOCK) WHERE ID=@primaryKey

Note that if another SPID is explicitly using NOLOCK then they'll still blitz past it.

You could also try adding ROWLOCK, i.e. WITH (UPDLOCK, ROWLOCK) - personally I'd keep it simple initially. Since you're in a serializable context, trying to be too granular may be a lost cause - key-range locks, etc.

Upvotes: 2

Related Questions