September 22, 2016 at 3:33 am
Can SQL SSMS do an Auto Rollback of a transaction?
I had an SQL Update done under Begin tran block.
And then I didn't commit it, but wanted to check the table entries in the same session.
But that query had an error in it.
As a result of which i realised that the Update was rollbacked.
Bij.
September 22, 2016 at 3:55 am
No. SSMS will not automatically roll back transactions.
If the update had an error, the update as a whole would have failed, but your transaction is still open and still needs to be committed or rolled back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 4:35 am
Gila,
I reproduced the same effect on a test server, and yes it seems to auto rollback.
The Update doesn't have any errors and uses a #temp table.
But then a query to insert records in the same #temp table is re-run, which will give an Error (because it already exists), it will then "auto" rollback the previous uncommited Update trans.
Bij
September 22, 2016 at 4:45 am
SSMS will not automatically roll back transactions.
If you're explicitly turned the XACT_ABORT session setting on, SQL Server (the DB engine, not the client tool) automatically rolls your transaction back on error, otherwise errors do not roll transactions back.
CREATE TABLE #Temp (
SomeNumber INT UNIQUE,
SomeOtherString VARCHAR(20)
);
INSERT INTO #Temp (SomeNumber)
VALUES (1), (2), (3), (4), (5)
BEGIN TRANSACTION
UPDATE #Temp SET SomeOtherString = '0000' + CAST(SomeNumber AS CHAR(1));
INSERT INTO #Temp (SomeNumber)-- fails, duplicates
VALUES (1), (2), (3), (4), (5)
SELECT * FROM #Temp
SELECT @@TRANCOUNT
The insert fails, the query of the table shows that the update completed, @@Trancount shows that there's still a transaction open.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 5:33 am
Gail,
I can't tell if the XACT_ABORT is On or Off.
But the way to replicate auto rollback is this way:
select * into #temptable
from basetable
select * from #temptable
begin tran
update basetable
set somefield = t.someotherFieldValue
from basetable b, #temptable t
where......
After the above update if you re-run the Select statement (select * into #temptable),
It will fail, as it already exists.
but then if you comment out that bit and see the base table, the Update would not have worked.
And if you try to rollback tran now, it will complain there is no transaction.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."
I tried this again and again and it does seem to auto rollback.
Bij.
September 22, 2016 at 5:53 am
There are a small number of DDL-related errors (data definition) that abort transactions, a very small number. They're not documented, it's a trial and error to find them, they're exceptions to the rule. It could be that trying to create a table that exists is one of those, never run into it myself though I have seen a foreign key constraint-related error roll a transaction back. If it is, then it's specifically the failed create table that's causing a rollback. (and again, it's not SSMS doing it, it's a DB engine thing)
When you're dealing only with DML, transactions do not get automatically rolled back on user errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 1:07 pm
datsun (9/22/2016)
Gail,I can't tell if the XACT_ABORT is On or Off.
But the way to replicate auto rollback is this way:
This is probably not what your wanting but I don't like to leave (whether I am just reading or participating in) threads with open questions....;
With that said (typed) here is the answer to your question plus I added all of the other possibilities associated with finding out what the state of XACT_ABORT is.
The answer lies in performing a simple BITWISE AND operation against @@OPTIONS, unless you know all these values yourself and can do them in your head (I CANNOT), here is a script to find all of the possible options and whether they are ON or OFF.
Enjoy...
SET XACT_ABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_NULL_DFLT_OFF OFF
SET ANSI_NULL_DFLT_ON OFF
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER OFF
SET ARITHIGNORE OFF
SET ARITHABORT ON
SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET DISABLE_DEF_CNST_CHK OFF --Obsolete in 2008 and greater
DECLARE @OPTIONS INT
DECLARE @OPTRESULTS VARCHAR(MAX)
SET @OPTIONS=(SELECT @@OPTIONS)
PRINT '@@OPTIONS= '+ CAST(@OPTIONS AS VARCHAR(20))
IF (@OPTIONS & 16384)=16384
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'XACT_ABORT ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'XACT_ABORT OFF'
IF (@OPTIONS & 8192)=8192
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NUMERIC_ROUNDABORT ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NUMERIC_ROUNDABORT OFF'
IF (@OPTIONS & 4096)=4096
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CONCAT_NULL_YIELDS_NULL ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CONCAT_NULL_YIELDS_NULL OFF'
IF (@OPTIONS & 2048)=2048
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_OFF ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_OFF OFF'
IF (@OPTIONS & 1024)=1024
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_ON ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_ON OFF'
IF (@OPTIONS & 512)=512
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NOCOUNT ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NOCOUNT OFF'
IF (@OPTIONS & 256)=256
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'QUOTED_IDENTIFIER ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'QUOTED_IDENTIFIER OFF'
IF (@OPTIONS & 128)=128
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHIGNORE ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARTHIGNORE OFF'
IF (@OPTIONS & 64)=64
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHABORT ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHABORT OFF'
IF (@OPTIONS & 32)=32
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULLS ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULLS OFF'
IF (@OPTIONS & 16)=16
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_PADDING ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_PADDING OFF'
IF (@OPTIONS & 8)=8
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_WARNING ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_WARNING OFF'
IF (@OPTIONS & 4)=4
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CURSOR_CLOSE_ON_COMMIT ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CURSOR_CLOSE_ON_COMMIT OFF'
IF (@OPTIONS & 2)=2
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'IMPLICIT_TRANSACTIONS ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'IMPLICIT_TRANSACTIONS OFF'
IF (@OPTIONS & 1)=1
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'DISABLE_DEF_CNST_CHK ON'
ELSE
SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'DISABLE_DEF_CNST_CHK OFF'
PRINT @OPTRESULTS
EDIT***This is the wrong thread I wanted to post this in you did not specifically ask the question here but it does answer your comment. I apologize if this is too OT.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply