forked from Baeldung/sql-tutorials
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsqlserver-locks.sql
More file actions
138 lines (102 loc) · 2.38 KB
/
sqlserver-locks.sql
File metadata and controls
138 lines (102 loc) · 2.38 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
-- Change schema to University
USE University;
--Shared Lock (S)
--Session 1
-- Set the isolation level to REPEATABLE READ to hold shared locks until commit/rollback
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT *
FROM Course
WHERE id='CS111';
--Check the locks held by this session:
SELECT request_session_id, resource_type, resource_description, request_mode, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
--Commit later
COMMIT TRAN;
--Session 2
SELECT *
FROM Course
WHERE id='CS111';
BEGIN TRAN;
UPDATE Course
SET credits = 8
WHERE id='CS111';
ROLLBACK TRAN;
--Sesion 3
SELECT
session_id,
blocking_session_id,
command,
status,
wait_type,
wait_time,
last_wait_type,
open_transaction_count,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id <> 0;
--EXEC sp_who2;
--Exclusive Lock (X)
--Session 1
-- Set the isolation level to REPEATABLE READ to hold shared locks until commit/rollback
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
UPDATE Course
SET credits = 8
WHERE id='CS111';
--Check the locks held by this session:
SELECT request_session_id, resource_type, resource_description, request_mode, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
--Rollback later
ROLLBACK TRAN;
--Session 2
BEGIN TRAN;
SELECT *
FROM Course
WHERE id='CS111';
COMMIT TRAN;
--Update Lock (U)
--Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT *
FROM Course
WITH (UPDLOCK)
WHERE id='CS111';
--Check the locks held by this session:
SELECT request_session_id, resource_type, resource_description, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
--Session 2
SELECT *
FROM Course
WHERE id='CS111';
UPDATE Course
SET credits = 8
WHERE id='CS111';
--Shared with Intent Exclusive (SIX)
--Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
-- Explicitly request a SIX lock:
SELECT *
FROM Course
WITH (TABLOCKX, HOLDLOCK)
WHERE id='CS111';
UPDATE Course
SET credits = 8
WHERE id='CS111';
--Rollback later
ROLLBACK TRAN;
SELECT request_session_id, resource_type, resource_description, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
--Session 2
BEGIN TRAN;
SELECT *
FROM Course
WHERE id='CS111';
COMMIT TRAN;