Skip to content

Instantly share code, notes, and snippets.

@cham11ng
Last active April 27, 2020 08:50
Show Gist options
  • Save cham11ng/99d3cf6faa2c74b089947c98b3738ae7 to your computer and use it in GitHub Desktop.
Save cham11ng/99d3cf6faa2c74b089947c98b3738ae7 to your computer and use it in GitHub Desktop.
SQL Locks Research

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:

Random Data Generator

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;
UPDATE TOP 10 employees SET gender = 'F' WHERE gender = 'M';
UPDATE TOP 10 employees SET gender = 'M' WHERE gender = 'F';
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment