I have a C# program that writes to a database table for a shipment. I have another process on the database that updates DIFFERENT rows, and different columns in the same table (i.e. Total Fees Due). The fee calculation runs in the background and adjusts fees based on new information that may have come down from another external source.
In these cases, my C# program tries to update a record, and likely hits a
TransactionOptions tro = new TransactionOptions();
tro.IsolationLevel = IsolationLevel.Serializable;
tro.Timeout = new TimeSpan(0, 0, timeoutInSeconds);
using (TransactionScope scope =
new TransactionScope(TransactionScopeOption.RequiresNew, tro))
// (1) BEGIN TRANSACTION HAPPENS HERE
using (MyEntities ctx = MyCreateContextMethod())
// IT READS THE SHIPMENT FROM THE DB HERE. WE DO NOT WANT
// TO READ UNCOMMITTED DATA.
// SELECT *
// FROM SHIPMENT
// WHERE ShipmentID = 555555666666
// AND STATUS = 'NEW'
// DO SOME VALIDATION LOGIC HERE TO MAKE SURE
// ALL CHANGES MADE ARE PERMITTED. AT _MOST_, 10ms.
// ** DURING THIS WINDOW, FEES STORED PROC RUNS (see below)
// SHIPMENT IS VALID! WE CAN SAVE THESE CHANGES NOW.
myShipmentRecord.Status = 'VALID';
// (2) ISSUES "UPDATE Shipment ..."
// (3) DEADLOCK!!!
catch (Exception ex)
throw new Exception("Error saving Shipment", ex);
} // THIS IS WHERE THE COMMIT HAPPENS NORMALLY
-- FEE COMMAND:
-- BLOCKS HERE, WAITING FOR THE SHIPMENT DB PAGE TO COME UNLOCKED.
-- WHILE NOT OPTIMAL, IT IS ACCEPTABLE.
UPDATE SHIPMENT SET FEE = 123.45
WHERE SHIPMENTID = 123456789
AND STATUS = 'VALID'
WINDOW 1 WINDOW 2
SELECT TOP (1)
* -- ALL COLUMNS
FROM [dbo].[Shipment] AS [Extent1]
WITH (ROWLOCK, UPDLOCK)
-- ADDED ROWLOCK, UPDLOCK
-- TO MIMIC WHAT IS ACTUALLY
-- HAPPENING IN .NET
[Extent1].[ShipmentID] = 55556666
SET Fee = 123.45
FROM Shipment shp
WHERE shp.ShipmentID = 55556666
-- This blocks, as expected
[ShipmentStatusID] = 30,
[LastUpdateUser] = 'SomeUser'
where ([ShipmentID] = 55556666)
COMMIT -- AT THIS MOMENT, WINDOW 2 IS UNBLOCKED
I found the issue. My flaw came in which IsolationLevel I chose:
TransactionOptions tro = new TransactionOptions(); tro.IsolationLevel = IsolationLevel.Serializable; // <--- This is the culprit tro.Timeout = new TimeSpan(0, 0, timeoutInSeconds);
Serializable will lock the records at several different levels, and is too high of a IsolationLevel for what I needed. What clued me in was that when I wrote my SQL query, I had to force UPDLOCK and ROWLOCK, assuming that was the same as the
Serializable does much more than just ROWLOCK and UPDLOCK.
After doing a ton of reading on locks, deadlocks and isolation levels, I've determined that what I really needed was simply
ReadCommitted. When I changed to this IsolationLevel, things behaved exactly as I expected, and did not cause deadlocks.
The moral of this story: Serializable is a specific-use-case IsolationLevel. Use with caution.