view lock tables

Hey guys,

I read the other post about Lock tables. I went to the link and read it.
I saw the NOLOCK feature os MSSQL. What I have noticed is that, I can see the new data that was updated even if commit tran hasn't been executed. Is there a way that even if the table is still lock, I can still see the old data?


Thanks.

v1rt

Hi ya,

no, not until SQL2005, which has a snapshot isolation mode

Cheers
Twan

OK, I am confused.

What are we talking about here?

Seeing old data before committing a transaction?
OR
Seeing new data before committing a transaction?
OR
Seeing which tables are locked?

Here is what I am talking about. I have two query analyzer running. I did this purposely to learn how locking occur since I am new in this field.

in QA1, i ran

begin tran
update tempo set name='test'

in QA2, after running the query in QA1, I ran

select * from tempo

I can see the new data in column name which is test for name column. I guess Twan answered it correctly. I was hoping that if the query in QA1 was already executed, QA2 will still see the old data. But according to him, it's gonna happen in 2005.

Thanks.

V1rt

oops, it should have been

select * from tempo with (NOLOCK)

quote:Originally posted by v1rtu0s1ty

in QA1, i ran

begin tran

update tempo set name='test'

in QA2, after running the query in QA1, I ran

select * from tempo

I can see the new data in column name which is test for name column. I guess Twan answered it correctly. I was hoping that if the query in QA1 was already executed, QA2 will still see the old data.

I tested this and it didn't work exactly like this.

When I used SELECT with NOLOCK, QA showed me the new data.
However, when I used SELECT without NOLOCK, the query did not execute until I committed the transaction.

That is exactly what I meant. If executed with NOLOCK, we will see the new data. Please refer back to my first sentence above. But if SELECT is executed without the NOLOCK, our process will be waiting until the other query commits it.

OK, now I see what you've meant here:

quote:Originally posted by v1rtu0s1ty

oops, it should have been