Transcript 6232B_11x

Module 11
Creating Highly
Concurrent SQL Server®
2008 R2 Applications
Module Overview
• Introduction to Transactions
• Introduction to Locks
• Management of Locking
• Transaction Isolation Levels
Lesson 1: Introduction to Transactions
• What are Transactions?
• Auto Commit Transactions
• Explicit Transactions
• Implicit Transactions
• Transaction Recovery
• Considerations for using Transactions
• Demonstration 1A: Transactions
What are Transactions?
A transaction is an atomic unit of
work
A transaction leaves data in a
consistent state
A transaction is isolated from
other concurrent transactions
A transaction is durable
Auto Commit Transactions
• Default transaction mode
• Every TSQL statement is committed or rolled back when it
has completed. Committed if successful; Rolled back if
error
• Compile errors result in entire batch not being executed
• Run time errors may allow part of the batch to commit
• Database engine operates in autocommit until an explicit
transaction is started.
• XACT_ABORT setting ON converts statement terminating
errors into batch terminating errors; compile errors not
affected by XACT_ABORT ON
SET XACT_ABORT ON;
Explicit Transactions
A transaction in which start and end of transaction is
explicitly declared
• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
BEGIN TRANSACTION FundsTransfer;
EXEC Banking.DebitAccount '100', 'account1';
EXEC Banking.CreditAccount '100', 'account2';
COMMIT TRANSACTION;
• SAVE TRANSACTION
• Transaction Log – Use WITH MARK to specify transaction
marked in log
Implicit Transactions
• Setting implicit transaction mode on
SET IMPLICIT_TRANSACTIONS ON;
• An implicit transaction starts when one of the following
statements is executed and the statement is not part of an
existing transaction
ALTER TABLE
INSERT
CREATE
OPEN
DELETE
REVOKE
DROP
SELECT
FETCH
TRUNCATE TABLE
GRANT
UPDATE
• Transaction must be explicitly completed with
COMMIT or ROLLBACK TRANSACTION
Transaction Recovery
Transaction Recovery
Action Required
1
None
2
Roll forward
Roll back
3
4
Roll forward
5
Checkpoint
Roll back
System Failure
Considerations for using Transactions
Keep transactions as short as possible
Do not
Do not
Access
Do not
require user input
browse data
the least amount of data possible
open the transaction before it is required
Try to access resources in the same order
Accessing resources in the same order within
transactions can help avoid deadlocks
This is not always possible
Considerations for nested transactions
Allowed by syntax but true nesting not supported
Use @@trancount to determine nesting level
When a nested transaction rolls back, it rolls back
the outer transaction as well
Demonstration 1A: Transactions
In this demonstration you will see
• how transactions work
• how blocking affects other users
Note that blocking is discussed further in the next lesson.
Lesson 2: Introduction to Locks
• Methods of Concurrency Control
• What are Locks?
• Blocking vs. Locking
• What Concurrency Problems are Prevented by Locking
• Lockable Resources
• Types of Locks
• Lock Compatibility
Methods of Concurrency Control
Two main concurrency control types:
• Pessimistic
-Locks data when data is read in preparation for update
-Other users are blocked until lock is released
-Use where a high contention for data exists
• Optimistic
-Locks data when an update is performed
-Error received if data is changed since initial read
-Use where a low contention for data exists
What are Locks?
• Mechanism to sync access by multiple users to the same
data at the same time
• Two main types of lock:
Read locks – Allow others to read but not write
Write locks – Stop others from reading or writing
• Locks prevent update conflicts
Locking ensures that transactions are serialized
Locking is automatic
Locks enable concurrent use of data
Blocking vs. Locking
These two terms are often confused:
• Locking
- The action of taking and potentially holding locks
- Used to implement concurrency control
• Blocking
- Normal occurrence for systems using locking
- One process needs to wait for another process to
release locked resources
- Only a problem if it lasts too long
What Concurrency Problems are Prevented by
Locking?
Without locking mechanisms, the following problems can occur:
• Lost updates
• Uncommitted dependency (dirty read)
• Inconsistent analysis (non-repeatable read)
• Phantom reads
• Missing and double reads caused by row updates
Lockable Resources
SQL Server can lock these resources:
RID
A row identifier used to lock a single row within a
heap.
KEY
A row lock within an index used to protect key
ranges in serializable transactions.
PAGE
An 8-kilobyte (KB) page in a database, such as
data or index pages.
EXTENT
A contiguous group of eight pages, such as data
or index pages.
HoBT
A heap or B-tree. A lock protecting a B-tree
(index) or the heap data pages in a table that
does not have a clustered index.
TABLE
The entire table, including all data and indexes.
FILE
APPLICATION
METADATA
ALLOCATION_UNIT
DATABASE
A database file.
An application-specified resource.
Metadata locks.
An allocation unit.
The entire database.
Types of Locks
Lock mode
Description
Shared (S)
Used for read operations.
Update (U)
Used on resources that can be updated.
Exclusive
(X)
Used for data-modification operations, such as
INSERT, UPDATE, or DELETE.
Intent
Used to establish a lock hierarchy.
Schema
Used when an operation dependent on the schema
of a table is executing.
Bulk Update
(BU)
Used when bulk copying data into a table and the
TABLOCK hint is specified.
Key-range
Protects the range of rows read by a query when
using the serializable transaction isolation level.
Lock Compatibility
• Not all locks are compatible with other locks. As a simple
(but incomplete) example:
Lock
Incompatible with
Shared (S)
Exclusive (X)
Update (U)
All but Shared (S)
Exclusive (X)
All other locks
• Refer to Books Online for a complete list
Lesson 3: Management of Locking
• Locking Timeout
• Lock Escalation
• What are Deadlocks?
• Locking-related Table Hints
• Methods to View Locking Information
• Demonstration 3A: Viewing Locking Information
Locking Timeout
• How long should you wait for a lock to be released?
• SET LOCK_TIMEOUT specifies number of
milliseconds to wait
• -1 (default) waits forever
• When timeout expires, error is returned and
statement rolled back
• Not used often as most applications include query
timeouts
• READPAST locking hint – available but rarely used
SET LOCK_TIMEOUT 5000;
Lock Escalation
• Large numbers of rows are often processed
• This brings a need for large numbers of locks
• Acquiring and releasing a large number of locks can have a
significant impact on processing performance and memory
availability
• SQL Server will escalate from row locks to the table level
as needed
• For partitioned tables, it can escalate to the partition level
Lock escalation converts many finegrain locks to fewer coarse-grain locks
What are Deadlocks?
• Occurs when two or more tasks permanently block each
other by each task having a lock on a resource which the
other tasks are trying to lock.
-Task T1 has a lock on resource R1 (arrow from R1 to T1) and has
requested a lock on resource R2 (arrow from T1 to R2).
-Task T2 has a lock on resource R2 (arrow from R2 to T2) and has
requested a lock on resource R1 (arrow from T2 to R1).
-Because neither task can continue until a resource is available and
neither resource can be released until a task continues, a deadlock
state exists.
SQL Server automatically detects this situation and raises an error 1205
Task 1
Task 2
Resource 1
Resource 2
Locking-related Table Hints
Hint
Description
HOLDLOCK
Holds shared locks until the end of the
transaction
NOLOCK
Does not take or honor locks
PAGLOCK
Takes page locks rather than row locks
ROWLOCK
Takes row locks rather than page or table
locks
TABLOCK
Takes a table lock rather than a row or
page lock
TABLOCKX
Takes an exclusive lock on the whole table
UPDLOCK
Takes an update lock and holds it until the
end of the transaction
XLOCK
Exclusive locks are taken and held until
the end of the transaction
Methods to View Locking Information
Activity Dynamic
Monitor Management
Views
SQL Server
Profiler
Reliability and
Performance
Monitor
Demonstration 3A: Viewing Locking Information
In this demonstration, you will see how to:
• View lock information using Activity Monitor
• Use dynamic management views to view lock info
Lesson 4: Transaction Isolation Levels
• SQL Server Transaction Isolation Levels
• Read Committed Snapshot
• Isolation-related Table Hints
SQL Server Transaction Isolation Levels
Isolation Level
Dirty
Read
Nonrepeatable
Read
Phantoms
Read uncommitted
Yes
Yes
Yes
Read committed (default)
No
Yes
Yes
Repeatable read
No
No
Yes
Snapshot
No
No
No
Serializable
No
No
No
• Transaction Isolation Level can be set at the session level
separately for each transaction
Read Committed Snapshot
• SNAPSHOT isolation level is useful but typically requires
modifications to the application

In particular many reporting applications could benefit from it
• Read Committed Snapshot is a database option that
requires no modifications to the application
• Statements that use Read Committed are automatically
promoted to use Read Committed Snapshot instead
• Locks are only held for the duration of the statement, not
the duration of the transaction
ALTER DATABASE Sales
SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE Sales
SET READ_COMMITTED_SNAPSHOT ON;
Isolation-related Table Hints
Hint
Description
READCOMMITTED
Use read committed transaction
isolation level
READCOMMITTEDLOCK
Forces locking to be used when
implementing read committed (when
read committed snapshot has been
enabled)
READUNCOMMITTED
Use read uncommitted transaction
isolation level
REPEATABLEREAD
Use repeatable read transaction
isolation level
SERIALIZABLE
Use serializable transaction isolation
level
Lab 11: Creating Highly Concurrent SQL Server
Applications
• Exercise 1: Detecting Deadlocks
• Challenge Exercise 2: Investigating Transaction Isolation
Levels (Only if time permits)
Logon information
Virtual machine
623XB-MIA-SQL
User name
AdventureWorks\Administrator
Password
Pa$$w0rd
Estimated time: 45 minutes
Lab Scenario
In this lab, you will perform basic investigation of a
deadlock situation. You are trying to determine an
appropriate transaction isolation level for a new application.
If you have time, you will investigate the trade-off between
concurrency and consistency.
Lab Review
• What transaction isolation levels does SQL Server offer?
• How does blocking differ from locking?
Module Review and Takeaways
• Review Questions
• Best Practices