In Sql Server, is there a way to check if a selected group of rows are locked or not?

We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended periods of time (as it is waiting to get locks on all the rows or page lock or table lock) resulting in timeouts or taking multiple days to complete the task.

So, we are changing the approach to delete small batch of rows at at time. But we want to check if the selected (let's say 100 or 1000 or 2000 rows) are currently locked by a different process or not.

• If not, then proceed with delete/update.

• If they are locked, then move on to the next group of records.

• At end, come back to the begining and attempt to update/delete the left out ones.

Is this doable?

Thanks,
ToC

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then return to any batches that were not previously deleted and retry our original delete logic. We must repeat this cycle until all required batches of rows are deleted.

As has been mentioned, it is reasonable to use a READPAST hint and the READ COMMITTED (default) isolation level, in order to skip past ranges that may contain blocked rows. I will go a step further and recommend using the SERIALIZABLE isolation level and nibbling deletes.

SQL Server uses Key-Range locks to protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level...find more here:
https://technet.microsoft.com/en-US/library/ms191272(v=SQL.105).aspx

With nibbling deletes, our goal is to isolate a range of rows and ensure that no changes will occur to those rows while we are deleting them, that is to say, we do not want phantom reads or insertions. The serializable isolation level is meant to solve this problem.

Before I demonstrate my solution, I would like to add that neither am I recommending switching your database's default isolation level to SERIALIZABLE nor am I recommending that my solution is the best. I merely wish to present it and see where we can go from here.

A few house-keeping notes:

• The SQL Server version that I am using is Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

• My test database is using the FULL recovery model

To begin my experiment, I will set up a test database, a sample table, and I will fill the table with 2,000,000 rows.

USE [master];
GO

SET NOCOUNT ON;

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
    ALTER DATABASE [test] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [test];
END
GO

-- Create the test database
CREATE DATABASE [test];
GO

-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;

-- Create a FULL database backup
-- in order to ensure we are in fact using 
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO

USE [test];
GO

-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
      c1 BIGINT IDENTITY (1,1) NOT NULL
    , c2 INT NOT NULL
) ON [PRIMARY];
GO

-- Insert 2,000,000 rows 
INSERT INTO dbo.tbl
    SELECT TOP 2000
        number
    FROM
        master..spt_values
    ORDER BY 
        number
GO 1000

At this point, we will need one or more indexes upon which the locking mechanisms of the SERIALIZABLE isolation level can act.

-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
    ON dbo.tbl (c1);
GO

-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2 
    ON dbo.tbl (c2);
GO

Now, let us check to see that our 2,000,000 rows were created

SELECT
    COUNT(*)
FROM
    tbl;

So, we have our database, table, indexes, and rows. So, let us set up the experiment for nibbling deletes. First, we must decide how best to create a typical nibbling delete mechanism.

DECLARE
      @BatchSize        INT    = 100
    , @LowestValue      BIGINT = 20000
    , @HighestValue     BIGINT = 20010
    , @DeletedRowsCount BIGINT = 0
    , @RowCount         BIGINT = 1;

SET NOCOUNT ON;
GO

WHILE  @DeletedRowsCount <  ( @HighestValue - @LowestValue ) 
BEGIN

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION

        DELETE 
        FROM
            dbo.tbl 
        WHERE
            c1 IN ( 
                    SELECT TOP (@BatchSize)
                        c1
                    FROM
                        dbo.tbl 
                    WHERE 
                        c1 BETWEEN @LowestValue AND @HighestValue
                    ORDER BY 
                        c1
                  );

        SET @RowCount = ROWCOUNT_BIG();

    COMMIT TRANSACTION;

    SET @DeletedRowsCount += @RowCount;
    WAITFOR DELAY '000:00:00.025';
    CHECKPOINT;

END;

As you can see, I placed the explicit transaction inside the while loop. If you would like to limit log flushes, then feel free to place it outside the loop. Furthermore, since we are in the FULL recovery model, you may wish to create transaction log backups more often while running your nibbling delete operations, in order to ensure that your transaction log can be prevented from growing outrageously.

So, I have a couple goals with this setup. First, I want my key-range locks; so, I try to keep the batches as small as possible. I also do not want to impact negatively the concurrency on my "gigantic" table; so, I want to take my locks and leave them as fast as I can

StackExchange Database Administrators Q#102160, answer score: 11