Microsoft® SQL Server® 2012 Bible

1054

Part VIII: Performance Tuning and Optimization

Save Points

You can also declare a save point within the sequence of tasks inside a transaction and then

roll back to that save point. This gives you the fl exibility to roll back sections of code with-

out dooming the entire transaction.

Consider as an example a stored procedure called within a transaction. If you were to

declare a save point at the beginning of the stored procedure, you could rollback any work

accomplished inside that stored procedure without needing to roll back the outside

transaction. Although this is not a best practice for coding standards, it does give you the

fl exibility to accomplish this task if needed.

Default Locking and Blocking Behavior

SQL Server uses locks, an internal memory structure, to provide transactional integrity

between transactions.

There are different types of locks; among these are Shared (reading), Update (getting ready

to write), Exclusive (writing), and many more. Some of these locks work well together, that

is, two transactions can have Shared locks on a resource. However, when an exclusive

lock has been acquired on a resource, no other transaction can acquire locks on that same

resource. The locks used by the transactions are blocking other transaction from gaining

access to the resource.

The different types of locks and how compatible they are with each other is documented in BOL at:

http://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx.

SQL Server’s default transaction isolation is read committed, meaning that SQL Server

ensures that only committed data is read. When a transaction updates a row, and the data

is still yet uncommitted, SQL Server makes other transactions that want to read that

data wait until the fi rst transaction is committed.

To demonstrate SQL Server’s default locking and blocking behavior, the following code

walks through two transactions accessing the same row. Transaction 1 updates the row,

whereas transaction 2 attempts to select the row. The best way to see these two transac-

tions is with two query editor windows, as shown in Figure 47-1.

Transaction 1 opens a logical transaction and updates the department table:

-- Transaction 1

USE AdventureWorks2012

BEGIN TRANSACTION

UPDATE HumanResources.Department

SET Name = 'New Name'

WHERE DepartmentID = 1;

c47.indd 1054c47.indd 1054 7/31/2012 10:23:27 AM7/31/2012 10:23:27 AM

http://www.it-ebooks.info