o Example: A row cannot exist in table A unless a corresponding row exists in table B (a foreign-key constraint). When inserting a row into table B,
a shared lock will be obtained on the corresponding row in table B until the insert in table A completes, to ensure that nobody deletes the
corresponding row from table B and thereby ensure that the foreign key constraint (a logical consistency rule) is enforced when the transaction completes.
Latches ensure physical consistency
o To say that data is "physically consistent" is to state that the structure of the data on disk or in memory follows the defined patterns
that SQL Server expects (i.e. is not corrupt) and that the right data is stored in the right place (e.g. the value for column Y of row X is not
inadvertently stored in column W of row Z).
Sp_lock shows all outstanding locks, the sessions owning those locks, the resource being locked, the type of lock, and the state of the lock (granted or waiting).
Sys.dm_tran_locks is a data management view in SQL Server 2005 and SQL Server 2008 that shows additional detail about locks held by transactions in SQL Server.
When a process is blocked because it is waiting on a lock, you will see summary detail about the lock it is awaiting in sys.dm_os_waiting_tasks. And sys.dm_os_wait_stats provides a high level summary of time spent waiting on the various types of locks (regardless of the resource being locked).
-------------------------------------------------------------
-- 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
--
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;
No comments:
Post a Comment