none
C# vs. SQL transactions RRS feed

  • Question

  • Hello,

    We have an MVC application that initiates transactions for every request made to the controllers:

    public class TransactionPerRequest : IRunOnEachRequest, IRunOnError, IRunAfterEachRequest
        {
    ...
            /// <summary>
            /// Runs at the begining of each Post request to setup 
            /// a Transaction wrapper
            /// </summary>
            void IRunOnEachRequest.Execute()
            {
                if (_httpContext.Request.HttpMethod == "POST")
                {
                    _httpContext.Items["_Transaction"] =
                        _context.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
            }
    ...
            /// <summary>
            /// Commits or rollsback the transaction at the end or each request dependant on the
            /// <c>HttpContext</c> <c>_Error</c> item's value
            /// This is only run for Post requests 
            /// </summary>
            void IRunAfterEachRequest.Execute()
            {
                if (_httpContext.Request.HttpMethod == "POST")
                {
                    var transaction = (DbContextTransaction) _httpContext.Items["_Transaction"];
                    var badRequest = (_httpContext.Response.StatusCode != (int)HttpStatusCode.OK &&
                        _httpContext.Response.StatusCode != (int)HttpStatusCode.Redirect &&
                        _httpContext.Response.StatusCode != (int)HttpStatusCode.MovedPermanently);
                    if (transaction?.UnderlyingTransaction?.Connection == null
                     || transaction.UnderlyingTransaction.Connection.State != ConnectionState.Open)
                    {
                        Debug.WriteLine("Skipping transaction commit or rollback because the underlying connection is closed");
                        return;
                    }
    
                    if (_httpContext.Items["_Error"] != null  || badRequest)
                    {
                       
                        transaction.Rollback();
                        transaction.Dispose();
                    }
                    else
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                }
            }
        }

    We also have a stored procedure wrapped in a transaction:

    ALTER PROCEDURE [dbo].[DeleteProjectData]
    	@projectId INT,
    	@deleteType VARCHAR(10),
    	@username NVARCHAR(255)
    AS
    BEGIN
    	
    -- setup
    
    	BEGIN TRY
    	
    	BEGIN TRANSACTION
    
    -- Changes to database happen here
    
    	COMMIT TRANSACTION
    
    	END TRY
    	BEGIN CATCH
    		PRINT 'Exception thrown: ' + ERROR_MESSAGE()
    		PRINT 'Line number     : ' + CAST(ERROR_LINE() AS VARCHAR)
    
    		IF(@@TRANCOUNT > 0)
            BEGIN
                ROLLBACK TRANSACTION
            END
    	END CATCH
    	
    END

    So my question is: is this redundant? If a request is made (thus initiating a transaction in the C# code), and part of processing that request involves calling the stored procedure, do I need both transactions, or will the C# one suffice?

    The main use we're getting out of these transactions is automatic roll back in case something goes wrong. So suppose we removed the BEGIN TRANSACTION and COMMIT TRANSACTION from the stored procedure, and something went wrong in the middle of executing the stored procedure, will the C# transaction roll back any changes?

    I'm guessing that unless the C# code is somehow able to track database changes, we will still need stored procedures to be wrapped in transactions so that if anything goes wrong in the execution of the stored procedure, the database can handle the roll back. Am I right?

    If I'm right, what kind of roll backs will the C# transaction handle? We use Entity Framework. Any changes we make to the entities take effect first in the Entity Framework cache, and only if we call context.SaveChanges() will those changes be committed to the database. But if something goes wrong before the call to context.SaveChanges(), I'm guessing this is where the C# transaction handles roll backs. That is to say, it rolls back changes to the Entity Framework cache. Am I right?
    • Edited by gib898 Tuesday, April 24, 2018 3:34 PM formatting
    Tuesday, April 24, 2018 3:33 PM

All replies

  • So my question is: is this redundant? If a request is made (thus initiating a transaction in the C# code), and part of processing that request involves calling the stored procedure, do I need both transactions, or will the C# one suffice?

    It's redundant. The transaction commit or rollback can happen in the C# code or the sproc but not both. 

    The main use we're getting out of these transactions is automatic roll back in case something goes wrong. So suppose we removed the BEGIN TRANSACTION and COMMIT TRANSACTION from the stored procedure, and something went wrong in the middle of executing the stored procedure, will the C# transaction roll back any changes?

    The  transaction scope initiated in the C# code works in conjunction with the database engine to either commit records to tables or rollback records from tables within the transaction scope, upon success or failure. 

    I'm guessing that unless the C# code is somehow able to track database changes, we will still need stored procedures to be wrapped in transactions so that if anything goes wrong in the execution of the stored procedure, the database can handle the roll back. Am I right?

    No you are not correct. One transaction scope either initiated in the C# code or initiated in the sproc covers all the record transactions with either one working in conjunction with the database engine. 

    If I'm right, what kind of roll backs will the C# transaction handle?

    It's the same no matter where the transaction scope is initiated C# code or sproc code. 

    But if something goes wrong before the call to context.SaveChanges(), I'm guessing this is where the C# transaction handles roll backs.

    No, this is not correct. It only happens at the database level with the database engine as the database engine persists changes to database table or tables with records. 

    That is to say, it rolls back changes to the Entity Framework cache. Am I right?

    No, this is not correct.

    We have an MVC application that initiates transactions for every request made to the controllers:

    I would also say that the above is questionable concerning the MVC UI design pattern, and at best, the transactions scope should be initiated in the model with the model doing CRUD with the database, with model calling a repository to do the CRUD or the model calling the DAL to do the CRUD.  

    Ideally, it should not be happing in the controller as the controller's job is flow control. 

    To me, it doesn't matter if one is using MVC 5 or Core concerning the effective usage of the MVC UI design pattern. 

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copied>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    Tuesday, April 24, 2018 8:37 PM
  • Thanks for the feedback, DA, it was helpful.
    Tuesday, April 24, 2018 10:52 PM
  • Hi gib898,

    >>Thanks for the feedback, DA, itwashelpful.

    If the reply is helpful for you, please markit as answer, it will be beneficial to other communities whohave the similar issue.


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 25, 2018 12:54 AM
    Moderator