SQL Server Performance

view lock tables

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Sep 23, 2004.

  1. v1rtu0s1ty New Member

    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
  2. Twan New Member

    Hi ya,

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

    Cheers
    Twan
  3. Luis Martin Moderator

    May be I confused, but using EM, Managment, Current Activity, locks/objects you can see what tables are locked.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. chopeen Member

    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?
  5. v1rtu0s1ty New Member

    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
  6. v1rtu0s1ty New Member

    oops, it should have been

    select * from tempo with (NOLOCK)
  7. chopeen Member

    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.
  8. v1rtu0s1ty New Member

    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.
  9. chopeen Member

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

    quote:Originally posted by v1rtu0s1ty

    oops, it should have been

Share This Page