There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).
From the documentation:
It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
A demonstration:
CREATE TABLE dbo.foo(a INT);
BEGIN TRANSACTION;
INSERT dbo.foo(a) SELECT 1;
BEGIN TRANSACTION;
INSERT dbo.foo(a) SELECT 2;
BEGIN TRANSACTION;
ROLLBACK TRANSACTION;
SELECT tc = @@TRANCOUNT;
SELECT a FROM dbo.foo;
DROP TABLE dbo.foo;
Results:
tc
----
0
1 row(s) affected.
a
----
0 row(s) affected.
So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.
CREATE PROCEDURE `t`(IN `r` TINYINT, OUT `l` TINYINT, OUT `z` TINYINT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE exit HANDLER
FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l = -1;
SET z = -1;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO b (f)
VALUES( r );
SET l = LAST_INSERT_ID();
CALL s( r, z );
IF z==-1
THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
EDIT:1
here is the demo
SQL FIDDLE
inner procedure also has transaction and that is doing all previous DML COMMIT.
If you move your Insert after the CALL in outer procedure you will there is no insert.
but it may not always possible to change order. somehow we have to avoid the COMMIT from any nested procedure call and do only in the parent procedure.
but sometime those nested procedures can be called independently and you may want to keep Transactions for those calls. so basically what I am doing is adding Transaction/Rollback/Commit statement based on the parameter passed to the procedure.
when making a call directly from APP Code to procedure always pass 1 and hence the procedure will be use explicit Transaction. but when you making nested call in parent procedure you do not want to use transaction for nested calls so explicitly pass 0.
CREATE TABLE `a` (
`b` TINYINT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`b`)
);
CREATE TABLE `b` (
`id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`f` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);
CREATE PROCEDURE `s`
(
IN `f` TINYINT,
IN `NestedTran` TINYINT,
OUT `z` TINYINT
)
BEGIN
DECLARE exit HANDLER
FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET z = -1;
IF NestedTran = 1 THEN
ROLLBACK;
END IF;
END;
IF NestedTran = 1 THEN
START TRANSACTION;
END IF;
INSERT INTO a
VALUES( f );
SET z = f;
IF NestedTran = 1 THEN
COMMIT;
END IF;
END;
CREATE PROCEDURE `t`
(
IN `r` TINYINT,
IN `NestedTran` TINYINT,
OUT `l` TINYINT,
OUT `z` TINYINT
)
BEGIN
DECLARE exit HANDLER
FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l = -1;
IF NestedTran = 1 THEN
ROLLBACK;
END IF;
END;
IF NestedTran = 1 THEN
START TRANSACTION;
END IF;
INSERT INTO b (f)
VALUES( r );
SET l = LAST_INSERT_ID();
CALL s( r,0, z );
IF z = -1 THEN
IF NestedTran = 1 THEN
ROLLBACK;
END IF;
ELSE
IF NestedTran = 1 THEN
COMMIT;
END IF;
END IF;
END/
Best Answer
yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).