Here's a strange problem I'm running into on a production server. It has happened twice in the last two weeks, and this is a server that gets a lot of traffic.
We have some code in a Web Service that executes a BEGIN TRAN
, then runs a few SQL queries (two inserts followed by an update). Then at the end executes a COMMIT
. Twice now we have gotten the message in the logs:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Between the first two inserts and the update, we call another web service, so there could be a slight delay between the first two inserts and last update before the COMMIT
is called. Could this be causing our problem? We're running this on IIS 7 and Server 2008 R2 (all updated applied).
Originally we though it could be the app pools getting recycled, but changed that to recycle in the middle of the night. Now I'm not sure what would be causing SQL server to forget the call to BEGIN TRAN
.
This web service does get called quite a bit. Has anyone seen something like this before? I'm at a total loss at the moment...
Any help or suggestion appreciated greatly!
Syntax and parameters: The basic syntax for using a COMMIT command in SQL SERVER is as follows : BEGIN TRANSACTION; {a set of SQL statements};
Basics/Definition. The COMMIT statement lets a user save any changes or alterations on the current transaction. These changes then remain permanent. The ROLLBACK statement lets a user undo all the alterations and changes that occurred on the current transaction after the last COMMIT.
A SQL transaction is a grouping of one or more SQL statements that interact with a database. A transaction in its entirety can commit to a database as a single logical unit or rollback (become undone) as a single logical unit. In SQL, transactions are essential for maintaining database integrity.
It looks like your transaction failed, got rolled back and there is nothing to commit
example of such a thing
CREATE TABLE BlaTest(id INT PRIMARY KEY NOT NULL) GO
Now run this
BEGIN TRAN INSERT BlaTest VALUES('a') GO COMMIT TRAN
Here is the error
Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value 'a' to data type int. Msg 3902, Level 16, State 1, Line 2 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
This will run without a problem
BEGIN TRAN INSERT BlaTest VALUES(5) GO COMMIT TRAN
A good article on transactions is Error Handling in SQL 2005 and Later by Erland Sommarskog
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With