Stranger-Things/06 Escalation Conn 1.sql at main · retracement/Stranger-Things · GitHub

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
/*************************************************************/
/* All scripts contained within are Copyright � 2025 of      */
/* Mark Broadbent, whether they are derived or actual        */
/* works by him or his representatives                       */
/*************************************************************/
/* They are distributed under the Apache 2.0 licence and any */
/* reproducion, transmittion, storage, or derivation must    */
/* comply with the terms under the licence linked below.     */
/* If in any doubt, contact the license owner for written    */
/* permission by emailing contactme@sturmovik.net            */
/*************************************************************
Copyright [2025] [Mark Broadbent]

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.*/

/*******************************/
/* Concurrency through locking */
/*******************************/
-- In this section we will demonstrate how SQL Server
-- locking can effect concurrency

----------------------------------------------------------
-- In this example we will look at how escalation works --
----------------------------------------------------------
-- We will execute transactional updates in batches

-- Look at table to get a feel for it
SET NOCOUNT ON
USE [Stranger_Things]
GO
IF @@TRANCOUNT <> 0 ROLLBACK
SELECT TOP 3 * FROM Views
SELECT COUNT(*) AS 'Rows' FROM Views


-- Check no open transaction and look at all active locks
EXEC usp_TranHelper @filteronSPID = @@SPID
-- Remember escalation occurs when > 5000 locks are taken


-- Start transaction and update < 1000 records in a batch
SET NOCOUNT OFF -- show counts for demo
BEGIN TRAN
	UPDATE Views
	SET viewedby_id = 1 WHERE view_id < 1000


	-- Now look at the active locks
	EXEC usp_TranHelper @filteronSPID = @@SPID


	-- Update < 4999 records in a batch
	UPDATE Views
	SET viewedby_id = 1 WHERE view_id >= 1000 AND view_id < 5999


	-- Now look at the active locks
	-- Did escalation happen?
	EXEC usp_TranHelper @filteronSPID = @@SPID


	-- Update batch > 5000 records
	UPDATE Views
	SET viewedby_id = 1 WHERE view_id > 5999 AND view_id < 12000;


	-- Now look at the active locks
	-- Did escalation happen?
	EXEC usp_TranHelper @filteronSPID = @@SPID


	-- Switch to session 2 and run query


	-- Now look at the active locks
	-- Can you identify the problem?
	EXEC usp_TranHelper @noworkspacelocks = 1


	-- Rollback tran
	ROLLBACK TRAN
-- fin