1 / 21

Transactions and Exception Handling

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

waite
Download Presentation

Transactions and Exception Handling

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transactions and Exception Handling Eric Allsop SQLBits 6th October 2007

  2. 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

  3. What is a Transaction? • Atomicity • Consistency • Isolation • Durability

  4. Types of Transaction • Implementation • Auto commit • Implicit (IMPLICIT_TRANSACTION) • Explicit (BEGAN TRAN etc.) • Scope • Local • Distributed

  5. Transaction Processing

  6. Lock Manager • Internal SQL Server service • Manages access to resources • Services lock and latch requests • Enforces isolation level • In memory service • Fixed memory allocation

  7. 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

  8. Lock Modes • Shared locks (S) • Exclusive locks (X) • Lock compatibility • Update locks (U) • Intent locks (IS, IX, SIX, IU, SIU, UIX)

  9. Lock Modes contd. • Schema locks (Sch-S, Sch-M) • Bulk Update locks (BU) • Key Range locks (RangeS-S, …) • Lock hints

  10. 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

  11. Concurrency Effects • Lost updates • Uncommitted dependencies (Dirty reads) • Inconsistent Analysis (Non repeatable reads) • Phantom Reads

  12. Concurrency Model • Pessimistic – use locks • Lower concurrency • Blocking • Deadlocking • Optimistic – use snapshots • Higher concurrency • Can be resource intensive • May need to manage conflict

  13. Isolation Levels

  14. 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

  15. Transaction Bits and Pieces • LOCK_TIMEOUT • Read-only filegroups • Nested transactions • Save points • Transaction marks • XACT_ABORT • @@TRANCOUNT

  16. Exception Handling • Error vs. exception • Severity vs. error number • Some errors are too severe to handle • User defined errors

  17. 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

  18. 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

  19. Exceptions in Transactions • XACT_STATE 0 – no active transactions 1 – open committable transaction -1 – open doomed transaction

  20. 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

  21. 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 • eric.allsop@imgroup.com

More Related