August 10, 2006 at 11:59 pm
Hi,
i need to know how to set and unset a RowLevel lock on a set of tables.
i have a select clause with a from and a where clause and i want only the records this select clause pulls out to be locked.
Tried seeing The Select Query but cudnt find any Concrete code which culd explain how to perfom Row Locking.
Kindly reply.
Regards,
viral
August 11, 2006 at 9:20 am
A SELECT statement will put a Shared lock on the data it is selecting. If you use the ROWLOCK hint, you will be instructing SQL Server to place an Intent Shared lock on the data. I think what you want here is to use the NOLOCk hint. This will prevent SQL Server from locking rows/pages/table during a SELECT. As long as you are OK with dirty reads, use this approach; otherwise, I would not mess with locking hints on a SELECT statement.
August 15, 2006 at 12:06 am
If you have the memory available, SQL Server defaults to row-level locks. If you are locking a significant number of rows on a page, it will escalate the lock into a page-level lock and release all the individual row-level locks on data on that page.
Lock too many pages, and it will escalate to a table lock.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply