Need help on getting tables lock in SQL server 7.0

Need help on getting tables lock in SQL server 7.0

Post by Amit Wali » Wed, 15 Sep 1999 04:00:00



Hello,
          I have SQL server 7.0 and my tables are getting locked and I don't
see any configuration setting for Rowlevelocking or any other method to take
care of this issue.

Help is appreciated Please get back to me. Thanks

Amit Walia

703.749.2892 ext. 230

 
 
 

Need help on getting tables lock in SQL server 7.0

Post by Dave Fackle » Wed, 15 Sep 1999 04:00:00


Amit,

SQL Server 7.0 uses dynamic locking.  Thus the Query Optimizer determines
the best locking level based on the query involved.  You can tell the
Optimizer what type of locking you want for an individual query by using
optimizer hints via the "with" option.

Thus:

    update MyTable with (rowlock)
        set MyCol1 = MyCol1 * 2

would cause row-level locking on the update.  If a large number of records
are involved, the Query Optimizer might chose page level or table level
locking unless you specify exactly what you want.  Be warned though that
specifying a specific locking level (like the example above) might result in
a LOT of locking overhead for a query.

Look up "locking hints" in Books On-Line.

Dave Fackler