DAT302: Transactional Development with SQL Server

1
DAT302 Transactional Development with SQL Server

• Tony BainConsultant

2
Agenda

• Quick Review
• Transaction Properties - ACID
• Transaction Terminology
• Transaction Commands
• Isolation Issues
• Locks
• Isolation Levels
• Deadlocks
• Best Practice

3
Quick Review - ACID

• Atomic
• If a transaction completes or fails then all
  changes made within the transaction are applied
  or rolled back respectively.
• Consistent
• If a transaction commits, is explicitly rolled
  back or is rolled back due to some form of
  failure it must leave the database in a
  consistent error free form.
• Isolated
• One transaction that is modifying data cannot
  affect, or be affected by another transaction
  that is modifying data.
• Durable
• Once committed the transaction cannot be undone
  by anyone or anything.

4
Quick Review Transaction Terminology

• Transaction Unit of work
• Commit Confirm unit of work
• Rollback Undo work done
• Deadlock Transaction stalemate
• Implicit and Explicit
• Transaction Scope

5
Quick Review Transaction Commands

• TSQL
• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
• .NET SQLDataProvider
• .BeginTransaction
• .Commit
• .Rollback
• .NET maps to TSQL

6
Isolation Issues

• What are the issues?
• Dirty Reads Viewing in doubt information
• Unrepeatable reads Retrieving different values
  on consecutive reads
• Phantoms Retrieve different number of rows on
  consecutive reads
• Lost Updates Two transactions updating same
  information at the same time

7
Locks to the rescue

• Lock Types
• Shared locks Many shared locks can be taken.
  But not if an exclusive lock is in place.
• Exclusive locks Only one exclusive lock can be
  taken on a resource. But not until all shared
  locks have been released.
• Update locks Only one update lock can be taken.
  Can be taken if shared locks exist.
• Intent locks Used internally as a performance
  improver.
• Schema locks

8
Lock Granularity

• Lock Granularity
• Row lock Locks a row within a table.
• Key lock Used within an index for range
  protection.
• Page lock Locks a page
• Extent lock Locks an extent
• Table lock Locks a table

9
Conversion Duration

• Shared locks can be converted to exclusive locks,
  but only if no one else has shared lock on
  resource
• The duration that a Shared lock is held depends
  on the isolation level.
• Once obtained an exclusive locks remains until
  the end of the transaction
• Update locks remain for the duration of the
  transaction, or until escalated

10
Altering Transaction Behavior

• Transactions CANNOT be turned off
• Atomicity, Consistency and Durability cannot be
  affected.
• But Isolation can be balanced against concurrency
• Isolation Levels
• Locking Hints

11
Isolated - Isolation Levels

• What are Isolation levels
• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

12
Read Uncommitted

• Achieved using
• SET TRANSACTION ISOLATION LEVEL Read Uncommitted
• NOLOCK locking Hint
• Bad, Bad, Bad
• Unless always read only, then no point anyway.
• Allows for inconstant query results leading to
  corrupt data within database.

13
Read Committed

• Default Isolation Level for SQL Transactions
• Read data that has been committed
• Shared locks are dropped after read, not held for
  duration of the transaction

14
Repeatable Read

• Reads that are repeated throughout a transaction
  will always retrieve the same values within the
  rows
• However the number of rows may change
• Holds shared locks for the duration of the
  transaction, or until escalated.

15
Serializable

• Highest Isolation, however still allows multi
  user shared locks
• Least concurrent
• Default Isolation of COM
• Holds shared locks and range locks for the
  duration of a transaction, or until escalated.

16
Deadlocks

• Conversion Deadlock
• Two transactions attempting to convert the type
  of locks they hold, each blocking the other
• Cycle Deadlock
• Occurs when objects are accessed in a different
  order
• Use the UPDLOCK hint
• Set Deadlock Priority Level

17
Best Practices

• Build Transactions to succeed
• Only include what is part of the unit of work.
• Keep them brief
• Use lowest isolation level
• Deadlock Prevention Document object access
  order.
• Test all code under heavy user load.

18
Questions?
19
(No Transcript)