1050
Part VIII: Performance Tuning and Optimization
example, a single insert command that inserts 25 rows is a logical unit of work. All the
25 rows must be inserted together or none of them are inserted. An update to a single row
operates within a transaction so that the row in the clustered index (or heap) and the row’s
data in every nonclustered index are updated. Any partially competed transaction would
violate transactional integrity.Logical Transactions
If the logical unit of work involves multiple operations, some code is needed to defi ne the
bounds of the transaction: two statements — one at the beginning of the transaction and
the other at its completion, at which time the transaction is committed to stable media. If
the code detects an error, the entire transaction can be rolled back, or undone. The follow-
ing three commands appear simple, but a volume of sophistication lies behind them:■ (^) begin transaction
■ commit transaction
■ (^) rollback transaction
(The text in bold is the required portion of the command.)
A transaction, once begun, should be either committed or rolled back as soon as possible.
An open transaction continues to hold locks for the duration of the transaction, which
likely leads to problems on a production system. Consider the following transaction. The
fi rst update statement decrements 100 units of quantity from the ProductInventory
table for LocationID 6, whereas the next statement adds those 100 units of quantity to the
LocationID of 50. You can see how both of these statements would need to commit together
or not at all; otherwise you’d have Product quantity in the system that you’d have a hard
time accounting for.
USE AdventureWorks2012
BEGIN TRY
BEGIN TRANSACTION
UPDATE Production.ProductInventory
SET Quantity -= 100
WHERE ProductID = 527
AND LocationID = 6 -- misc storage
AND Shelf = 'B'
AND Bin = 4;
UPDATE Production.ProductInventory
SET Quantity += 100
WHERE ProductID = 527
AND LocationID = 50 -- subassembly area
AND Shelf = 'F'
AND Bin = 11;
c47.indd 1050c47.indd 1050 7/31/2012 10:23:27 AM7/31/2012 10:23:27 AM
http://www.it-ebooks.info