1

I have some sql script files where i am making some DDL changes as part of commit block

BEGIN TRANSACTION

-- CREATE/ALTER TABLE, COLUMNS, CONTRAINTS etc etc
COMMIT

Sometime when script fail, i still see the changes in DDL applied in database although this whole thing is in transaction block. What am I missing here?

imak
  • 6,489
  • 7
  • 50
  • 73

3 Answers3

3

Just because a particular statement causes an error, that doesn't mean that other statements won't also execute. Look at the documentation for XACT_ABORT:

In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed

If you want to rollback a transaction when an error occurs, you need to enclose your code in a TRY...CATCH block (or older style, check @@ERROR after ever statement, and goto a label where a ROLLBACK will occur).

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Looking at this link for XACT_ABORT, seem like this only applies to changes for insert, update and delete. Do you think this could be used in case of CREATE, and ALTER too?? – imak Jan 18 '11 at 15:58
1

@MartinSmith is correct when he says that this is not the case with SQL Server.MSDN states:"... You can use all Transact-SQL statements in an explicit transaction, except for the following statements: ALTER DATABASE CREATE FULLTEXT INDEX ALTER FULLTEXT CATALOG DROP DATABASE ALTER FULLTEXT INDEX DROP FULLTEXT CATALOG BACKUP DROP FULLTEXT INDEX CREATE DATABASE RECONFIGURE CREATE FULLTEXT CATALOG RESTORE "

Ankit Shukla
  • 11
  • 1
-5

DDL statment internally uses commit. DDL statment can not be rollback using rollback command.

Pankaj Agarwal
  • 11,191
  • 12
  • 43
  • 59
  • Sadly, most database systems' transactions don't affect DDL. It's a pain for migrations between DB versions, etc., but that's just the way it is. – Matt Gibson Jan 18 '11 at 15:51
  • Don't know about other DBMS's. This is not true in SQL Server apart from a very few statements. (e.g. `CREATE DATABASE`) – Martin Smith Jan 18 '11 at 16:00
  • Don't need to. I know this to be the case. – Martin Smith Jan 18 '11 at 16:03
  • @Martin Well, to put my mind at rest, could you cite a reference for this behaviour? Assuming you mean what I think you mean, which is that SQL Server rolls back DDL statements on ROLLBACK? My Google-foo is failing me; all the documentation I can find specifically mentions stuff like "data modifications", and doesn't say much either way about DDL... – Matt Gibson Jan 18 '11 at 16:14
  • @Matt - Sorry I'm not going to bother trawling the internet to find a reference for this. Just try it! `BEGIN TRANSACTION CREATE TABLE X(C INT) ROLLBACK` Do you have a table called X at the end? – Martin Smith Jan 18 '11 at 16:15
  • 1
    @Martin I did try it (before you suggested that) and I believe you, I'm just curious that such a nice feature compared with other DB systems doesn't seem to be specifically mentioned anywhere in the usual places... – Matt Gibson Jan 18 '11 at 16:17
  • @Matt - Ah OK I didn't realise this was not a common behaviour. Googling the error message you get when trying to create a DB in a transaction (Msg 226: `CREATE DATABASE statement not allowed within multi-statement transaction.`) indicates that it might not always have been that way back in the sybase days. Not sure when it would have been introduced. Before my time though! – Martin Smith Jan 18 '11 at 16:25
  • @Matt, @Martin i am a bit lost, how will you write the following script, because after running this i still get Test3 although i would like to rollback when script fail on dropping Test2 BEGIN TRANSACTION CREATE TABLE Test1 ( X INT ) DROP TABLE Test2 CREATE TABLE Test3 ( X INT ) COMMIT – imak Jan 18 '11 at 16:31
  • `SET XACT_ABORT ON BEGIN TRANSACTION BEGIN TRY CREATE TABLE Test1 ( X INT ) DROP TABLE Test2 CREATE TABLE Test3 ( X INT ) END TRY BEGIN CATCH PRINT 'ROLLBACK' ROLLBACK RETURN END CATCH COMMIT` – Martin Smith Jan 18 '11 at 16:35
  • @Martin, Thanks for providing example, its big help. Please correct me if i am wrong but does this mean that XACT_ABORT is OFF by default? Will it be a good thing to SET XACT_ABORT OFF after the COMMIT or will it be unnecessary? – imak Jan 18 '11 at 16:42
  • It is off by default. Opinion varies as to whether you should use it at all if you are using try...catch (e.g. see answer and comments here http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure/1150350#1150350). I don't think you would need to reset it explicitly. – Martin Smith Jan 18 '11 at 16:52
  • so I exclude SET XACT_ABORT ON from the example you provided and can still get the same result. Just wondering why you included it in there at all. Is there any advantage of having it? – imak Jan 18 '11 at 16:59
  • @imak - To ensure the transaction gets rolled back in the event of an error not caught by the `try` block (timeout error) – Martin Smith Jan 18 '11 at 17:02
  • @Matt - You might find this answer of interest http://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql/4736346#4736346 – Martin Smith Jan 19 '11 at 14:30
  • @imak - I have no idea why you accepted this answer as it is completely wrong! – Martin Smith Jan 19 '11 at 14:31
  • @Martin Thanks, yes, didn't know it had come along in Oracle. It looks like everyone's gradually realising it's a helpful idea :) – Matt Gibson Jan 19 '11 at 14:59