transactions and exception handling
Download
Skip this Video
Download Presentation
Transactions and Exception Handling

Loading in 2 Seconds...

play fullscreen
1 / 21

Transactions and Exception Handling - PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on

Transactions and Exception Handling. Eric Allsop SQLBits 6 th October 2007. Transactions and Exception Handling. Transactions Lock Manager Locks, lockable resources and modes Managing locks Concurrency vs. Isolation Blocks and Deadlocks Old School Exception Handling TRY/CATCH

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Transactions and Exception Handling' - kyra-rogers


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
transactions and exception handling

Transactions and Exception Handling

Eric Allsop

SQLBits

6th October 2007

transactions and exception handling1
Transactions and Exception Handling
  • Transactions
  • Lock Manager
  • Locks, lockable resources and modes
  • Managing locks
  • Concurrency vs. Isolation
  • Blocks and Deadlocks
  • Old School Exception Handling
  • TRY/CATCH
  • Managing Exceptions in Transactions
what is a transaction
What is a Transaction?
  • Atomicity
  • Consistency
  • Isolation
  • Durability
types of transaction
Types of Transaction
  • Implementation
    • Auto commit
    • Implicit (IMPLICIT_TRANSACTION)
    • Explicit (BEGAN TRAN etc.)
  • Scope
    • Local
    • Distributed
lock manager
Lock Manager
  • Internal SQL Server service
  • Manages access to resources
  • Services lock and latch requests
  • Enforces isolation level
  • In memory service
  • Fixed memory allocation
lockable resources
Lockable Resources
  • Common Access
    • RID / KEY
    • Page
    • Table
    • Database
  • Space Management
    • Extent
    • HOBT
    • Allocation Unit
    • File
  • Other
    • Application
    • Metadata
  • Hierarchy of lockable resources
  • Locks applied at most suitable level to maximise concurrency
  • Automatic lock escalation driven by memory limits
  • Lock hints
lock modes
Lock Modes
  • Shared locks (S)
  • Exclusive locks (X)
  • Lock compatibility
  • Update locks (U)
  • Intent locks (IS, IX, SIX, IU, SIU, UIX)
lock modes contd
Lock Modes contd.
  • Schema locks (Sch-S, Sch-M)
  • Bulk Update locks (BU)
  • Key Range locks (RangeS-S, …)
  • Lock hints
viewing locking information
Viewing Locking Information
  • Pre 2005
    • sp_lock
    • sp_who / sp_who2
    • DBCC INPUTBUFFER / fn_get_sql
  • SQL 2005
    • sys.dm_tran_locks
    • sys.dm_exec_connections
    • sys.dm_exec_requests
    • sys.dm_exec_sql_text
concurrency effects
Concurrency Effects
  • Lost updates
  • Uncommitted dependencies (Dirty reads)
  • Inconsistent Analysis (Non repeatable reads)
  • Phantom Reads
concurrency model
Concurrency Model
  • Pessimistic – use locks
    • Lower concurrency
    • Blocking
    • Deadlocking
  • Optimistic – use snapshots
    • Higher concurrency
    • Can be resource intensive
    • May need to manage conflict
deadlocks
Deadlocks
  • Lock wait <> Deadlock
  • Deadlock is an irresolvable chain of blocking
  • Lock manager automatically resolves deadlock by selecting deadlock victim
  • Most deadlock situations can be architected out
  • Resolving deadlocks
transaction bits and pieces
Transaction Bits and Pieces
  • LOCK_TIMEOUT
  • Read-only filegroups
  • Nested transactions
  • Save points
  • Transaction marks
  • XACT_ABORT
  • @@TRANCOUNT
exception handling
Exception Handling
  • Error vs. exception
  • Severity vs. error number
  • Some errors are too severe to handle
  • User defined errors
exception handling with the @@family
Exception handling with the @@family
  • @@ERROR and @@ROWCOUNT are volatile
  • Limited handling capabilities
  • Limited information available
  • Repetitive code blocks
  • Unstructured code with GOTO
  • Potential need to manage open transaction in caller
try catch methodology
TRY/CATCH Methodology
  • Put suspect code in TRY block
  • Put exception handling in CATCH block
  • CATCH block directly follows TRY block in same batch
  • Manage many more exceptions
  • Throw error to caller using RAISERROR
  • Exception handling functions provide detail of exception
exceptions in transactions
Exceptions in Transactions
  • XACT_STATE

0 – no active transactions

1 – open committable transaction

-1 – open doomed transaction

transactions and exception handling2
Transactions and Exception Handling
  • Transactions
  • Lock Manager
  • Locks, lockable resources and modes
  • Managing locks
  • Concurrency vs. Isolation
  • Blocks and Deadlocks
  • Old School Exception Handling
  • TRY/CATCH
  • Managing Exceptions in Transactions
resources and contact details
Resources and Contact Details
  • Resources
    • Inside MS SQL Server 2005 Series
      • T-SQL Programming
        • Itzik Ben-Gan et al.
      • The Storage Engine
        • Kalen Delaney
    • Books Online
  • Contact
ad