Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003

    Unanswered: Atomic Transaction

    Hi all,

    If I have 2 database operations
    1. Update ....
    2. Delete ....

    I want these 2 operations to execute atomically, thus I make use of

    BEGIN Transaction ...
    Update ....
    Delete ....
    COMMIT Transaction ...

    But if one of these operation fails, it locks up the database. Does anyone have a resolution for this? How do I detect sql Exceptions to release the transaction so that it doesn't lock up the database?



  2. #2
    Join Date
    Oct 2003
    Manchester UK

    Is this a stored Proc?

    I assume this is written in a stored procedure.

    In that case

    After your first UPDATE you need to chek


    If its Zero continue otherwise you need to ROLLBACK the transaction as the update failed. The same is true for your Delete statement. You should only COMMIT when both succeed. You should report or log the error somehow of course.

    Have a look in Books Online under the topic

    Using @@ERROR

    its under Accessing and Changing relational Data :: Advanced Query Concepts :: Error Handling


    I haven't lost my mind, there's a backup on one of these floppies, somewhere.

  3. #3
    Join Date
    Aug 2003
    Thanx, I will give that a try.
    I love dbforums, people here's always got the answer.


Posting Permissions

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