Procedure for determining if any of the database tables are locked in Microsoft SQL

Every so often, operations you perform can cause databases to have locking or blocking problems or an environment issue can cause a database table locking to occur, preventing other users from accessing or writing data to the table. Locking and blocking are unavoidable. They occur in every relational database management system (RDBMS), and SQL Server is no exception.

Blocking occurs when a single logged-on database session, or server process ID (SPID), holds a lock on a specific resource, and a second SPID tries to acquire a conflicting lock on that same resource. Typically, resource locks don’t last long. When the first user releases the lock, the second server SPID can lock the resource to continue processing a query.

SQL Server uses a granular approach to locking. It will select the appropriate locking level based on the number of records affected and other ongoing activities. By default, locks are escalated from smaller row-level and page-level locks, to larger table-level locks as needed to improve performance. While escalation is generally a good thing, it also can cause problems, for example when a Service Set Identifier (SSID) locks an entire table and prevents other SSIDs from working with that table.

If you run into an issue where you think a table or tables are locked, you can run the following script to be certain. We will be using Accpac Sample Company (SAM561) to demonstrate.

1. Open Microsoft SQL Server Management Studio and click on the New Query button.

2. In the Query window, enter the following script:
Use SAMI56
go
--to list of tables which are being locked at the moment
select
object_name(resource_associated_entity_id) as 'TableName' ,*
from
sys.dm_tran_locks
where resource_type = 'OBJECT'
and resource_database_id = DB_ID()
GO

3. Click the Execute icon to run the script

4. Review the Results screen to see the tables currently locked.

In order to produce these results, start the Inventory Control Day-End Periodic Process.

If you know someone who could benefit from our blog, or from our other resources such as the Success Newsletter or our Tips & Tricks Videos, then please forward this article to them or refer them to our website at www.axisgp.com.