How to check which locks are held on a table
To add to the other responses, sp_lock
can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2
to quickly zero in on locking problems.
There are multiple different versions of "friendlier" sp_lock
procedures available online, depending on the version of SQL Server in question.
In your case, for SQL Server 2005, sp_lock
is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks
view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.
How to find out what is locking my tables?
Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):
- sp_who
- sp_lock
Also, in SSMS, you can view locks and processes in different ways:
Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.
Detecting locked tables (locked by LOCK TABLE)
SHOW OPEN TABLES
to show each table status and its lock.
For named locks see Show all current locks from get_lock
Find out number of active locks on a table
Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition
values.
USE yourdatabase;
GO
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');
Please look at the documentation for sys.dm_tran_locks
Find what rows are locked for a given table and who is locking them in SQL Server
sys.dm_tran_locks
, as already said in 694581. To identity which rows are actually locked, you need to understand the locking hierarchy (table->rowset->page->row) and you need to crack the lock resource description. For table locks is the object id from sys.objects
, for rowsets is the partition_id from sys.partitions
and for pages is the actual page id. For rows it depends whether is a heap or a btree, but you can use the (undocumented) %%lockres%%
virtual column to find the row. If this is too simple, you need to consider also range locks as they impact all the rows in the specified range.
When you add up the difficulty of navigating the physical hierarchy, specially when page locks are involved, with the complex model of the lock compatibility matrix, the complications added by hash collisions and consider the the pace at which the locks you're looking at change, I would say that at the very best you can do a very rough approximation. Besides doing a specific problem investigation, there is little point into digging into this. I would be horrified to hear of an application that looks actively at locks held and makes any kind of decision based on the information seen.
Related Topics
SQL Sum Data from Multiple Tables
Call a Set-Returning Function with an Array Argument Multiple Times
Differencebetween Function and Procedure in Pl/Sql
How to Delete Duplicates in MySQL Table
Using Tuples in SQL "In" Clause
What's the Difference Between "Where" Clause and "On" Clause When Table Left Join
Questions Every Good Database/SQL Developer Should Be Able to Answer
How to Query a Comma Separated Column for a Specific Value
Including Null Values in an Apache Spark Join
Concatenate Results from a SQL Query in Oracle
Postgresql: Encoding Problems on Windows When Using Psql Command Line Utility
Get the Default Values of Table Columns in Postgres
Should I Design a Table with a Primary Key of Varchar or Int