-- Code and scripts from Chapter 6: 
--    Concurrency Problems


-- You can detect table locks at a given point in time using the 
-- sys.dm_tran_locks DMV. The following query shows an example:

SELECT
  request_session_id,
  resource_type,
  DB_NAME(resource_database_id) AS DatabaseName,
  OBJECT_NAME(resource_associated_entity_id) AS TableName,
  request_mode,
  request_type,
  request_status
FROM sys.dm_tran_locks AS TL
  JOIN sys.all_objects AS AO
    ON TL.resource_associated_entity_id = AO.object_id
WHERE request_type = 'LOCK'
  AND request_status = 'GRANT'
  AND request_mode IN ('X','S')
  AND AO.type = 'U'
  AND resource_type = 'OBJECT'
  AND TL.resource_database_id = DB_ID();




-- For example, the following code will prevent lock escalation 
-- on the table for one hour.

BEGIN TRAN
SELECT *
FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)
WHERE 1=0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN




-- For example, the following query shows just those waits that 
-- have been occurring for more than 5 seconds:

SELECT
  WT.session_id AS waiting_session_id,
  WT.waiting_task_address,
  WT.wait_duration_ms,
  WT.wait_type,
  WT.blocking_session_id,
  WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
WHERE WT.wait_duration_ms > 5000;





-- The following query returns a subset of the columns and shows 
-- all those locks that are in a WAIT state:

SELECT
  TL.resource_type,
  DB_NAME(TL.resource_database_id) as DatabaseName,
  TL.resource_associated_entity_id,
  TL.request_session_id,
  TL.request_mode,
  TL.request_status
FROM sys.dm_tran_locks AS TL
WHERE TL.request_status = 'WAIT'
ORDER BY DatabaseName, TL.request_session_id ASC;




-- The sys.dm_tran_locks DMV uses both the 
-- resource_associated_entity_id along with the 
-- resource_description to identify the locked resource, so we 
-- just need to use that as the method for joining the view with 
-- itself to pick out each waiting and granted lock for each 
-- given resource. The following query shows how you can do it:

SELECT
  TL1.resource_type,
  DB_NAME(TL1.resource_database_id) AS DatabaseName,
  TL1.resource_associated_entity_id,
  TL1.request_session_id,
  TL1.request_mode,
  TL1.request_status
FROM sys.dm_tran_locks as TL1
  JOIN sys.dm_tran_locks as TL2
    ON TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
    AND TL1.request_status <> TL2.request_status
    AND (TL1.resource_description = TL2.resource_description
    OR (TL1.resource_description IS NULL 
        AND TL2.resource_description IS NULL))
ORDER BY TL1.request_status ASC;




-- You might want to also query the view and return the actual 
-- object represented by the resource_associated_entity_id, 
-- whether it be a RID, key, page or table.

SELECT
  TL1.resource_type,
  DB_NAME(TL1.resource_database_id) AS DatabaseName,
  CASE TL1.resource_type
    WHEN 'OBJECT' 
          THEN OBJECT_NAME(TL1.resource_associated_entity_id,
                           TL1.resource_database_id)
          WHEN 'DATABASE' THEN 'DATABASE'
    ELSE
       CASE
         WHEN TL1.resource_database_id = DB_ID() THEN
             (SELECT OBJECT_NAME
                  (object_id, TL1.resource_database_id)
              FROM sys.partitions
              WHERE hobt_id = TL1.resource_associated_entity_id)
         ELSE NULL
       END
  END AS ObjectName,
  TL1.resource_description,
  TL1.request_session_id,
  TL1.request_mode,
  TL1.request_status
FROM sys.dm_tran_locks AS TL1
  JOIN sys.dm_tran_locks AS TL2
    ON TL1.resource_associated_entity_id = 
       TL2.resource_associated_entity_id
WHERE TL1.request_status <> TL2.request_status
  AND (TL1.resource_description = TL2.resource_description
   OR (TL1.resource_description IS NULL
       AND TL2.resource_description IS NULL))
ORDER BY TL1.resource_database_id,
         TL1.resource_associated_entity_id,
         TL1.request_status ASC;





-- First you use the sp_configure command to set the advanced 
-- option blocked process threshold to a value, let's say 60 
-- seconds:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold', 60;
RECONFIGURE;






--You can join on the sys.dm_tran_locks. Here's an example:

SELECT
  WT.session_id AS waiting_session_id,
  DB_NAME(TL.resource_database_id) AS DatabaseName,
  WT.wait_duration_ms,
  WT.waiting_task_address,
  TL.request_mode,
  TL.resource_type,
  TL.resource_associated_entity_id,
  TL.resource_description AS lock_resource_description,
  WT.wait_type,
  WT.blocking_session_id,
  WT.resource_description AS blocking_resource_description
FROM sys.dm_os_waiting_tasks AS WT
  JOIN sys.dm_tran_locks AS TL
    ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
  AND WT.session_id > 50;




-- By adding a subquery that joins the sys.dm_exec_requests DMV 
-- and the sys.dm_exec_sql_text() DMF, correlating back to the 
-- joining on the waiting task's session_id, you can extract the 
-- query text. Here's an example, adapted from a similar query 
-- in A Troubleshooting Methodology, Chapter 1 of this volume.

SELECT
  WT.session_id AS waiting_session_id,
  DB_NAME(TL.resource_database_id) AS DatabaseName,
  WT.wait_duration_ms,
  WT.waiting_task_address,
  TL.request_mode,
  (SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
    ((CASE ER.statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE ER.statement_end_offset
      END - ER.statement_start_offset)/2) + 1)
   FROM sys.dm_exec_requests AS ER
     CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
   WHERE ER.session_id = TL.request_session_id)
          AS waiting_query_text,
  TL.resource_type,
  TL.resource_associated_entity_id,
  WT.wait_type,
  WT.blocking_session_id,
  WT.resource_description AS blocking_resource_description,
  CASE 
    WHEN WT.blocking_session_id > 0 THEN
      (SELECT ST2.text 
       FROM sys.sysprocesses AS SP
         CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
       WHERE SP.spid = WT.blocking_session_id)
    ELSE NULL
  END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS WT
  JOIN sys.dm_tran_locks AS TL
    ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
  AND WT.session_id > 50;




-- Here's a skeletal example of how you can rewrite your code to 
-- retry the transaction using Transact-SQL:

DECLARE @Tries tinyint, @Error int;
SET @Tries = 1;
WHILE @Tries <= 3
  BEGIN
    BEGIN TRANSACTION;
      BEGIN TRY
      -- <code goes here>
      IF XACT_STATE() = 1 COMMIT;
        BREAK;
      END TRY
      BEGIN CATCH
        SET @Error = ERROR_NUMBER();
        IF @Error = 1205
        BEGIN
          IF XACT_STATE() = -1 ROLLBACK;
        END
        SET @Tries = @Tries + 1;
        CONTINUE;
      END CATCH;
    END;




-- Assume you have two tables, a header table called H1 and a 
-- detail table called D1, and you have declared a foreign key 
-- from D1 to H1 referencing H1's primary key. Here's the code 
-- to create that scenario:

USE Scratch;
GO
ALTER DATABASE Scratch
  SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT OFF;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
GO
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
ALTER TABLE D1 ADD CONSTRAINT FK_D1ID_D1
FOREIGN KEY (H1ID) REFERENCES H1(H1ID);
GO
INSERT H1 VALUES (1,'Test');
GO





-- Now let's change the database to READ COMMITTED SNAPSHOT 
-- (sometimes abbreviated to RCSI):

ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT ON;

-- You'll have to make sure for a moment that you only have one 
-- connection to the database so that this change will take 
-- affect. You also need to reinsert the row into the H1 table.

INSERT H1 VALUES (1,'Test');




-- To reproduce this, alter the database to disable RCSI and 
-- enable SNAPSHOT isolation (momentarily taking all other 
-- connections out of the database):

ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch
  SET ALLOW_SNAPSHOT_ISOLATION ON;


-- Now reinsert the row into the H1 table one more time.

INSERT H1 VALUES (1,'Test');




-- For this example, you'll need to recreate the same header and 
-- detail tables but this time create a simple trigger that will 
-- ensure referential integrity for single-row INSERTs. 
-- (Using foreign key constraints is the recommended way to 
-- enforce referential integrity, but many databases still use 
-- triggers.)

USE Scratch;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
CREATE TRIGGER tr_D1_H1
  ON D1
  FOR INSERT
AS
BEGIN
-- Single-row inserts only
  IF (SELECT H1ID FROM inserted) NOT IN (SELECT H1ID FROM H1)
  BEGIN
    PRINT 'Rolling back insert'
    ROLLBACK
  END
END;
GO
INSERT H1 VALUES (1, 'Test')
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
GO




-- Remove all other users in the database and issue the following
--  commands, putting the database into the 
-- READ COMMITTED SNAPSHOT state:

ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT ON;
GO




-- To clean up, empty out the D1 detail table and reinsert the 
-- header row into H1:

DELETE FROM D1;
INSERT H1 VALUES (1,'Test');





-- For this example, remove all other users in the database, set 
-- READ COMMITTED SNAPSHOT to OFF in the database, set the 
-- database to allow SNAPSHOT isolation:

ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION ON;
GO




-- For example, you could rewrite the initial RI trigger as 
-- follows, using the READCOMMITTEDLOCK hint:

CREATE TRIGGER tr_D1_H1
  ON D1
  FOR INSERT
AS
BEGIN
  -- Single-row inserts only
  IF (SELECT H1ID FROM inserted) NOT IN
        (SELECT H1ID FROM H1 WITH (READCOMMITTEDLOCK))
  BEGIN
    PRINT 'Rolling back insert'
    ROLLBACK
  END
END;




-- In all the examples so far of lost updates, you can apply the 
-- UPDLOCK hint to the SELECT statement that reads the value 
-- from Sales.SalesOrderDetail:

SET @OrderQty =
 (SELECT OrderQty
  FROM Sales.SalesOrderDetail WITH (UPDLOCK)
  WHERE SalesOrderID = 43659
    AND SalesOrderDetailID = 1);



--Finally the UPDLOCK hint will also prevent the lost update 
-- shown in Table 6-12. Just add the UPDLOCK hint to the SELECT 
-- statement in step 2 as follows:

SELECT OrderQty
FROM Sales.SalesOrderDetail WITH (UPDLOCK)
WHERE SalesOrderID = 43659
  AND SalesOrderDetailID = 1;




-- Using the example from the transaction shown in Table 6-11,
-- you could rewrite the SNAPSHOT transaction as:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
WAITFOR TIME '00:15:00.0';

DECLARE @Tries tinyint, @Error int;
DECLARE @OrderQty smallint;
SET @Tries = 1;

WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
    BEGIN TRY
      SELECT OrderQty
      FROM Sales.SalesOrderDetail
      WHERE SalesOrderID = 43659
       AND SalesOrderDetailID = 1;
     WAITFOR DELAY '00:00:00.2';
     UPDATE Sales.SalesOrderDetail
       SET OrderQty = OrderQty + 2
     WHERE SalesOrderID = 43659
       AND SalesOrderDetailID = 1;
     IF XACT_STATE() = 1 COMMIT;
     BREAK;
   END TRY;
   BEGIN CATCH
     SET @Error = ERROR_NUMBER();
     IF @Error = 3960
       BEGIN
         PRINT '3960 encountered';
         IF XACT_STATE() = -1 ROLLBACK;
       END
     SET @Tries = @Tries + 1;
     CONTINUE
   END CATCH;
 END;




-- For monitoring Full SNAPSHOT isolation level transactions in 
-- particular, you can use sys.dm_tran_active_snapshot_database_transactions. 
-- The following query shows one example:

SELECT
  transaction_id,
  session_id,
  transaction_sequence_num,
  is_snapshot,
  max_version_chain_traversed,
  elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;




-- The following query shows one way of using this DMV to inspect
-- the free space in tempdb:

SELECT
  SUM(user_object_reserved_page_count) * 8.192 AS UserObjectsKB,
  SUM(internal_object_reserved_page_count) * 8.192 AS InternalObjectsKB,
  SUM(version_store_reserved_page_count) * 8.192 AS VersionStoreKB,
  SUM(unallocated_extent_page_count) * 8.192 AS FreeSpaceKB
FROM sys.dm_db_file_space_usage;