xml Column update and Locking in Sql Server

Question

Hi,

I have a few windwos services. They get xml column from Sql server manipulate and update it.

Service A- Gets XML
Service B- Gets XML
Service A- Updates XML (it will be lost)
Service B- Updates XML

I must lock row and I use next Code:

SqlCommand cmdUpdate = new SqlCommand();
            cmdUpdate.CommandText = "select MyXML from MyTable with(holdlock,rowlock) where id=@id";
            cmdUpdate.Parameters.AddWithValue("@id", id);

        using (SqlConnection conn = Helper.GetConnection())
        {
            cmdUpdate.Connection = conn;
            SqlTransaction ts = conn.BeginTransaction();
            cmdUpdate.Transaction = ts;
            XElement elem = XElement.Parse(cmdUpdate.ExecuteScalar().ToString());
            UpdateXElement(elem);
            cmdUpdate.Parameters.Clear();
            cmdUpdate.CommandText = "update MyTable set MyXML=@xml where id=@id";
            cmdUpdate.Parameters.AddWithValue("@id", id);
            cmdUpdate.Parameters.AddWithValue("@xml", elem.ToString());

            cmdUpdate.ExecuteNonQuery();
            ts.Commit();
        }
    }`

then occurs Deadlocks.

Have you got a better idea, to solve this problem ?

Thanks

Answer 1

The scenario you are describing is not a deadlock. It's a lock contention, in other words, exactly what the locks are for:

1. Service A- Gets XML - Service A locks XML
2. Service B- Gets XML - Services B places lock request which waits for service A to release the lock
3. Service A- Updates XML (it will be lost) - Service A should commit or rollback the transaction to release the lock.
4. Service B- Updates XML - Service B acquires the lock on the XML and updates it

Service B will be frozen between steps 2 and 3.

This means you should perform these steps as fast as possible.

Update:

You use a HOLDLOCK to lock the row in a transaction.

HOLDLOCK places a shared lock which is compatible with another shared lock but not with update lock placed by UPDATE.

Here's what happens:

1. Service A places a shared lock on row 1
2. Service B places a shared lock on row 1
3. Service A tries to place an update lock on row 1 which is not compatible with the shared lock placed by Service B on step 2. Service A enters wait state (while still holding a shared lock placed on step 1).
4. Service B tries to place an update lock on row 1 which is not compatible with the shared lock placed by Service A on step 1. Service B enters wait state. DEADLOCK.

There is no point in placing a shared lock in a SELECT clause here. You should place an UPDLOCK in a SELECT clause instead. This will make the transaction locks completely incompatible and either transaction will have to wait for completion of other transactions before acquiring any locks.

In this scenario, deadlocks are impossible.