Chris Jelly Chris Jelly - 1 month ago 16
C# Question

Transactions with IsolationLevel Snapshot cannot be promoted

I'm trying to wrap a TransactionScope around my call to a stored procedure via Entity v.4.0.30319. I keep encountering the following exception:


Transactions with IsolationLevel Snapshot cannot be promoted.


How can I get around this?

The underlying stored procedure is basically one big insert statement into a table.

My code is as follows:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, GetTransactionOptions()))
{
int? status;
status = GetStatusIDFromEnum(newMatterCredential);

using (MatterCredentialsEntities db = new MatterCredentialsEntities())
{
DateTime? objDateAnnounced = GenerateNullForDateTime(newMatterCredential.DateAnnounced);
DateTime? objDateClosed = GenerateNullForDateTime(newMatterCredential.DateClosed);
DateTime? objDateFinancialClosed = GenerateNullForDateTime(newMatterCredential.DateFinancialClosed);
db.prcCreateCredential(Common.GetUserProfID(), newMatterCredential.MatterID, status, newMatterCredential.DescriptionSummary, newMatterCredential.DescriptionDetailed, newMatterCredential.BusinessEntitySectorID, newMatterCredential.BusinessEntityRoleID, newMatterCredential.UNGeographyID, newMatterCredential.ProjectName, newMatterCredential.ClientIndustryId, newMatterCredential.TransactionValue, newMatterCredential.TransactionCurrencyID, newMatterCredential.OtherParties, newMatterCredential.LegalAdvisers, newMatterCredential.DateAnnounced, newMatterCredential.DateClosed, newMatterCredential.DateFinancialClosed, newMatterCredential.Award, newMatterCredential.NotifyPartner, newMatterCredential.Notes);
}

scope.Complete();
}

public static TransactionOptions GetTransactionOptions()
{
TransactionOptions tranOpt = new TransactionOptions();
tranOpt.IsolationLevel = IsolationLevel.Snapshot;
return tranOpt;
}

Answer

MSDN says you cannot promote a transaction with snapshot isolation.

MSDN - IsolationLevel Enumeration

Snapshot - Volatile data can be read. Before a transaction modifies data, it verifies if another transaction has changed the data after it was initially read. If the data has been updated, an error is raised. This allows a transaction to get to the previously committed value of the data. When you try to promote a transaction that was created with this isolation level, an InvalidOperationException is thrown with the error message:

Transactions with IsolationLevel Snapshot cannot be promoted

Something else must be changing the data since you started the transaction, if this is part of larger transaction that it is participating in

I suggest changing the transaction to serializable.

public static TransactionOptions GetTransactionOptions()
{
    TransactionOptions tranOpt = new TransactionOptions();
    tranOpt.IsolationLevel = IsolationLevel.Serializable;
    return tranOpt;
}

Edit: See below ensure you have MSDTC running as this wants to create a distributed transaction.

Comments