SQL Server - Check to see if a table is locked

How to check to table locking in SQL Server 2008 R2.

We used to use SP_LOCK and SP_LOCK2, but have since learned that the recommend method to determine locks is the code below as the two procs are to be deprecated in future releases.

Microsoft says the better way is to use the sys.dm_tran_locks dynamic management views.



The following code will select from the sys.dm_tran_locks and sys.partitions system tables.

More information on Locks can be found at the following Microsoft library pages:

Lock Modes

Locking Granularity

Key Range Locking 

 

---------------------------------------------------------------

--  Code to find out what table is locked and the lock reason

---------------------------------------------------------------

select 
    object_name(P.object_id) as TableName
    , resource_type
    , resource_description
    , request_mode
    , CASE REQUEST_MODE
                    WHEN 'S'        THEN 'Shared'
                    WHEN 'U'        THEN 'Update'
                    WHEN 'X'        THEN 'Exclusive'
                    WHEN 'IS'       THEN 'Intent Shared'
                    WHEN 'IU'       THEN 'Intent Update'
                    WHEN 'IX'       THEN 'Intent Exclusive'
                    WHEN 'SIU'      THEN 'Shared Intent Update'
                    WHEN 'SIX'      THEN 'Shared Intent Exclusive'
                    WHEN 'UIX'      THEN 'Update Intent Exclusive'
                    WHEN 'BU'       THEN 'Bulk Update'
                    WHEN 'RangeS_S' THEN 'Shared Range S'
                    WHEN 'RangeS_U' THEN 'Shared Range U'
                    WHEN 'RangeI_N' THEN 'Insert Range'
                    WHEN 'RangeI_S' THEN 'Insert Range S'
                    WHEN 'RangeI_U' THEN 'Insert Range U'
                    WHEN 'RangeI_X' THEN 'Insert Range X'
                    WHEN 'RangeX_S' THEN 'Exclusive range S'
                    WHEN 'RangeX_U' THEN 'Exclusive range U'
                    WHEN 'RangeX_X' THEN 'Exclusive range X'
                    WHEN 'SCH-M'    THEN 'Schema-Modification'
                    WHEN 'SCH-S'    THEN 'Schema-Stability'

        ELSE NULL
        END AS REQUEST_LOCK_MODE

FROM   sys.dm_tran_locks   AS L
       join sys.partitions AS P
        on L.resource_associated_entity_id = p.hobt_id

SQL Server: how to delete a table in chunks

 
I was asked the other day how someone should delete all of rows in a very large table when they couldn't use the TRUNCATE function because there were foreign keys tied to that table.

Deleting all the tables in a single step could cause a large transaction and cause lots of locks.

I found the code below a while back on a better way to minimize the maximum locks and resources on the server.  Deleting in smaller chunks creates a bunch of small transactions that is more manageable for the server to handle.

In addition, if you happen to have an error on one of the chunks, all the deletes up to then are retained and not rolled back, only that one chunk is rolled back.   Then again, if you have an error, all prior deletes are not recoverable.


For this example I'll create a table, populate it with a small set of tables, then the delete command is what we actually use to delete it in chunks.

--- Create table to hold data
CREATE TABLE #Cars (id int)

--- Add a number of Rows to table
DECLARE @x INT
SET @x = 1
WHILE(@x < 10000)
BEGIN
    INSERT INTO #Cars Select round((rand()*100),0)
    SET @x = @x + 1
END

   
--- Delete all the rows in chunks of 5000

--- If the number of ROWS processed in the DELETE statement are not Zero 

--the repeat the delete statement

DeleteThem:
    DELETE TOP(5000) FROM #Cars

if @@RowCount != 0
goto DeleteThem