May 11, 2009 at 5:22 pm
In SS2000, I could do a simple example of locking by making a temp table in one query session and inserting a row in a transaction without committing. Opening a second query window would select from the temp table and show no results until the transaction in the other window was committed. In 2008, querying the temp table from another query window sits at the 'query executing....' indefinitely while the transaction is open in the other window.
Why?
I was trying to illustrate transactions for some students and this used to be a really simple way to do it. What do I need to do differently with 2008? I assume this is something to do with 2008's treatment of locks. Selecting in the second window with (nolock) shows the uncommitted row. But what I want is to return the result without the uncommitted row in the second window.
I'm using Express, does that make a difference?
Thanks for any help; sorry if this is a dumb question. I checked BOL in both the transactions and locks categories but didn't see anything that helped.
May 12, 2009 at 1:18 pm
It would be a good idea to check if Snapshot Isolation is set to READ COMMITTED ON or OFF. Maybe in SQL 2005 set up you had, you might have had ISOLATION set as READ COMMITTED. Then it will show the previously committed value in the Second window (SPID). Just guessing here.
-Roy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply