216
Chapitre 7. Transactions et verrous
SELECT
OBJECT_NAME(object_id) as table_name,
name as index_name,
allow_row_locks,
allow_page_locks
FROM sys.indexes
WHERE allow_row_locks & allow_page_locks = 0
ORDER BY table_name, index_name;
ALTER INDEX PK_Contact_ContactID
ON Person.Contact
SET (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF);
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT LastName, FirstName, EmailAddress
FROM Person.Contact
WHERE LastName = 'Adams';
SELECT
CASE es.transaction_isolation_level
WHEN 0 THEN 'non spécifié'
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END as transaction_isolation_level,
tl.request_session_id as spid, tl.resource_type, tl.request_mode,
tl.request_type,
CASE
WHEN tl.resource_type = 'object' THEN
OBJECT_NAME(tl.resource_associated_entity_id)
WHEN tl.resource_type = 'database' THEN
DB_NAME(tl.resource_associated_entity_id)
WHEN tl.resource_type IN ('key','page') THEN
(SELECT object_name(i.object_id) + '.' + i.name
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = tl.resource_associated_entity_id)
ELSE CAST(tl.resource_associated_entity_id as varchar(20))
END as objet
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_sessions es
ON tl.request_session_id = es.session_id
WHERE request_session_id = @@spid
ORDER BY
CASE resource_type
WHEN 'DATABASE' THEN 10
WHEN 'METADATA' THEN 20
WHEN 'OBJECT' THEN 30
WHEN 'PAGE' THEN 40
WHEN 'KEY' THEN 50