1093
Chapter 47: Managing Transactions, Locking, and Blocking
47
Transaction-Log Rollback
If the transaction is rolled back, the DML operations are reversed in memory, and a
transaction-abort entry is made in the log.Transaction-Log Recover y
The primary benefi t of a write-ahead transaction log is that it maintains the durability
transactional property in the case of system failure.If SQL Server should cease functioning (perhaps due to a power failure or physical disaster),
the transaction log is automatically examined when it recovers, as follows:■ (^) Any transactions that have been committed but not yet written to the data fi le are
“redone.” This is the REDO portion of crash recovery.
■ (^) If any entries are in the log as DML operations but are not committed, they are
rolled back. This is known as the UNDO phase of crash recovery.
■ (^) To test this feature, begin a transaction and shut down the SQL server before
issuing a commit transaction (using the services applet). This does a shutdown
nowait. Simply closing Management Studio won’t do it; Management Studio
requests permission to commit the pending transactions and rolls back the trans-
action if permission isn’t given. If SQL Server is shut down normally (this varies
greatly; there are many ways to stop; some gracefully shut down; others don’t), it
waits for any pending tasks to complete before stopping.
■ (^) If you have followed the steps outlined previously, and you disable the system just
before step 7, the transaction-log entries will be identical to those shown later
(refer to Figure 47-10).
■ (^) Start SQL Server and it recovers from the crash nicely and rolls back the unfi nished
transaction. This can be seen in the SQL Server error log.
■ (^) If any entries are in the log as DML operations and committed but not marked as
written to the data fi le, they are written to the data fi le. This feature is nearly
impossible to demonstrate.
Transaction Performance Strategies
Transaction integrity theory can seem daunting at fi rst, and SQL Server has numerous
tools to control transaction isolation. If the database is low usage or primarily read-
only, transaction locking and blocking shouldn’t be a problem. However for heavy-usage
OLTP databases, you need to apply the theory and working knowledge from this chapter
using these strategies. Also if you mix reporting and OLTP systems, you potentially face
blocking issues because reporting systems generally place locks at page or table level,
which isn’t ideal for your OLTP system, which often requires more granular row
level locks.c47.indd 1093c47.indd 1093 7/31/2012 10:23:34 AM7/31/2012 10:23:34 AM
http://www.it-ebooks.info