Why transaction fails in Entity Framework

Question

I have Kendo application using Entity Framework to access stored procedures. There is transaction used in calling stored procedures. But it gives,

The transaction operation cannot be performed because there are pending requests working on this transaction.

The code is as follow,

    FlightCenterEntities _repository = new FlightCenterEntities(connectionString);
    ...
    using (TransactionScope scope = new TransactionScope())
    {
        this._repository.uspDeleteSalesSupplier(salesId);

        // Write to Sales table
        this._repository.uspUpdateSales(
                            salesId,
                            userId,
                            shop,
                            createdDateTime,
                            bookingDate,
                            passengerName,
                            destination,
                            deposit,
                            saleLocation
                            ).SingleOrDefault();

        for (int i = 0; i < list.Count; i++)
        {
            // Write to SalesSupplier table
            var returnValue = this._repository.uspAddSalesSupplier(
                                                    salesId,
                                                    list[i].SourceTypeName,
                                                    list[i].SupplierName,
                                                    list[i].SaleValue,
                                                    list[i].NumberPassengers
                                                    ).SingleOrDefault();

            status = returnValue == null ? 0 : (int)returnValue;
            if (status == 0)
                break;
        }

        scope.Complete();

The error happens at scope.Complete(). The stored procedures are as follow,

CREATE PROCEDURE [dbo].[uspDeleteSalesSupplier]
    @SalesID    int
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        UPDATE [dbo].[SalesSupplier]
        SET IsDeleted = 1
        WHERE SalesID = @SalesID

        SELECT @@Rowcount

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR    
    END CATCH;   
END

CREATE PROCEDURE [dbo].[uspUpdateSales]
    @SalesID            int,
    @UserID             varchar(50),
    @Shop               varchar(50),
    @CreatedDateTime    Datetime,
    @BookingDate        Datetime,
    @PassengerName      varchar(50),
    @Destination        varchar(50),
    @Deposit            decimal,
    @SaleLocation       varchar(50)
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        Update [dbo].[Sales]
            Set     UserID = @UserID,
                    Shop = @Shop,
                    CreatedDateTime = @CreatedDateTime,
                    BookingDate =@BookingDate,
                    PassengerName = @PassengerName,
                    Destination = @Destination,
                    Deposit = @Deposit,
                    SaleLocation = @SaleLocation
            Where   SalesID = @SalesID      

        SELECT @@Rowcount

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR
    END CATCH;
END

CREATE PROCEDURE [dbo].[uspAddSalesSupplier]
    @SalesID            int,
    @SourceType         varchar(50),
    @Supplier           varchar(50),
    @SaleValue          decimal,
    @Passengers         int
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        INSERT INTO [dbo].[SalesSupplier]
                   (SalesID,
                    SourceType,
                    Supplier,
                    SaleValue,
                    Passengers,
                    IsDeleted
                    )
             VALUES
                   (@SalesID,
                    @SourceType,
                    @Supplier,
                    @SaleValue,
                    @Passengers,
                    0
                   )

        SELECT cast(@@rowcount as int)

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR
    END CATCH;
END

It might be that you are doing other repository updates in `...`? Suggest either `SaveChanges()` before you start the Transaction around your SPROCs, or otherwise, create a NEW DbContext inside the TransactionScope just for use by your SPROC calls. — StuartLC, Jan 04 '15 at 06:09
There is another transaction scope using several Insert into and no problem happens. It looks like whenever 2 or more update in a scope will cause the error. How to create a new DbContext inside transaction scope? You can post it as an answer to get credit. — , Jan 04 '15 at 06:15

score 3 · Answer 1 · edited May 23 '17 at 11:58

3

Edit

It looks like this issue here.

The workaround is to evaluate the return result of all imported stored procs via .FirstOrDefault() or SingleOrDefault() as you've done with the other two procedures, i.e.:

var foo = this._repository.uspDeleteSalesSupplier(salesId).FirstOrDefault();

Original Answer (not helpful)

It might be that you are doing other repository updates in the section marked ...?

If there are other pending updates on the repository, you will need to flush these via SaveChanges() before you start the Transaction around your SPROCs on the same DbContext

Alternatively, create a NEW DbContext inside the TransactionScope just for use by your SPROC calls, as follows:

using (var scope = new TransactionScope(
    TransactionScopeOption.RequiresNew, 
    new TransactionOptions
    {
       IsolationLevel.ReadCommitted
    }))
{
  var sprocRepository = new FlightCenterEntities(connectionString);
  sprocRepository.uspDeleteSalesSupplier(salesId);
  ... replace the this._repository with sprocRepository for the other usp calls.

  scope.Complete();
}

Assuming that you don't require a nested TransactionScope, I've added an explicit TransactionScopeOption.RequiresNew and also reduced the isolation to ReadCommitted.

edited May 23 '17 at 11:58

Community

1
1

answered Jan 04 '15 at 06:22

StuartLC

104,537
17
209
285

No other repository updates in the section. sprocRepository exists only in the new TransactionScope, right? – Jan 04 '15 at 06:29
The procs don't actually compile, e.g. the `@Error` variables aren't declared. Possibly there is also an imbalanced `BEGIN TRAN` lurking in the PROC somewhere? – StuartLC Jan 04 '15 at 06:40
I tried creating a new DbContext like sprocRepository, but still failed with same error. – Jan 04 '15 at 06:58
You can replace this._repository by _repository. – Jan 04 '15 at 07:20
@user585440 I managed to repeat the issue - it looks like an [EF fail](http://stackoverflow.com/a/4379268/314291) :( FWIW I was using EF 6.1.2 DbFirst. – StuartLC Jan 04 '15 at 13:06
What is difference between using FirstOrDefault and SingleOrDefault? – Jan 05 '15 at 09:17
They should do the same thing - basically, anything to get EF to 'grok' that the SPROC results have completed materialization (noting that SPROCs can be used to return entity collections as well, EF will allow IQueryable compositions to be done on imported procs. Without materialization. You could probably get the same effect with `.ToList()` and `.AsEnumerable()` – StuartLC Jan 05 '15 at 09:29
I test it with FirstOrDefault after stored procedures, but failed with same error. – Jan 05 '15 at 23:00
I just change to linq to entities from stored procedures, and it works fine. So we should abandon stored procedures in this case. – Jan 06 '15 at 23:12
Re : Difference `.FirstOrDefault` -> If there is more than one, the first will be returned. `SingleOrDefault` -> if there is more than one, an exception is raised. – StuartLC May 19 '16 at 13:56

score 0 · Answer 2 · answered Nov 02 '18 at 10:37

Actually, the reason of the problem for me was another thing, I will post it here because it may be helpful for others.

the problem was that I am not closing Context after using it, In another word, I was not call dispose on this context object. so the solution was using the context object in using statement like the following

using(var context = new DbContext()) {
        // do your work here
}

Why transaction fails in Entity Framework

2 Answers2