Post reply

3 General questions

  • March 5, 2004 at 7:39 am

    #84524

    Hi-

    I have a few general questions that perhaps a guru can answer.

    1) What the difference between the "Hints"(NOLOCK etc..) and the "SET" statements(Serializeable etc..)?

    2) Is there any reason to explicitly use a Transaction for a SELECT statement?  I use Transactions for Inserts, Updates, and Deletes, but for a Select ??

    3) When Joining to a non-Indexed View - what happens behind the scenes?  Is it a performance disaster?

    Thanks - B

  • March 6, 2004 at 6:30 am

    #497699

    1) NOLOCK is a locking hint you can use to avoid contention in waiting for exclusive locks to finish. NOLOCK will create a non-schema verified lock which means you could be getting dirty data. Your isolation levels can allow you to do a couple different things, BOL is probably the best tool to research these.

    2) NO, as you cannot ROLLBACK or COMMIT a select, no point that I know of to use a tran.

    3) It's like a select statement really, no cached plan but will still utilize existing indexes on underlying tables.

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • March 8, 2004 at 6:25 am

    #497765

    If you wanted to use a pessimistic locking technique and apply a lock to a row at the time you select it then you would need to do it within a transaction.

    For example:

    SELECT * FROM MyTable WITH (ROWLOCK, UPDLOCK)

    would need to be done within a transaction otherwise the lock would not be maintained.

    Regards

    John

  • March 9, 2004 at 9:54 am

    #498004

    What if the above statement is used inside a cursor like

    declare mycur cursor for

    SELECT * FROM MyTable WITH (ROWLOCK, UPDLOCK)

    Do we still need to include it in a transaction for the lock to be maintained?

    Thanks

     

    Prasad Bhogadi
    www.inforaise.com

  • March 9, 2004 at 1:25 pm

    #498044

    All the testing I have ever done on this tells me that the lock will not be maintained unless it is within a transaction however I have not done too much with cursors.

    In your cursor example I was not entirely sure what would happen so I have tried it out.

    To my surprise I find that the lock is maintained on the latest row that is fetched from the cursor without the need for any explicit transaction.

    Do you already know this or were you asking the question?

    Regards

    John

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

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