My developer ask me, inside one store procedure, we have 3 statements, if second statement fails, will first statement rollback? Will third statement continuous execute? What if we use begin tran.. commit?
Example
Create procedure tesme
As
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
If second insert fail, will first insert data rollback? Will sql
contineous execute sql statement ?
use tempdb
go
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
create procedure testme
as
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
exec testme
go
Result :
(1 row(s) affected)
Msg 547, Level 16, State 0, Procedure testme, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__32C16125". The conflict occurred in database "tempdb", table "dbo.t1", column 'a'.
The statement has been terminated.
(1 row(s) affected)
select * from t2
a
1
3
By Default, first insert didn't rollback and statement 3 been processes, even second insert fail
Check this :
SET XACT_ABORT { ON | OFF }
OFF is the default setting.
http://msdn.microsoft.com/en-us/library/ms188792.aspx
Explanation from mdsn
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.
OFF is the default setting.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
From the document, if we didn't define xact_abort in store procedure, the value will for xact_abort = off
By default xact_abort is off,
how to check if xact_abort is on or off in current session
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (16384
&@options) = 16384 ) PRINT 'XACT_ABORT'
set xact_abort on
go
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
--reset to default
set xact_abort off
Enabled set xact_abort on inside store procedure
--clear data
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
exec testme
go
select * from t2 -- return no result
in this example, We enable set xact_abort on in the store procedure
Second statement fails with "The INSERT statement conflicted with the FOREIGN KEY constraint"
when set xact_abort on define in SQL store procedure, if second sql statement fail,
first sql statement will be rollback and third sql statement won't be run(set xact_abort on)
Two transaction in one store procedure
If Second transaction fails, will first transaction rollback
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
BEGIN TRANSACTION
INSERT INTO t2 VALUES (6);
INSERT INTO t2 VALUES (8);--this will fail
COMMIT TRANSACTION;
GO
exec testme
go
error :
Msg 547, Level 16, State 0, Procedure testme, Line 9
Msg 547, Level 16, State 0, Procedure testme, Line 10
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__4D404D37". The conflict occurred in database "tempdb", table "dbo.t1", column 'a'.
select * from t2
first batch of query been execute and won't rollback
a
1
3
value 6 didn't add into table
Two transaction in one store procedure, If Second transaction fails,only second transaction will be rollback(set xact_abort on)
If First transaction in store procedure fail, will second transaction contienous?
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION
INSERT INTO t2 VALUES (6);
INSERT INTO t2 VALUES (8);--this will fail
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
exec testme
go
select * from t2 -- no result return
if first transaction fails, second transaction won't be continuous(set xact_abort on)
If second calling store procedure, will sql rollback dml from first calling store procedure?
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
exec testmecalling
exec testmecalling2 -- this will fail
COMMIT TRANSACTION;
GO
create procedure testmecalling
as
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
create procedure testmecalling2
as
INSERT INTO t2 VALUES (6);
INSERT INTO t2 VALUES (8);
exec testme
go
select * from t2 --no result return
If calling
store procedure inside tran..commit, one fail will rollback other (set xact_abort on)
