June 3, 2011 at 2:04 pm
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
June 3, 2011 at 2:11 pm
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
June 3, 2011 at 2:32 pm
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
June 3, 2011 at 2:37 pm
Yes, there is no where clause because there is only one record in the table.
Mike
June 3, 2011 at 4:34 pm
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]
June 4, 2011 at 5:44 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply