Chapter 12: Managing Transactions and Locks

1
Chapter 12 Managing Transactions and Locks
2
Overview
P421

• Review of Transaction Log use
• Introduction to Transactions and Locks
• Managing Transactions
• SQL Server Locking Managing Locks

3
Transaction Log Architecture
P422-427

• Write-Ahead Transaction Log
• Flushing the page and Dirty Pages
• Use of Checkpoints
• Minimize what must be processed to recover
• Truncating the Transaction Log
• Shrinking the Transaction Log
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE

4
Transaction Recovery and Checkpoints
Transaction Recovery
Action Required
1
None
2
Roll forward
3
Roll back
4
Roll forward
5
Roll back
Checkpoint
System Failure
5
Concurrency Control
P427

• Pessimistic concurrency control
• High contention for data
• Optimistic concurrency control
• Low contention for data

6
Lockable Resources
P428
7
Types of Locks
P428

• Basic Locks
• Shared
• Exclusive
• Special Situation Locks
• Intent
• Update
• Schema
• Bulk update

8
Lock Compatibility
P428

• Locks May or May Not Be Compatible with Other
  Locks
• Examples
• Shared locks are compatible with all locks except
  exclusive
• Exclusive locks are not compatible with any other
  locks
• Update locks are compatible only with shared locks

9
ACID properties test for Transactions
P432

• Atomicity
• All or none
• Consistency
• Leave data in a consistent state
• Isolation
• Dont show data thats ½ done
• Durability
• Modifications are permanent

10
Considerations for Using Transactions
P433-434

• Transaction Guidelines
• Keep transactions as short as possible
• Use caution with certain Transact-SQL statements
• Avoid transactions that require user interaction
• Issues in Nesting Transactions
• Allowed, but not recommended
• Use _at__at_trancount to determine nesting level

11
Transaction Types
P435-440

• Explicit Transaction
• Explicitly define start and end (Begin Commit)
• Autocommit Transactions
• Every statement is committed or rolled back
• Implicit Transactions
• Statement after Commit Transaction or Rollback
  Transaction starts a new transaction
• SET IMPLICIT_TRANSACTIONS ON

12
Setting the Implicit Transactions Option

• Automatically Starts a Transaction When You
  Execute Certain Statements
• Nested Transactions Are Not Allowed
• Transaction Must Be Explicitly Completed with
  COMMIT or ROLLBACK TRANSACTION
• By Default, Setting Is Off

SET IMPLICIT_TRANSACTIONS ON
13
SQL Server Locking
P446

• Concurrency Problems Prevented by Locks
• Dealing with Deadlocks

14
Concurrency Problems Prevented by Locks
P446-447

• Lost Update
• Two simultaneous updates
• Uncommitted Dependency (Dirty Read)
• Selecting data thats being updated
• Inconsistent Analysis (Nonrepeatable Read)
• Reading data that changed between multiple reads
• Phantoms Reads
• Reading data that doesnt exist on the second read

15
Concurrency Control
P448-449

• Optimistic concurrency control
• Low contention for data
• Pessimistic concurrency control
• High contention for data
• Isolation level issues
• Lower isolation level increases concurrency
  increases chance of reading bad data
• Higher isolation level ensure data is correct
  negatively affects concurrency

16
Isolation levels
P449

• Transaction Isolation Level
• READ COMMITTED (DEFAULT)
• Prevents SELECT statements from reading dirty, or
  uncommitted data
• READ UNCOMMITTED
• Allows dirty reads
• REPEATABLE READ
• No other rows can update/delete rows in the
  result set until released inserting news rows is
  okay
• SERIALIZABLE
• No other transaction can make any changes

17
Dynamic Locking Architecture
P449
DynamicLocking
Cost
Table
Page
Row
Granularity
Locking Cost Concurrency Cost
18
Deadlocks
P450

• How SQL Server Ends A Deadlock
• How to Minimize Deadlocks
• Access objects in same order
• Keep transactions short
• Use low isolation level
• Use bound connections
• How to Customize the Lock Time-Out Setting
• SET LOCK_TIMEOUT function

19
Recommended Practices
20
Review

• Introduction to Transactions and Locks
• Managing Transactions
• SQL Server Locking
• Managing Locks

21
Lab Managing Transactions and Locks
22
Lab Managing Transactions and Locks

• Exercise 1
• Pages 429-430 Accessing and Modifying the
  Transaction Log
• Exercise 2
• Pages 442-445 Implementing Explicit Transactions
• Exercise 3
• Pages 456-457 Configuring Transaction Properties