Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: BEGIN TRANSACTION COMMIT TRANSACTION help

    I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

    For instance, run these statements to set up a test:
    --DROP TABLE testTable1
    --DROP TABLE testTable2
    CREATE TABLE testTable1 (f1 varchar(1))
    CREATE TABLE testTable2 (f1 varchar(1))
    CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
    insert into testTable1(f1) values ('a')

    So table testTable1 has a unique index on it..

    Now try to run these statements:

    --DELETE FROM testTable2
    BEGIN TRANSACTION
    insert into testTable1(f1) values ('a')
    insert into testTable2(f1) values ('a')
    COMMIT TRANSACTION

    SELECT * FROM testTable2


    ..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...

    So, I have modified the test as follows and it works:

    DECLARE @errorHolder int
    SELECT @errorHolder = 0
    --DELETE FROM testTable2
    BEGIN TRANSACTION aaa
    insert into testTable1(f1) values ('a')
    SELECT @errorHolder = @errorHolder + @@ERROR
    insert into testTable2(f1) values ('a')
    SELECT @errorHolder = @errorHolder + @@ERROR
    IF @errorHolder > 0
    ROLLBACK TRANSACTION
    ELSE
    COMMIT TRANSACTION
    SELECT * FROM testTable2


    Is there an easier way

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    Use
    Code:
    SET XACT_ABORT ON
    to automatically roll back a transaction if a run-time error is raised in the TSQL statements.

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    thanks kaffenils, that's much easier.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I prefer to try and catch my errors withg error handling and then I have control over my logic

    BUT DDL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by Brett Kaiser
    I prefer to try and catch my errors withg error handling and then I have control over my logic
    I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @@ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Time consuming? That's your justification?!
    Oh dear...
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by georgev
    Time consuming? That's your justification?!
    Oh dear...
    Did you even read what I wrote? Have I said not to use TRY...CATCH or @@ERROR if you need more control of exceptions?
    "Oh Dear"

    All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
    Code:
    IF @@ERROR<>0 BEGIN ... END or [GOTO abc
    On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •