-- Code and scripts from Chapter 8: Locking and Concurrency


-- The process that has the exclusive lock on a row of the 
--    sales.SalesOrderHeader table also has an intent exclusive lock on 
--    the page containing the row and another intent exclusive lock on 
--    the table containing the row. We can see those locks by first 
--    running this code:

USE AdventureWorks;
BEGIN TRAN
UPDATE  Sales.SalesOrderHeader
SET ShipDate = ShipDate + 1
WHERE SalesOrderID = 43666;
GO

-- This statement should affect a single row. Because I have started a
--    transaction and not yet terminated it, any exclusive locks 
--    acquired are still held. I can look at those locks using the 
--    sys.dm_tran_locks view:

SELECT resource_type, resource_description, 
        resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id > 0;
GO



-- A lock with an owner of transaction must be acquired within a 
--    user-defined transaction, and it is automatically released at the 
--    end of the transaction without any need to call sp_releaseapplock.
--    A lock with an owner of session is released automatically only 
--    when the session disconnects.

-- Here’s an example. Let’s request the lock shown above and then look 
--    at the sys.dm_tran_locks view:

EXEC sp_getapplock 'ProcLock', 'Exclusive', 'session';
GO
SELECT resource_type, resource_description, 
       resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'APPLICATION';
GO

-- Because the object_name function applies only to the current 
--    database, this code is filtered to only return lock information 
--    for resources in the current database. The output is organized to 
--    reflect the information returned by the sp_lock procedure, but you 
--    can add any additional filters or columns that you need. I will 
--    use this query in many examples later in this chapter, so I’ll 
--    create a VIEW based on the SELECT and call it DBlocks. 

CREATE VIEW DBlocks AS
SELECT request_session_id as spid, 
	 db_name(resource_database_id) as dbname, 
    CASE 
   WHEN resource_type = 'OBJECT' THEN 
         object_name(resource_associated_entity_id)	
	   WHEN resource_associated_entity_id = 0 THEN 'n/a'
   ELSE object_name(p.object_id) 
	 END as entity_name, index_id,
       resource_type as resource, 
	    resource_description as description, 
       request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
	ON p.hobt_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
GO


-- Example 1: SELECT with Default Isolation Level
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT * FROM Production.Product
WHERE Name = 'Reflector';
SELECT * FROM DBlocks WHERE spid = @@spid;
COMMIT TRAN;


-- Example 2: SELECT with Repeatable Read Isolation Level
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT * FROM Production.Product
WHERE Name LIKE 'Racing Socks%';
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'Product';
COMMIT TRAN;
GO


-- Example 3: SELECT with Serializable Isolation Level
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM Production.Product
WHERE Name LIKE 'Racing Socks%';
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'Product';
COMMIT TRAN;
GO


-- Example 4: Update Operations
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.6
WHERE Name LIKE 'Racing Socks%';
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'Product';
COMMIT TRAN;
GO


-- Example 5: Update with Serializable Isolation Level Using an Index
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.6
WHERE Name LIKE 'Racing Socks%';
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'Product';
COMMIT TRAN;
GO


-- Example 6: Update with Serializable Isolation Level Not Using an Index
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.6
WHERE Color = 'White'
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'Product'
COMMIT TRAN;


-- Example 7: Creating a Table
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT * 
INTO newProducts
FROM Production.Product
WHERE ListPrice between 1 and 10
SELECT * FROM DBlocks 
WHERE spid = @@spid;
COMMIT TRAN;
GO


-- Example 8: Row Locks
 
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE newProducts
SET ListPrice = 5.99
WHERE name = 'Road Bottle Cage'
SELECT * FROM DBlocks 
WHERE spid = @@spid
AND entity_name = 'newProducts'
COMMIT TRAN;
GO


-- You can analyze the syslockinfo.rsc_bin field as the resource block. 
--    Let’s look at an example. I’ll select a single row from the 
--    Person.Contact table in AdventureWorks using the REPEATABLE READ 
--    isolation level, so my shared locks will continue to be held for the 
--    duration of the transaction. I’ll then look at the rsc_bin column in 
--    syslockinfo for key locks, page locks, and table locks.

USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRAN
SELECT * FROM Person.Contact 
WHERE ContactID = 249;
GO
SELECT rsc_bin, rsc_type 
FROM master..syslockinfo
WHERE rsc_type IN (5,6,7);
GO


-- You can actually generate the deadlock SQL Server Management Studio,
--     as follows:
--   Open a query window, and change your database context to the 
--    AdventureWorks database. Execute the following batch for process A:

BEGIN TRAN
    UPDATE  Production.Product 
    SET ListPrice = ListPrice * 0.9
    WHERE ProductID  = 922;

-- Open a second window, and execute this batch for process B:

BEGIN TRAN
    UPDATE  Purchasing.PurchaseOrderDetail
    SET OrderQty = OrderQty + 200
    WHERE ProductID  = 922
    AND PurchaseOrderID = 499; 

--  Go back to the first window, and execute this update statement:

UPDATE  Purchasing.PurchaseOrderDetail
    SET OrderQty = OrderQty - 200
    WHERE ProductID  = 922
    AND PurchaseOrderID = 499;

--  At this point, the query should block. It is not deadlocked yet, 
--    however. It is waiting for a lock on the PurchaseOrderDetail table, 
--    and there is no reason to suspect that it won’t eventually get that 
--    lock.
 
-- Go back to the second window, and execute this update statement:

UPDATE  Production.Product 
    SET ListPrice = ListPrice * 0.9
    WHERE ProductID  = 922;

-- At this point, a deadlock occurs. 


-- You can see the values of each of these snapshot states for all your 
--    databases with the following query:

SELECT name, snapshot_isolation_state_desc, 
		is_read_committed_snapshot_on , *
FROM sys.databases;
GO


-- Run the script below to make a copy of the Production.Product table, and 
--    then turn on ALLOW_SNAPSHOT_ISOLATION in the AdventureWorks database. 
--    Finally, verify that the option is ON and that there are currently no 
--    rows in the version store. You might need to close any active 
--    transactions currently using AdventureWorks. 

USE AdventureWorks
SELECT * INTO NewProduct
FROM Production.Product;
GO

ALTER DATABASE ADVENTUREWORKS SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

SELECT name, snapshot_isolation_state_desc, 
		is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name= 'AdventureWorks';
GO

SELECT COUNT(*) FROM sys.dm_tran_version_store
GO

-- As soon as you see that the database option is ON and there are no rows in 
--    the version store, you can continue. What I want to illustrate is that as 
--    soon as ALLOW_SNAPSHOT_ISOLATION is enabled, SQL Server starts storing 
--    row versions, even if there are no snapshot transactions that need to 
--    read those version. So now run this UPDATE statement on the NewProduct 
--    table, and look at the version store again.

UPDATE  NewProduct
SET ListPrice = ListPrice * 1.1;
GO
SELECT COUNT(*) FROM sys.dm_tran_version_store;
GO



-- I’ll now create a simple versioning scenario to illustrate how the values
--    in the snapshot metadata get updated. This will not be a complete 
--    overview, but it should get you started in exploring the versioning 
--    metadata for your own queries. I’ll be using the AdventureWorks database, 
--    which has ALLOW_SNAPSHOT_ISOLATION set to ON, and I’ll create a simple 
--    table:

CREATE TABLE t1
(col1 int primary key, col2 int);
GO
INSERT INTO t1 SELECT 1,10;
INSERT INTO t1 SELECT 2,20;
INSERT INTO t1 SELECT 3,30;
GO

-- We’ll call this session Connection 1. Change the session’s isolation level 
--    and start a snapshot transaction, and examine some of the metadata: 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN;
SELECT * FROM t1;
GO

SELECT * FROM sys.dm_tran_current_transaction 
SELECT * FROM sys.dm_tran_version_store
SELECT * FROM sys.dm_tran_transactions_snapshot
go


-- In another connection (Connection 2), run an update and examine 
--    some of the metadata for the current transaction:

BEGIN TRAN
 UPDATE T1 SET col2 = 100
	WHERE col1 = 1;
SELECT * FROM sys.dm_tran_current_transaction ;
GO

-- Note that although this transaction has an XSN because it will generate 
--    versions, it is not running in SI, so the is_snapshot value is 0. I’ll 
--    refer to this transaction’s XSN as XSN2.

- Now start a third transaction in a Connection 3 to perform another SELECT. 
--    (Don’t worry, this is the last one and we won’t be keeping it around.) 
--    It will be almost identical to the first, but there will be an important 
--    difference in the metadata results.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN;
SELECT * FROM t1;
GO
SELECT * FROM sys.dm_tran_current_transaction;
SELECT * FROM sys.dm_tran_transactions_snapshot;
GO

-- In the sys.dm_tran_current_transaction view, you’ll see a new XSN for this
--    transaction (XSN3), and you’ll see that the value for 
--    first_snapshot_sequence_num and first_useful_sequence_num 
--    are both the same as XSN1. In the sys.dm_tran_transactions_snapshot 
--    view, you’ll see that this transaction with XSN3 has two rows, 
--    indicating the two transactions that were active when this one started. 
--    Both XSN1 and XSN2 show up in the snapshot_sequence_num column. You can 
--    now either commit or roll back this transaction, and then close the 
--    connection.

-- Go back to Connection 2, where you started the UPDATE, and COMMIT it.

-- Now let’s go back to the first SELECT transaction in Connection 1 and rerun 
--    the SELECT statement, staying in the same transaction.

SELECT * FROM t1;
GO

-- Even though the UPDATE in Connection 2 has committed, we will still see the 
--    original data values because we are running a snapshot transaction. We 
--    can examine the sys.dm_tran_active_snapshot_database_transactions view 
--    with this query:

SELECT transaction_sequence_num, commit_sequence_num, 
	is_snapshot, session_id,first_snapshot_sequence_num,
	max_version_chain_traversed, elapsed_time_seconds
FROM  sys.dm_tran_active_snapshot_database_transactions;
GO

--  You could actually run the above query from any connection; it shows ALL 
--    active snapshot transactions in the SQL Server instance, and because it 
--    includes the session_id, you can join it to sys.dm_exec_sessions to get 
--    information about the connection that is running the transaction.

SELECT transaction_sequence_num, commit_sequence_num, 
	is_snapshot, t.session_id,first_snapshot_sequence_num,
	max_version_chain_traversed, elapsed_time_seconds, 
    host_name, login_name, transaction_isolation_level
FROM  sys.dm_tran_active_snapshot_database_transactions t
   JOIN sys.dm_exec_sessions s
	  ON t.session_id = s.session_id;
GO

-- Another value to note is in the column called max_version_chain_traversed. 
--    Although now it should be 1, we can change that. Go back to Connection 2 
--    and run another UPDATE statement. Even though the BEGIN TRAN and 
--    COMMIT TRAN are not necessary for a single statement transaction, 
--    I am including them to make it clear that this transaction is complete.

BEGIN TRAN;
 UPDATE T1 SET col2 = 300
	WHERE col1 = 1;
COMMIT TRAN;
GO

-- Examine the version store if desired, to see rows being added.

SELECT *
 FROM sys.dm_tran_version_store;
GO

-- When you go back to Connection 1 and run the same SELECT inside the original 
--    transaction and look again at the max_version_chain_traversed column in 
--    sys.dm_tran_active_snapshot_database_transactions, you should see that 
--    the number keeps growing. Repeated UPDATE operations, either in 
--    Connection 2 or a new connection, will cause the 
--    max_version_chain_traversed value to just keep increasing, as long as 
--    Connection 1 stays in the same transaction. 



-- The code below creates a copy of the HumanResources.Department table and 
--    then creates two triggers on the new Department table. All the trigger 
--    does is return a single row containing the number of rows in the version 
--    store and the size of all the row versions in the version store. I have 
--    included the statement to turn off row versioning in the AdventureWorks 
--    database to confirm that the use of the version store by triggers doesn’t 
--    depend on any database option.

-- Turn off the snapshot options
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT OFF;
GO

-- Make a copy of the Department table (15 rows)
USE AdventureWorks;
SELECT * 
INTO Department 
FROM  HumanResources.Department;
GO

-- Create two triggers, one for UPDATE and one for DELETE
CREATE TRIGGER upd_Department
ON Department 
FOR UPDATE 
AS
SELECT count(*) AS NumRows, 
    (sum(record_length_first_part_in_bytes)  +   
     sum(record_length_second_part_in_bytes))/8060. AS Version_store_Pages
FROM  sys.dm_tran_version_store;
GO

CREATE TRIGGER del_Department
ON Department 
FOR DELETE 
AS
SELECT count(*) AS NumRows, 
    (sum(record_length_first_part_in_bytes)  +   
     sum(record_length_second_part_in_bytes))/8060. AS Version_store_Pages
FROM  sys.dm_tran_version_store;
GO

-- Now update a single row in the Department table and notice the count of rows 
--    in the version store. There should be one row for the inserted table and 
--    one row for the deleted table.

UPDATE Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 11;
GO

-- Now delete a single row in the Department table and notice the count of rows 
--    in the version store. There should be only one row for the deleted table.

DELETE Department
WHERE DepartmentID = 12;
GO

-- Because tempdb is used for the version store, applications that make heavy 
--    use of triggers in SQL Server 2000 must be aware of potentially increased 
--    demands on tempdb after an upgrade to SQL Server 2005. 



-- The following example illustrates the difference between READPAST, 
--    READUNCOMMITTED, and setting LOCK_TIMEOUT to 0. All of these techniques 
--    let you “get around” locking problems, but the behavior is slightly 
--    different in each case.

-- In a new query window, execute the following batch to lock one row in the 
--    HumanResources.Department table:

USE AdventureWorks;
BEGIN TRAN;
UPDATE HumanResources.Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 1;
GO

-- Open a second connection, and execute the following statements:

USE AdventureWorks;
SET LOCK_TIMEOUT 0;
SELECT * FROM HumanResources.Department;
SELECT * FROM Sales.SalesPerson;
GO

-- Notice that after error 1222 is received, the second SELECT statement is 
--    executed, returning all 17 rows from the SalesPerson table. The batch is 
--    not cancelled when error 1222 is encountered.

-- Warning: Not only is a batch not cancelled when a lock timeout error is 
--    encountered, but any active transaction will not be rolled back. If you 
--    have two UPDATE statements in a transaction and both must succeed if 
--    either succeeds, a timeout for one of the UPDATE statements will still 
--    allow the other statement to be processed. You must include error 
--    checking in your batch to take appropriate action in the event of an 
--    error 1222.

-- Open a third connection, and execute the following statements:

USE AdventureWorks;
SELECT * FROM HumanResources.Department (READPAST);
SELECT * FROM Sales.SalesPerson;
GO

-- SQL Server skips (reads past) only one row, and the remaining 15 rows of 
--    Department are returned, followed by all the SalesPerson rows.


-- Open a fourth connection, and execute the following statements:
     
USE AdventureWorks;
SELECT * FROM HumanResources.Department (READUNCOMMITTED);
SELECT * FROM Sales.SalesPerson;
GO

-- In this case, SQL Server does not skip anything. It reads all 16 rows from 
--    Department, but the row for Department 1 shows the dirty data that you 
--    changed in step 1. This data has not yet been committed and is subject 
--    to being rolled back.