I've been wondering if it would be possible to use 'nested transactions' in SQL Server. To test this, I've set up a little test database and executed a few
T-SQL batches:
USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
COMMIT TRAN
INSERT INTO tblTest (Name) VALUES ('Name3')
COMMIT TRAN
This is trivial, and it works as expected: 3 records have been added to the table. The next batch looks like this:
USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
COMMIT TRAN
INSERT INTO tblTest (Name) VALUES ('Name3')
ROLLBACK TRAN
This is no big deal either: as expected, no records have been added to the table. Up to the next one:
USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
ROLLBACK TRAN
INSERT INTO tblTest (Name) VALUES ('Name3')
COMMIT TRAN
This batch fails with the following error message:
Server: Msg 3902, Level 16, State 1, Line 16
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.It turns out that only the last record (Name3) is inserted into the database. That's not what I expected.
Normally, one should expect that Name1 and Name3 are persisted in the database, and only Name2 gets rollbacked.
However, as my collegue Geert pointed out: the BEGIN TRANSACTION statement increments the @@TRANCOUNT Server Variable with 1, and the COMMIT TRANSACTION decrements the @@TRANCOUNT variable with 1.
The ROLLBACK TRAN statement however, decrements the @@TRANCOUNT server variable to 0. That's why the last COMMIT statement gives us the error message: there has been a rollback, and therefore the @@Trancount is set to zero.
Apparently, the ROLLBACK TRANSACTION also rollbacks to the most outer begin transaction, that's why the record 'Name1' is not persisted into the database.
As it turns out, it is not possible to use nested transactions in this way. There is however a way to solve this 'problem':
Savepoints to the rescue
It is possible to use 'savepoints' to solve this problem. As stated in the SQL Server books online:
Savepoints offer a mechanism to roll back portions of transactions.You use savepoints like this:
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')
SAVE TRANSACTION sp1
INSERT INTO tblTest (Name) VALUES ('Name2')
ROLLBACK TRAN sp1
INSERT INTO tblTest (Name) VALUES ('Name3')
COMMIT TRAN
In this code example, you start a transaction, execute a statement, and save the transaction using the SAVE TRANSACTION sp1 statement.
This statement sets a savepoint with the name 'sp1'. You can then rollback to that savepoint using the ROLLBACK TRAN <savepointname> command.
The result of this batch is as expected: 2 records are inserted into the tblTest table: 'Name1' and 'Name3'
5 opmerkingen:
You should try nested savepoints... they don't work as expected either. A COMMIT TRAN on a savepoint seems to reset @@TRANCOUNT to 0!
See:
BEGIN TRY
CREATE TABLE TestTransactions (ID INT IDENTITY PRIMARY KEY, Value VARCHAR(30));
END TRY
BEGIN CATCH
DROP TABLE TestTransactions;
CREATE TABLE TestTransactions (ID INT IDENTITY PRIMARY KEY, Value VARCHAR(30));
END CATCH
DECLARE @transname1 VARCHAR(20);
DECLARE @transname2 VARCHAR(20);
SET @transname1 = 'savePoint';
SET @transname2 = 'blashkdjl';
BEGIN TRANSACTION
PRINT 'After opening first transaction: ' + CAST(@@TRANCOUNT AS CHAR(1));
INSERT INTO TestTransactions ( Value ) VALUES ( 'First Insert' );
SAVE TRANSACTION @transname1
PRINT 'After opening first savepoint: ' + CAST(@@TRANCOUNT AS CHAR(1));
INSERT INTO TestTransactions ( Value) VALUES ( 'Second Insert');
SAVE TRANSACTION @transname2
PRINT 'After opening second (nested) savepoint: ' + CAST(@@TRANCOUNT AS CHAR(1));
INSERT INTO TestTransactions ( Value) VALUES ( 'Third Insert');
ROLLBACK TRANSACTION @transname2
PRINT 'After closing second (nested) savepoint: ' + CAST(@@TRANCOUNT AS CHAR(1));
COMMIT TRANSACTION @transname1
PRINT 'After closing first savepoint: ' + CAST(@@TRANCOUNT AS CHAR(1));
INSERT INTO TestTransactions ( Value) VALUES ( 'Fourth Insert');
COMMIT TRANSACTION
PRINT 'After closing transaction: ' + CAST(@@TRANCOUNT AS CHAR(1));
Hmm, this is indeed not consistent with the Books Online:
"ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."
Hi, you have good explained Nested Transaction but I have a question:
If inside a transaction I want to execute some query outside the transaction how I can do?
It's possibile exclude from transaction some statement?
In this simplified example I want to Execute All the INSERT INTO Order queries within transaction but if something wrong. It rollback everything and I lost also my log
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Order ....
EXEC sp_WriteLog
INSERT INTO Order ...
EXEC sp_WriteLog
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Hi Michele,
I don't think that this is possible within T-SQL. I guess you'll have to execute those statements on another connection.
To be sure, I just did a little test:
I've created a C# program which executes 2 inserts statements on the same connection.
A transaction is started on that connection, and only the first insert statement (SqlCommand) is attached to the transaction that has been started.
The 2nd SqlCommand is not.
When I execute this program, an InvalidOperationException is thrown:
'Execute requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.
The transaction property of the command has not been properly initialized.'
So, in other words: it is indeed not possible to execute a statement outside the transaction of a connection where a transaction has been started.
But, I also do not understand your problem:
If your transaction fails (for instance, the insert in order fails for some reason), why is it a problem that your log is rollbacked ?
I mean: when the transaction fails, nothing will be inserted in 'Order', so your log will be of no use ?
Een reactie posten