Two Simultaneous Transactions in Snapshot Isolation That Cannot Be Run Serially
USE pubs
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DECLARE @price money
BEGIN TRAN
SELECT @price = price
FROM titles
WHERE title_id = 'BU1032'
UPDATE titles
SET price = @price
WHERE title_id = 'PS7777'
COMMIT TRAN
USE pubs
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DECLARE @price money
BEGIN TRAN
SELECT @price = price
FROM titles
WHERE title_id = 'PS7777'
UPDATE titles
SET price = @price
WHERE title_id = 'BU1032'
COMMIT TRAN
-- We can see those locks by first running this code:
USE Adventureworks2008;
BEGIN TRAN
UPDATE Sales.SalesOrderHeader
SET ShipDate = ShipDate + 1
WHERE SalesOrderID = 43666;
-- 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
-- We can verify what table it is by using the following query:
SELECT object_name(722101613)
-- For example, we can issue the following query within an explicit transaction in
-- the AdventureWorks2008 database:
BEGIN TRAN
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN 100 and 110;
-- Assume that we are in TRANSACTION ISOLATION LEVEL SERIALIZABLE and we issue this
-- SELECT statement:
SELECT * FROM Person.Person
WHERE LastName BETWEEN 'Freller' AND 'Freund';
-- 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();
-- Example 1: SELECT with Default Isolation Level
USE Adventureworks2008;
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 AdventureWorks2008;
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
-- Example 3: SELECT with Serializable Isolation Level
USE AdventureWorks2008 ;
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
-- Example 4: Update Operations
USE AdventureWorks2008;
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
-- Example 5: Update with Serializable Isolation Level Using an Index
USE AdventureWorks2008;
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
-- Example 6: Update with Serializable Isolation Not Using an Index
USE AdventureWorks2008;
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 AdventureWorks2008;
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
-- Example 8: Row Locks
USE AdventureWorks2008;
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
-- I’ll then look at the rsc_bin column in syslockinfo for key locks, page locks, and
-- table locks.
USE AdventureWorks2008
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
SELECT * FROM Person.Person
WHERE BusinessEntityID = 249
GO
SELECT rsc_bin, rsc_type
FROM master..syslockinfo
WHERE rsc_type IN (5,6,7)
GO
-- I can translate this to an object name as follows:
SELECT object_name(1509580416)
-- To see which object this partition belongs to, I can query the sys.partitions view:
SELECT object_name(object_id)
FROM sys.partitions
WHERE partition_ID = 72057594045333504;
-- Consider the following example, which creates a clustered and nonclustered index on a
-- tiny table, and then selects the %%lockres%% value for each row, first using the clustered
-- index and then using the nonclustered index:
CREATE TABLE lockres (c1 int, c2 int);
GO
INSERT INTO lockres VALUES (1,10);
INSERT INTO lockres VALUES (2,20);
INSERT INTO lockres VALUES (3,30);
GO
CREATE UNIQUE CLUSTERED INDEX lockres_ci ON lockres(c1);
CREATE UNIQUE NONCLUSTERED INDEX lockres_nci ON lockres(c2);
GO
SELECT %%lockres%% AS lock_resource, * FROM lockres WITH (INDEX = lockres_ci);
SELECT %%lockres%% AS lock_resource, * FROM lockres WITH (INDEX = lockres_nci);
GO
-- I could find which row that resource corresponds to with the following query:
SELECT * FROM lockres
WHERE %%lockres%% = '(010086470766)'
-- The value returned looks just like the special value %%physloc%% that was mentioned in
-- Chapter 5, “Tables”:
CREATE TABLE lockres_on_heap (c1 int, c2 int);
GO
INSERT INTO lockres_on_heap VALUES (1,10);
INSERT INTO lockres_on_heap VALUES (2,20);
INSERT INTO lockres_on_heap VALUES (3,30);
GO
SELECT %%lockres%% AS lock_resource, * FROM lockres_on_heap;
-- Here is an example of disabling lock escalation on the TransactionHistory table:
ALTER TABLE TransactionHistory
SET (LOCK_ESCALATION = DISABLE);
-- Execute the following batch for process A:
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.9
WHERE ProductID = 922;
-- 2. 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;
-- 3. Go back to the first window, and execute this UPDATE statement:
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = OrderQty - 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
-- 4. Go back to the second window, and execute this UPDATE statement:
UPDATE Production.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductID = 922;
-- Table 10-9 A SELECT Running in RCSI
-- Transaction 1
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = 10.00
WHERE ProductID = 922;
COMMIT TRAN
-- Transaction 2
BEGIN TRAN
SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 8.89
SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
COMMIT TRAN
-- RCSI is enabled and disabled with the ALTER DATABASE command, as shown in this command
-- to enable RCSI in the AdventureWorks2008 database:
ALTER DATABASE AdventureWorks2008
SET READ_COMMITTED_SNAPSHOT ON
-- The blocking can be avoided by specifying a TERMINATION clause for the ALTER command,
-- as discussed in Chapter 3.
ALTER DATABASE AdventureWorks2008
SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
-- For a session-level option to take effect, you must also allow the database to use SI
-- by altering the database:
ALTER DATABASE AdventureWorks2008
SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Table 10-10 A SELECT Running in a SNAPSHOT Transaction
-- Transaction 1
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = 12.00
WHERE ProductID = 922;
COMMIT TRAN
-- Transaction 2
SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
BEGIN TRAN
SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
-- This is the beginning of
-- the transaction
SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
-- Return the committed
-- value as of the beginning
-- of the transaction
COMMIT TRAN
SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 12.00
-- 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;
-- Table 10-13 An Update Conflict in SNAPSHOT Isolation
-- Transaction 1
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity=Quantity + 200
WHERE ProductID = 872;
-- Quantity is now 524
COMMIT TRAN
-- Transaction 2
SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
BEGIN TRAN
SELECT Quantity
FROM Production.ProductInventory
WHERE ProductID = 872;
-- SQL Server returns 324
-- This is the beginning of
-- the transaction
UPDATE Production.ProductInventory
SET Quantity=Quantity + 300
WHERE ProductID = 872;
-- Process will block
-- Process receives error 3960
-- In Table 10-13, Transaction 2 could use UPDLOCK on its initial SELECT as follows:
SELECT Quantity
FROM Production.ProductInventory WITH (UPDLOCK)
WHERE ProductID = 872;
-- Table 10-14 DDL Inside a SNAPSHOT Transaction
-- Transaction 1
SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
BEGIN TRAN
SELECT count(*)
FROM Production.Product
-- This is the beginning of
-- the transaction
CREATE TABLE NewProducts
( <column definitions>)
-- This DDL is legal
CREATE INDEX PriceIndex
ON Production.Product
(ListPrice)
-- This DDL will generate an
-- error
-- Transaction 2
BEGIN TRAN
INSERT Production.Product
VALUES (9999, .....)
-- A new row is insert into
-- the Product table
COMMIT TRAN
-- Table 10-15 Concurrent DDL Outside the SNAPSHOT Transaction
-- Transaction 1
SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
BEGIN TRAN
SELECT TOP 10 *
FROM Production.Product;
-- This is the start of
-- the transaction
SELECT TOP 10 *
FROM Production.Product;
-- Succeeds
-- The ALTER to a different
-- table does not affect
-- this transaction
SELECT TOP 10 * FROM Production.Product;
-- ERROR
-- Transaction 2
BEGIN TRAN
ALTER TABLE Purchasing.Vendor
ADD notes varchar(1000);
COMMIT TRAN
BEGIN TRAN
ALTER TABLE Production.Product
ADD LowestPrice money;
COMMIT TRAN
-- You might need to close any active transactions currently using AdventureWorks2008:
USE AdventureWorks2008
SELECT * INTO NewProduct
FROM Production.Product;
GO
ALTER DATABASE ADVENTUREWORKS2008 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
SELECT name, snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name= AdventureWorks2008;
GO
SELECT COUNT(*) FROM sys.dm_tran_version_store;
GO
-- 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
-- The output shows only one of the rows inserted:
CREATE TABLE T1 (T1ID char(1), T1name char(10))
GO
INSERT T1 SELECT 'A', 'aaaaaaaaaa'
INSERT T1 SELECT 'B', 'bbbbbbbbbb'
GO
DBCC IND (AdventureWorks2008, 'T1',-1) -- page 6709
DBCC TRACEON (3604)
DBCC PAGE('AdventureWorks2008', 1, 6709, 1)
-- If I update one of these rows, the previous row is written to the version store and
-- the XSN is reflected in the row versioning information:
UPDATE T1 SET T1name = '2222222222' where T1ID = 'A';
GO
DBCC PAGE('AdventureWorks2008', 1, 6709, 1)
GO
-- Here’s an example of a ghost record under versioning:
DELETE T1 WHERE T1ID = 'B'
DBCC PAGE('AdventureWorks2008 ', 1, 6709, 1)
GO
-- I’ll use the AdventureWorks2008 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;
-- Change the session’s isolation level, 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;
-- 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;
-- It is almost identical to the first, but there is 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;
-- 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;
-- 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;
-- You could actually run the preceding 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;
-- 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;
-- Examine the version store if desired, to see rows being added.
SELECT *
FROM sys.dm_tran_version_store;
-- This example sets the LOCK_TIMEOUT value to 5 seconds and then retrieves that value for display:
SET LOCK_TIMEOUT 5000;
SELECT @@LOCK_TIMEOUT;
-- 1. In a new query window, execute the following batch to lock one row in the HumanResources.Department
-- table:
USE AdventureWorks2008;
BEGIN TRAN;
UPDATE HumanResources.Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 1;
-- 2. Open a second connection, and execute the following statements:
USE AdventureWorks2008;
SET LOCK_TIMEOUT 0;
SELECT * FROM HumanResources.Department;
SELECT * FROM Sales.SalesPerson;
-- 3. Open a third connection, and execute the following statements:
USE AdventureWorks2008 ;
SELECT * FROM HumanResources.Department (READPAST);
SELECT * FROM Sales.SalesPerson;
-- 4. Open a fourth connection, and execute the following statements:
USE AdventureWorks2008 ;
SELECT * FROM HumanResources.Department (READUNCOMMITTED);
SELECT * FROM Sales.SalesPerson;
