Duration of shared locks held

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hassan - 28 Dec 2007 19:09 GMT

Say if i have a join on 2 tables : Table1 with 1 row and Table2 with 1
million rows and if I dont have an efficient index and my query has to scan
every page in Table2 hence acquiring shared locks on that table, the
question I have is , for the duration that it reads through every page on
Table2 in the join, will it also hold a shared lock on Table1 that may
possibly have only one page ?

Thanks

Reply to this Message

Mohit K. Gupta - 28 Dec 2007 19:25 GMT

Yes, because it has to read information from both tables. If you are 100%
sure the data can be read without have affect on the dirty reads you can use
(nolock) hint to speed up the processes.

When you are doing massive selects like that it will lock tables, and any
query that is being blocked will stay on hold until all locks are released
from the resource.

Signature

Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

> Say if i have a join on 2 tables : Table1 with 1 row and Table2 with 1
> million rows and if I dont have an efficient index and my query has to scan
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

Dan Guzman - 29 Dec 2007 18:58 GMT

During a scan operation in the READ COMMITTED isolation level, locks are
acquired during the scan (page, key) and are released as soon as no longer
needed. An intent shared lock is acquired at the table level to indicate
that a lower level shared lock exists.

You can run a Profiler trace of a particular query including lock
acquired/released to see the actual behavior.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Say if i have a join on 2 tables : Table1 with 1 row and Table2 with 1
> million rows and if I dont have an efficient index and my query has to
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message