SQL Locks Research

locks.md

Some findings:

• Exclusive lock (X) This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock. (imposed by DML Statments)

DBMS products use a locking mechanism to control access and modifications while ensuring data integrity.

1. SQL Table hints

The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. The WITH (NOLOCK) table hint is used to override the default transaction isolation level

WITH (NOLOCK)read an uncommitted row that is subsequently rolled back. While it can prevent reads being deadlocked by other operations, it comes with a risk.

Useful Cases

Advantanges:

• the query will consume less memory in holding locks against that data

To avoid locking:

• Limit the number of rows accessed by coding predicates to filter unwanted rows. Doing so reduces the number of locks on pages containing rows that are accessed but not required, thereby reducing timeouts and deadlocks.

Reference:

• https://stackoverflow.com/questions/686724/what-is-with-nolock-in-sql-server
• https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/
• http://www.dbta.com/Columns/DBA-Corner/What-Can-You-Do-to-Avoid-Database-Locking-Problems-100926.aspx
• https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

Random Data Generator

• https://www.mockaroo.com/

procedure.sql

CREATE PROCEDURE dbo.make_male @batch INT AS
BEGIN
    UPDATE TOP (@batch) employees SET gender = 'M' WHERE gender = 'F';
END;


CREATE PROCEDURE dbo.make_female @batch INT AS
BEGIN
    UPDATE TOP (@batch) employees SET gender = 'F' WHERE gender = 'M';
END;

top.sql

UPDATE TOP 10 employees SET gender = 'F' WHERE gender = 'M';

UPDATE TOP 10 employees SET gender = 'M' WHERE gender = 'F';

top_batch.sql

-- Using CTE. Batch RUN
DECLARE @Batch INT = 100
DECLARE @RowCount INT = @Batch

WHILE @RowCount > 0
BEGIN
    WITH CTE AS (
        SELECT TOP (@Batch) * FROM employees
    )
    UPDATE cte SET gender = 'F' WHERE gender = 'M';
    SET @RowCount = @@ROWCOUNT;
    PRINT @RowCount
END

-- Batch Run without CTE
DECLARE @Batch INT = 100
DECLARE @RowCount INT = @Batch

WHILE (@RowCount > 0)
BEGIN
  UPDATE TOP (@Batch) e
    SET e.gender = 'F'
    FROM (SELECT * FROM employees WHERE gender = 'M') e;

  SET @RowCount = @@ROWCOUNT
  PRINT @RowCount
END