Newbie issue with Transactions

  • Hi all...

    If I debug/step through the following TSQL:

    BEGIN Transaction

    update company set name = 'Mike'

    Rollback transaction

    When I get the the 3rd line (Rollback...), I have another user try the following:

    SELECT * FROM Company

    The other user is hung until I finish my 3 lines. What setting could change that?

    Mike

  • I think you might want to do some reading on locks and transactional consistency. What you're describing there is desired behaviour. You don't want users to see transactionally consistent (dirty) data.

    Let's try this example

    Begin transaction

    update Accounts set amount = amount-10000 where accountholderID = 24

    update Accounts set amount = amount+10000 where accountholderID = 67

    rollback

    Would you really want other users to see the half-done and later rolled back account information?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster is absolutely right, and I wanted to point out something else which I hope was just for demonstration. Your update statement has no WHERE clause, so it will update EVERY row in the table.

    CEWII

  • Yes, there is no where clause because there is only one record in the table.

    Mike

  • I have tried playing with Begin Transaction / Commit Transaction / Rollback Transaction logic, but I've never been able to get the syntax down correctly. Other than MSDN is there a source that can go over this a little better to assist in beginning understanding of this logic? As you can see, my self education has left some rather basic holes that bite me more often than I like.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • Please post new questions in a new thread. Thanks

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply