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

    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?

    Thanx

    James

  2. #2
    Join Date
    Oct 2003
    Location
    Manchester UK
    Posts
    73

    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

    @@ERROR

    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

    regards

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

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

    James

Posting Permissions

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