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 PAGE_LOCK. Something occasionally causes a lock to happen. Either way, I would expect this code to block as long as necessary, issue a commit so that the other items can continue to process, but should not deadlock.
When I connect SQL Profiler, I can see when
I've noted those places in the comments of the code. Using debugging, I can re-create this event 100% of the time by stopping the code and running the fee update at the specified moment, then resuming.
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)
if (ValidateShipment(myShipmentRecord))
{
// SHIPMENT IS VALID! WE CAN SAVE THESE CHANGES NOW.
myShipmentRecord.Status = 'VALID';
try
{
// (2) ISSUES "UPDATE Shipment ..."
// (3) DEADLOCK!!!
ctx.SaveChanges();
}
catch (Exception ex)
{
throw new Exception("Error saving Shipment", ex);
}
}
scope.Complete();
}
} // THIS IS WHERE THE COMMIT HAPPENS NORMALLY
The fee command tries to run on the DB, and blocks -- which is expected behavior.
-- FEE COMMAND:
-- -------------------------------
BEGIN TRANSACTION
-- 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'
COMMIT
What I am expecting to happen is that the .NET code would issue the UPDATE and succeed because it owns the lock (right??). It would then issue COMMIT through the completion and disposal of the scope, and the FEE COMMAND would then be un-blocked and would update as normal.
What actually happens is that Entity Framework causes a deadlock at SaveChanges(), and EF is always chosen as the deadlock victim.
Note that fees never run and update on the same shipments as the C# code. My only guess as to how the deadlock happens at all is that it is locking the DB page, not the row itself.
My questions:
If I were to do two different transactions in two different SSMS consoles to mimic this same behavior (running the actual commands from SQL Profiler in the exact same order), things work as expected. Each process blocks at the corresponding times and things go through as normal. Why does Entity Framework cause a deadlock when issuing SaveChanges()?
Is there a definitive work-around?
UPDATE
Below is the same items I run in two separate SSMS windows. In this case, I'm even updating the SAME ROW just to force row-lock dependencies, which is not what is happening in production.
WINDOW 1 WINDOW 2
BEGIN TRANSACTION
SELECT TOP (1)
* -- ALL COLUMNS
FROM [dbo].[Shipment] AS [Extent1]
WITH (ROWLOCK, UPDLOCK)
-- ADDED ROWLOCK, UPDLOCK
-- TO MIMIC WHAT IS ACTUALLY
-- HAPPENING IN .NET
WHERE
[Extent1].[ShipmentID] = 55556666
BEGIN TRANSACTION
UPDATE shp
SET Fee = 123.45
FROM Shipment shp
WHERE shp.ShipmentID = 55556666
-- This blocks, as expected
UPDATE [dbo].[Shipment]
SET
[ShipmentStatusID] = 30,
[LastUpdateUser] = 'SomeUser'
where ([ShipmentID] = 55556666)
COMMIT -- AT THIS MOMENT, WINDOW 2 IS UNBLOCKED
COMMIT
All of this runs as expected without deadlocks.
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. Unfortunately, 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.