-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLQueryTransactions.sql
296 lines (223 loc) · 11.2 KB
/
SQLQueryTransactions.sql
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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
-- Transactions:
-- A transaction is one or more T-SQL statements that are treated as a unit. If a single transaction fails,
-- then all of the statements fail. If a transaction is successful, you know that all the data modification
-- statements in the transaction were successful and committed to the database.
-- https://learn.microsoft.com/en-us/training/modules/implement-transactions-transact-sql/2-describe-transactions
-- Explicit transactions
--The keywords BEGIN TRANSACTION and either COMMIT or ROLLBACK start and end each batch of statements.
-- This allows you to specify which statements must be either committed or rolled back together.
-- Atomicity – each transaction is treated as a single unit, which succeeds completely or fails completely.
-- Consistency – transactions can only take the data in the database from one valid state to another.
-- Isolation – concurrent transactions cannot interfere with one another, and must result in a consistent database state.
-- Durability – when a transaction has been committed, it will remain committed.
USE AdventureWorks2019;
BEGIN TRY
INSERT INTO Sales.Orders(custid, empid, orderdate)
VALUES (68, 9, '2021-07-12');
INSERT INTO dbo.Orders(custid, empid, orderdate)
VALUES (88, 3, '2021-07-15');
INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty)
VALUES (1, 2, 15.20, 20);
INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty)
VALUES (999, 77, 26.20, 15);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
END CATCH;
-- the INSERT statements for the Orders and OrderDetails tables are
-- enclosed within BEGIN TRANSACTION/COMMIT TRANSACTION keywords.
-- This ensures that all statements are treated as a single transaction,
-- which either succeeds or fails. Either one row is written to both the Orders and
-- OrderDetails table, or neither row is inserted.
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Orders(custid, empid, orderdate)
VALUES (68,9,'2006-07-15');
INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty)
VALUES (99, 2,15.20,20);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
ROLLBACK TRANSACTION;
END CATCH;
-- Active transaction
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.SimpleOrders(custid, empid, orderdate)
VALUES (68,9,'2006-07-12');
INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty)
VALUES (1, 2,15.20,20);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
IF (XACT_STATE()) <> 0 -- active transaction
BEGIN
ROLLBACK TRANSACTION;
END;
ELSE .... -- provide for other outcomes of XACT_STATE()
END CATCH;
-- Data Concurrency
-- Concurrency uses locking and blocking to enables data to remain consistent with
-- many users updating and reading data at the same time.
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT OFF;
-- Optimistic concurrency
-- With optimistic locking there's an assumption that few conflicting updates will occur.
-- At the start of the transaction, the initial state of the data is recorded.
-- Before the transaction is committed, the current state is compared with the initial state.
-- If the states are the same, the transaction is completed. If the states are different,
-- the transaction is rolled back.
-- Pessimistic concurrency
-- With pessimistic locking there is an assumption that many updates are happening to the
-- data at the same time. By using locks only one update can happen at the same time,
-- and reads of the data are prevented while updates are taking place.
--exercises
DELETE SalesLT.Customer
WHERE CustomerID = IDENT_CURRENT('SalesLT.Customer');
DELETE SalesLT.Address
WHERE AddressID = IDENT_CURRENT('SalesLT.Address');
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate)
VALUES (0, 'Norman','Newcustomer','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=', NEWID(), GETDATE());
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6', NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, rowguid, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', NEWID(), '12-1-20212');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate)
VALUES (0, 'Norman','Newcustomer','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=', NEWID(), GETDATE());
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6', NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, rowguid, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', '16765338-dbe4-4421-b5e9-3836b9278e63', GETDATE());
COMMIT TRANSACTION;
--transaction with try catch blocks
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate)
VALUES (0, 'Norman','Newcustomer','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=',NEWID(), GETDATE());
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6',NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, rowguid, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', '16765338-dbe4-4421-b5e9-3836b9278e63', GETDATE());
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END CATCH;
-- check state
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate)
VALUES (0, 'Norman','Newcustomer','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=',NEWID(), GETDATE());
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6',NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, rowguid, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', '16765338-dbe4-4421-b5e9-3836b9278e63', GETDATE());
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
PRINT 'An error occurred.'
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Transaction in process.'
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END;
END CATCH
--
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate)
VALUES (0, 'Norman','Newcustomer','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=',NEWID(), GETDATE());
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6',NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', GETDATE());
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
PRINT 'An error occurred.'
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Transaction in process.'
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END;
END CATCH
-- Throw an error
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate) VALUES (0, 'Ann','Othercustomr','[email protected]','U1/CrPqSzwLTtwgBehfpIl7f1LHSFpZw1qnG1sMzFjo=','QhHP+y8=',NEWID(), GETDATE());;
INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate)
VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6',NEWID(), GETDATE());
INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, ModifiedDate)
VALUES (IDENT_CURRENT('SalesLT.Customer'), IDENT_CURRENT('SalesLT.Address'), 'Home', GETDATE());
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
THROW 51000, 'Some kind of error', 1;
END TRY
BEGIN CATCH
PRINT 'An error occurred.'
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Transaction in process.'
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END;
END CATCH
-- exercise:
BEGIN TRY
BEGIN TRANSACTION;
-- Get the highest order ID and add 1
DECLARE @OrderID INT;
SELECT @OrderID = MAX(SalesOrderID) + 1 FROM SalesLT.SalesOrderHeader;
-- Insert the order header
INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderID, GETDATE(), DATEADD(month, 1, GETDATE()), 1, 'CARGO TRANSPORT');
-- Insert one or more order details
INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
VALUES (@OrderID, 1, 712, 8.99);
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
PRINT 'An error occurred.'
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Transaction in process.'
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END;
END CATCH
-- in case of error
BEGIN TRY
BEGIN TRANSACTION;
-- Get the highest order ID and add 1
DECLARE @OrderID INT;
SELECT @OrderID = MAX(SalesOrderID) + 1 FROM SalesLT.SalesOrderHeader;
-- Insert the order header
INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderID, GETDATE(), DATEADD(month, 1, GETDATE()), 1, 'CARGO TRANSPORT');
-- Insert one or more order details
INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
VALUES (@OrderID, 1, 'Invalid product', 8.99);
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
PRINT 'An error occurred.'
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Transaction in process.'
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back.';
END;
END CATCH