SQLServerCh17

Chapter 17

How to manage
transactions and locking

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given a set of statements to be combined into a transaction, insert
the Transact-SQL statements to explicitly begin, commit, and roll
back the transaction.
Knowledge
 Describe the use of implicit transactions.
 Describe the use of explicit transactions.
 Describe the use of the COMMIT TRAN statement and the
@@TRANCOUNT function within nested transactions.
 Describe the use of save points.
 Define these types of concurrency problems: lost updates, dirty
reads, nonrepeatable reads, and phantom reads.

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the way locking and the transaction isolation level help
to prevent concurrency problems.
 Describe the way SQL Server manages locking in terms of
granularity, lock escalation, shared locks, exclusive locks, and
lock promotion.
 Describe deadlocks and the way SQL Server handles them.
 Describe four coding techniques that can reduce deadlocks.

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 3

INSERT statements that work with related data
DECLARE @InvoiceID int;
INSERT Invoices
VALUES (34,'ZXA-080','2012-04-30',14092.59,
0,0,3,'2012-05-30',NULL);
SET @InvoiceID = @@IDENTITY;
INSERT InvoiceLineItems
VALUES (@InvoiceID,1,160,4447.23,'HW upgrade');
INSERT InvoiceLineItems
VALUES (@InvoiceID,2,167,9645.36,'OS upgrade');

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 4

The same statements coded as a transaction
DECLARE @InvoiceID int;
BEGIN TRY
BEGIN TRAN;
INSERT Invoices
VALUES (34,'ZXA-080','2012-04-30',14092.59,
0,0,3,'2012-05-30',NULL);
SET @InvoiceID = @@IDENTITY;
INSERT InvoiceLineItems
VALUES (@InvoiceID,1,160,4447.23,'HW upgrade');
INSERT InvoiceLineItems
VALUES (@InvoiceID,2,167,9645.36,'OS upgrade');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 5

When to use explicit transactions
 When you code two or more action queries that affect related data
 When you update foreign key references
 When you move rows from one table to another table
 When you code a SELECT query followed by an action query and
the values inserted in the action query are based on the results of
the SELECT query
 When a failure of any set of SQL statements would violate data
integrity

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 6

The SQL statements for processing transactions
BEGIN {TRAN|TRANSACTION}
SAVE {TRAN|TRANSACTION} save_point
COMMIT [TRAN|TRANSACTION]
ROLLBACK [[TRAN|TRANSACTION] [save_point]]

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 7

A script that performs a test
before committing the transaction
BEGIN TRAN;
DELETE Invoices
WHERE VendorID = 34;
IF @@ROWCOUNT > 1
BEGIN
ROLLBACK TRAN;
PRINT 'More invoices than expected. ' +
'Deletions rolled back.';
END;
ELSE
BEGIN
COMMIT TRAN;
PRINT 'Deletions committed to the database.';
END;

The response from the system
(3 row(s) affected)
More invoices than expected. Deletions rolled back.

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 8

How nested transactions work
 If you commit a transaction when @@TRANCOUNT is equal to
1, all of the changes made to the database during the transaction
are committed and @@TRANCOUNT is set to zero.
 If you commit a transaction when @@TRANCOUNT is greater
than 1, @@TRANCOUNT is simply decremented by 1.
 The ROLLBACK TRAN statement rolls back all active
transactions regardless of the nesting level where it’s coded. It
also sets the value of @@TRANCOUNT back to 0.

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 9

A script with nested transactions
BEGIN TRAN;
PRINT 'First Tran @@TRANCOUNT: ' +
CONVERT(varchar,@@TRANCOUNT);
DELETE Invoices;
BEGIN TRAN;
PRINT 'Second Tran @@TRANCOUNT: ' +
CONVERT(varchar,@@TRANCOUNT);
DELETE Vendors;
COMMIT TRAN;
-- This COMMIT decrements @@TRANCOUNT.
-- It doesn't commit 'DELETE Vendors'.
PRINT 'COMMIT
@@TRANCOUNT: ' +
CONVERT(varchar,@@TRANCOUNT);
ROLLBACK TRAN;
PRINT 'ROLLBACK
@@TRANCOUNT: ' +
CONVERT(varchar,@@TRANCOUNT);

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 10

A script with nested transactions (continued)
PRINT ' ';
DECLARE @VendorsCount int, @InvoicesCount int;
SELECT @VendorsCount = COUNT (*) FROM Vendors;
SELECT @InvoicesCount = COUNT (*) FROM Invoices;
PRINT 'Vendors Count: ' +
CONVERT (varchar , @VendorsCount);
PRINT 'Invoices Count: ' +
CONVERT (varchar , @InvoicesCount);

The response from the system
First Tran

@@TRANCOUNT: 1

(114 row(s) affected)
Second Tran @@TRANCOUNT: 2
(122 row(s) affected)
COMMIT
@@TRANCOUNT: 1
ROLLBACK
@@TRANCOUNT: 0
Vendors count: 122
Invoices count: 114
Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 11

A transaction with two save points
IF OBJECT_ID('tempdb..#VendorCopy') IS NOT NULL
DROP TABLE tempdb.. #VendorCopy;
SELECT VendorID, VendorName
INTO #VendorCopy
FROM Vendors
WHERE VendorID < 5;
BEGIN TRAN;
DELETE #VendorCopy WHERE VendorID = 1;
SAVE TRAN Vendor1;
DELETE #VendorCopy WHERE VendorID = 2;
SAVE TRAN Vendor2;
DELETE #VendorCopy WHERE VendorID = 3;
SELECT * FROM #VendorCopy;
ROLLBACK TRAN Vendor2;
SELECT * FROM #VendorCopy;
ROLLBACK TRAN Vendor1;
SELECT * FROM #VendorCopy;
COMMIT TRAN;
SELECT * FROM #VendorCopy;

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 12

The response from the system

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 13

Terms
 Transaction
 Commit a transaction
 Roll back a transaction
 Autocommit mode
 Save point

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 14

Two transactions that retrieve and then modify
the same row
Transaction A
BEGIN TRAN;
DECLARE @InvoiceTotal money, @PaymentTotal money,
@CreditTotal money;
SELECT @InvoiceTotal = InvoiceTotal,
@CreditTotal = CreditTotal,
@PaymentTotal = PaymentTotal
FROM Invoices WHERE InvoiceID = 112;
UPDATE Invoices
SET InvoiceTotal = @InvoiceTotal,
CreditTotal = @CreditTotal + 317.40,
PaymentTotal = @PaymentTotal WHERE InvoiceID = 112;
COMMIT TRAN;

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 15

Two transactions that retrieve and then modify
the same row (continued)
Transaction B
BEGIN TRAN;
DECLARE @InvoiceTotal money, @PaymentTotal money,
@CreditTotal money;
SELECT @InvoiceTotal = InvoiceTotal,
@CreditTotal = CreditTotal,
@PaymentTotal = PaymentTotal
FROM Invoices WHERE InvoiceID = 112;
UPDATE Invoices
SET InvoiceTotal = @InvoiceTotal,
CreditTotal = @CreditTotal,
PaymentTotal = @InvoiceTotal - @CreditTotal,
PaymentDate = GetDate() WHERE InvoiceID = 112;
COMMIT TRAN;

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 16

Two transactions that retrieve and then modify
the same row (continued)
The initial values for the row

The values after transaction A executes

The values after transaction B executes

Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 17

The four types of concurrency problems
Problem
Lost updates

Description
Occur when two transactions select the
same row and then update the row based on
the values originally selected.
Occur when a transaction selects data that
isn’t committed by another transaction.

Dirty reads
(uncommitted
dependencies)
Nonrepeatable reads
Occur when two SELECT statements of the
(inconsistent analysis) same data result in different values because
another transaction has updated the data in
the time between the two statements.
Phantom reads
Occur when you perform an update or
delete on a set of rows when another
transaction is performing an insert or delete
that affects one or more rows in that same
set of rows.
Murach's SQL Server 2012, C17

© 2012, Mike Murach & Associates, Inc.

Slide 18