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?
Yes, more than redundant. Form the code it looks like you might have 3 transactions; C#, EF, and stored procedure.
gib9898_00
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?
I can't think of a reason why you would use C# to open a connection and send a BEGIN TRANSACTION when you are invoking a stored procedures. I would let the stored procedure handle the transaction. Much simpler, IMHO.
gib9898_00
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?
That's dependent on how well your code is written. Usually a transaction in C# is wrapped in a using block. It seems you're design is managing transaction state in the current context. It is not clear what happen when there is an error as you did not
post that bit of code. Also it looks like you 're using EF which also wraps SQL scripts in a transaction.
gib9898_00
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?
Again, a transaction in C# is usually wrapped in a using block. You've created a custom transaction framework which means you need to make sure the execution has defined start and end methods/layers that always fire regardless of exceptions.
IMHO, you've made this far more complicated than needed.
If you have a situation where there is a mix of procs and EF then you should handle this as a separate service and wrap the whole enchalada in a transcation.
1) simple (local) transactions, which are the most performant)
begin tran
.....
commit tran
this is what you sp is doing. sqlcommand also supports this
cmd.BeginTransaction()
just sends a "begin transaction" to the server
2) sqlserver also supports 2 phase commits, which are much more expensive.
this allows two databases or two connections to be involved transaction. The transaction scope is the C# interface to 2 phase commit. because your sp is running in a 2 phase commit, its commit does not do a real commit, its still pending until the 2 phase
commit is done.
in your case, you are using a 2 phase commit for your request. the request may make multiple SP calls, each one is a logical unit, so the transaction logic make sense in each SP. now if any of the SP calls fail, they are all rolled back (even if they committed).
I assume this is why you added the overhead of 2 phase commit.
note: I personally avoid 2 phase commit whenever possible.
Thanks both for your feedback. You have confirmed my thoughts. I didn't write this code. A co-worker and I are talking about simplifying it and removing the C# transaction is what we are considering.
Member
25 Points
231 Posts
C# vs. SQL transactions
Apr 24, 2018 03:28 PM|gib9898_00|LINK
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 ENDSo 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?
All-Star
43741 Points
18722 Posts
Re: C# vs. SQL transactions
Apr 24, 2018 04:44 PM|mgebhard|LINK
Yes, more than redundant. Form the code it looks like you might have 3 transactions; C#, EF, and stored procedure.
I can't think of a reason why you would use C# to open a connection and send a BEGIN TRANSACTION when you are invoking a stored procedures. I would let the stored procedure handle the transaction. Much simpler, IMHO.
That's dependent on how well your code is written. Usually a transaction in C# is wrapped in a using block. It seems you're design is managing transaction state in the current context. It is not clear what happen when there is an error as you did not post that bit of code. Also it looks like you 're using EF which also wraps SQL scripts in a transaction.
Again, a transaction in C# is usually wrapped in a using block. You've created a custom transaction framework which means you need to make sure the execution has defined start and end methods/layers that always fire regardless of exceptions.
IMHO, you've made this far more complicated than needed.
If you have a situation where there is a mix of procs and EF then you should handle this as a separate service and wrap the whole enchalada in a transcation.
All-Star
53574 Points
13316 Posts
Re: C# vs. SQL transactions
Apr 24, 2018 05:03 PM|bruce (sqlwork.com)|LINK
sqlserver support two types of transaction
1) simple (local) transactions, which are the most performant)
begin tran
.....
commit tran
this is what you sp is doing. sqlcommand also supports this
cmd.BeginTransaction()
just sends a "begin transaction" to the server
2) sqlserver also supports 2 phase commits, which are much more expensive.
this allows two databases or two connections to be involved transaction. The transaction scope is the C# interface to 2 phase commit. because your sp is running in a 2 phase commit, its commit does not do a real commit, its still pending until the 2 phase commit is done.
in your case, you are using a 2 phase commit for your request. the request may make multiple SP calls, each one is a logical unit, so the transaction logic make sense in each SP. now if any of the SP calls fail, they are all rolled back (even if they committed).
I assume this is why you added the overhead of 2 phase commit.
note: I personally avoid 2 phase commit whenever possible.
Member
25 Points
231 Posts
Re: C# vs. SQL transactions
Apr 24, 2018 10:46 PM|gib9898_00|LINK
Thanks both for your feedback. You have confirmed my thoughts. I didn't write this code. A co-worker and I are talking about simplifying it and removing the C# transaction is what we are considering.