Table Level Lock

Question

Table Level Lock

M.Ashok

SSC Journeyman

Points: 84
More actions
March 10, 2006 at 9:41 pm

#111439

Hi,
In SQL Server 2000, when will table level lock occur?
How to solve this problem?
Pls give more descriptions...

Thanks in advance..
Ashok

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

A table level lock is a natural event and is part of the SQL Server locking architecture. There are many articles on this site devoted to locking, so I'll just give you a high level view. When a DML statement/batch is run against the database, SQL Server must determine which level of locking is most appropriate and most efficient to process that statement/batch. SQL Server will start off with the lowest level lock and work it's way up as needed. For example, if you are updating one row, SQL Server should put a row lock on that row, update the row, then release the lock. When working with sets or groups of rows, SQL Server may determine that a Page, Extent, Table, or Database lock would be more efficient and less resource intensive than placing row locks on each row in the set/group.

With this said, Table locks are only natural and you should expect to see them. If table locks are causing you blocking or deadlocking problems, look to your SQL code and/or your indexes. You may have too much going on in a transaction that is causing SQL Server to keep the Table lock for an extended period of time. Again, there is ample information on this site to help tune transactions, indexes, SQL code, databases, etc. Good luck...

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden