-
Notifications
You must be signed in to change notification settings - Fork 0
/
An Introduction to Concurrent Transactions In SQL Server.txt
184 lines (139 loc) · 10.8 KB
/
An Introduction to Concurrent Transactions In SQL Server.txt
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
Concurrent Transactions In SQL Server
A transaction is a group of commands working together as a single unit of work.
Transactions are designed to change the data in a database and to always leave the data in a consistent state.
Either all of the commands execute and the final result committed or all of the changes are rolled back to their original state.
It is common for many transactions to run concurrently and to be accessing the same tables.
This leads to concurrency problems when two or more transactions attempt to access the same table for an update.
The common types of concurrency problems are Dirty Reads, Lost Updates, Non-repeatable Reads and Phantom Reads.
The isolation level that is chosen for a transaction defines the degree to which one transaction must be isolated from resources or data modifications made by other transactions.
Depending on the isolation level chosen there will be varying degrees of performance and concurrency problems.
The Sql Server Transaction Isolation Levels are Read Uncommitted, Read Committed, Repeatable Read, Snapshot and Serializable.
Read Uncommitted is the fastest since it involves fewer locks on the tables involved in the transaction.
Thie level allows many transactions to run at the same time.
Read Uncommitted is the source of Dirty Reads, Lost Update, Non-repeatable Reads, and Phantom Reads however.
Read Committed is the default setting for transactions in Sql Server.
The Read Committed level of transaction isolation will prevent dirty reads but it will not prevent lost updates, non-repeatable reads or phantom reads.
Repeatable Read transaction isolation level will prevent dirty reads, lost updates, non-repeatable reads but will not prevent phantom reads.
Snapshot transaction isolation level will prevent all of the concurrency problems by going down and causing a rollback if these problems arise.
Serializable transaction isolation level is the strictest of all the transaction isolation levels.
It has an exclusive lock on the tables being updated until the transaction is finished. Other transactions must wait until the transaction completes.
Because of this the Serializable isolation level is the slowest.
DIRTY READS
Dirty reads occur when one transaction is allowed to read the uncommitted data from another transaction.
If the transaction in progress is rolled back then the information read from the buffer is no longer valid.
This occurs with transaction isolation level read uncommitted and this is the only transaction isolation level having this side effect.
Transactions are very fast when run at this level.
> set transaction isolation level read uncommitted
It is also possible to read uncommitted data by using the NOLOCK table hint.
> select * from tblProducts (NOLOCK) where id = 100.
LOST UPDATES
A lost update transaction occurs when two or more transactions update the same data at the same time.
An inventory table may be updated many times as sales occur.
There will be many sales transactions occuring simultaneously on the same data.
A lost update occurs when one of the transactions overwrites the other transaction with the information in its buffer.
This problem occurs with the transaction isolation levels read uncommitted and read committed only.
It does not occur with higher transaction isolation levels.
If the transaction isolation level is set to Repeatable Read then Sql Server will choose the transaction requiring the least amount of work to undo and will kill the process.
The system will give a msg 1205 similar to the following:
Msg 1205, Level 13, State 51, Line 10
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In the event of a conflict the transaction won't run.
NON REPEATABLE READS
A non-repeatable read happens when one transaction reads the same data twice as part of the transaction but another transaction intervenes in between reads and updates the data.
The two reads from the first transaction will not be the same.
To prevent this type of problem use transaction isolation level Repeatable Read or higher isolation level.
For repeatable read there will be additional locks placed on the tables during the first transaction that cause the second transaction to wait until the first transaction has completed.
PHANTOM READS
Phantom reads occur when the number of rows that are read are not the same within a transaction.
Transaction 1 reads data from a table. Transaction 2 then adds or deletes rows from that block and updates the table.
Transaction 1 does an additional read on that same block of data but now rows have now been added or deleted by transaction 2.
This can be prevented by using transaction isolation levels Snapshot or Serializable.
The transaction isolation level Repeatable Read does not prevent inserts/deletes of the block of data and will not prevent a phantom read.
The transaction isolation level Serializable issues a range lock.
A range lock occurs when the where clause of a query contains a range.
This range of records is locked throughout the transaction until it is complete.
The Snapshot transaction isolation level will also prevent phantom reads and all other anomalies discussed here.
Snapshot uses a different strategy than the Serilization level.
The Serialization transaction isolation level uses a range lock making the tables inaccessable to other transactions while it is in progress.
Snapshot isolation doesn't use locks, instead it maintains versioning in the database Tempdb. Snapshot does not utilize locks so transactions do not have to wait for the resource.
For this reason it is much faster than Serialization and it provides the same level of data consistency.
Snapshot Isolation Level
The Snapshot isolation level must be set at the level of the database.
An Alter Database statement is used - ALTER DATABASE MY_DB SET ALLOW_SNAPSHOT_ISOLATION ON (OR OFF), then set transaction isolation level SNAPSHOT.
The transaction using the SNAPSHOT will wait to see if other transactions create a new version of the data during the course of it's processing.
If there is a conflict between two transactions which are both updating the same data and the version of the snapshot becomes invalid the transaction will go down with a message:
Msg 3960, Level 16, State 2, Line 5
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tblProduct' directly or indirectly in database 'ProductDB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
The transaction will not complete and you will be asked to rerun it. This prevents an overwrite of the previous transaction.
Read Committed SnapShot Isolation Level
There is one more transaction isolation level that can be used for concurrent transactions and that is the read committed snapshot isolation level.
The read committed snapshot isolation level is not a different isolaton level, it is a different way of implementing the 'read committed' isolation level.
The read committed isolation level blocks the transaction if it is trying to read data that is currently being updated.
To set the read committed snapshot isolation level for a transaction alter the database SET READ_COMMITTED_SNAPSHOT ON, then set transaction isolation level Read Committed.
This isolation level uses row versioning instead of locks so it will not go down but will instead wait for the other transaction to complete, compared to the transaction going down with a Msg 3960.
Snapshot vs. Read Committed Snapshot
ALTER DATABASE myDB
SET ALLOW_SNAPSHOT_ISOLATION ON/OFF
ALTER DATABASE myDB
SET READ_COMITTED_SNAPSHOT ON/OFF
To summarize, transactions running at the read committed snapshot isolation level will not go down with a Msg 3960 in the event of a conflict and transactions running at the snapshot level will.
Read committed snapshot isolation level doesn't require any changes to the application code and running at snapshot level does.
Read committed snapshot isolation level can be used with distributed transactions and snapshot level can't.
Read committed snapshot isolation level provides statement level read consistency and Snapshot isolation level provides transaction level consistency.
How to find blocking queries in sql server
Blocking occurs as a result of open transactions.
DBCC OpenTran will display the oldest open transaction and will not show all open transactions.
A SQL Server process can be killed using 1. the Sql Server Activity Monitor, 2. Using command KILL process_id causing a rollback.
What transactions are currently open? what queries are currently running in the database?
Issuing the command > DBCC OpenTran
provides information on the oldest open transaction
Transaction information for database 'LOCKING_IN_ADO'.
Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : user_transaction
LSN : (34:24:1)
Start time : Jan 1 2018 8:42:52:457AM
SID : 0x010500000000000515000000327ab70ef190cc4a553262a4e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The following script provides information on all open transactions:
www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/
See also 'Script Open Transactions with Text and PLans.txt
This script will identify all the open processes.
Identify the process that you want to terminate.
One way to terminate a process is by right clicking on the SERVER property and selecting the Activity Monitor.
Here you can identify and terminate the process by right clicking on the process and selecting 'Kill Process'.
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
[Begin Time] ASC;
GO