Best Practices for Optimizing Transactional Code in SQL

Best Practices for Optimizing Transactional Code in SQL Server 2008 Svetlin Nakov Director Training and Consulting Activities National Academy for Software Development (NASD) http: //academy. devbg. org

Agenda § ACID Transactions and Transaction Modes in SQL Server § Understanding Isolation Levels § Concurrency Problems § Locking vs. Versioning § Allowing Snapshot Isolation in SQL Server § SQL Server vs. Oracle Transactions 3

Transactions in SQL Server 2008

Database Transactions § Transactions are a sequence of modifications in the database executed as a single unit of work: § Either all of them execute successfully § Or none of them § Example: § A bank transfer from one account into another (withdrawal + deposit) § If either the withdrawal or the deposit fails the whole operation is cancelled 5

ACID Properties of Transactions § Atomicity § Either all modifications are performed, or none § Consistency § When completed, transactions leave all data and all related structures in a consistent state § Isolation § Transactions don't see other's transaction's uncompleted work (intermediate state) § Durability § Transactions persist despite of system failure 6

ACID Transactions in SQL Server § Handled automatically across databases on the same instance § Handled through MSDTC automatically or explicitly across instances § Controlled by Transaction Mode of the session § Can be defined explicitly, at connection level and at server level § Requires error handling logic § Programming errors do not cause transaction rollback unless XACT_ABORT setting on 7

Transaction Mode § Autocommit transactions (default) § Statement level implicit transaction § Each statement commits as a single unit § Explicit transactions (user-defined) § BEGIN TRANSATION § COMMIT / ROLLBACK TRANSACTION § Implicit transactions § Session Level Setting SET IMPLICIT_TRANSACTIONS ON 8

Concurrency Problems and Isolation

Concurrency Problems § SQL Server transaction isolation solves four major concurrency problems § Dirty read occur when a reader reads uncommitted data § Unrepeatable read occurs when existing row change within a transaction § Lost updates occur when two writers modify the same piece of state § Phantoms occur when new rows are added and appear within a transaction 10

Locking Strategies § Optimistic concurrency § Locks are not used – readers never wait § Conflicts are possible § Can be resolved before commit § High concurrency – scale well § Pessimistic concurrency § Use exclusive and shared locks § Transactions wait for each other § Low concurrency – does not scale 11

Isolation Levels and Locking § ANSI Isolation Levels § Level 0 – Read Uncommitted § Level 1 – Read Committed § Level 2 – Repeatable Reads § Level 3 – Serializable § Default isolation level in both SQL Server 2005/2008 is ANSI Level 1, Read Committed § In implementation this default level uses locking § Pessimistic concurrency 12

Isolation Levels and Concurrency in SQL Server 2008 Isolation Level Dirty Nonrepeat. Lost Phantom Concurrency Reads able Reads Updates Reads Model Read uncommitted Yes Yes Pessimistic Read committed snapshot No Yes Yes Optimistic* Read committed No Yes Yes Pessimistic Repeatable read No No No Yes Pessimistic Snapshot No No No** No Optimistic* Serializable No No Pessimistic * Optimistic concurrency uses row versioning instead of row locking ** Lost updates in snapshot isolation level are prevented by conflict detection instead of locking 13

Concurrency Problems Live Demo

Transactions Isolation: Locking vs. Versioning

Basic Locking Types § SQL Server insures isolation by means of locking § Write locks are exclusive locks, read locks (shared locks) allow other readers § A well-formed transaction acquires the correct lock type prior to using state § A two-phased transaction holds all locks until all locks have been acquired § Isolation levels determine how long locks are held 16

Traditional Transactions § SQL Server accomplishes isolation via locking § Writers wait on read locks § Both readers and writers wait on write locks § Variety of isolation levels supported § How long locks are held § What is locked (data and/or metadata) 17

Transactions and Consistency § SQL Server cannot guarantee statement-level consistency by locking § Even at serializable transaction level § Rows are locked as they are read § Rows can be changed after the execution of the reading statement begin § Read-consistency not guaranteed when multiple statements read multiple tables § e. g. data warehouse unload jobs § Readers cannot read old values when data is being updated 18

Transaction Isolation & Versioning § SQL Server 2008 can also use row versioning § Isolation accomplished by combination of locking and versioning § Write locks block other writers § Write locks do not block readers § Readers do not lock by default § Readers do not block writers by default § Known as snapshot isolation § Old rows kept in tempdb for reading § Eases conversion from versioning databases 19

Snapshot Reads § Snapshot always reads coherent data § No read lock needed even though data being changed § Version of each value maintained as long as needed § Transaction reads version of value corresponding to its start time 20

Snapshot Writes § Writes store the old version of changed rows in tempdb § This takes time and consume storage but allows better concurrency § Snapshot Transactions are ACID § Failures may be deferred § Write fails if version later than that when transaction started § Serialization isolation holds off transaction until safe to proceed 21

Snapshot Isolation Types § Two styles of snapshot isolation § Statement-level snapshot – like read committed § Consistent as of last statement § See changes others commit during transaction § Transaction-level snapshot – like serializable § Consistent as of beginning of transaction § Don't see changes others commit during transaction § Provides mandatory conflict detection 22

Using Snapshot Isolation § Must be explicitly enabled at database level § Set ALLOW_SNAPSHOT_ISOLATION option ON § master and msdb are snapshot enabled by default § Transaction isolation level must be SNAPSHOT USE master GO ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON GO. . . SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN 23

Using Statement-Level Snapshot Isolation § Must be explicitly enabled at database level § Set READ_COMMITTED_SNAPSHOT option ON § Transaction isolation level must be READ COMMITTED USE master GO ALTER DATABASE pubs SET READ_COMMITTED_SNAPSHOT ON GO. . . SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN 24

Snapshot vs. Serializable Isolation Levels Live Demo

Versioning Drawbacks § Versioning is not without cost § Takes up space in tempdb § Versions kept regardless of usage § Space usage must be planned § Up to twice as much I/O for updates § Readers must read through saved versions § More versions, slower reads § Conflict detection cause updates to roll back Msg 3960, Level 16, State 1, Line 1. Cannot use snapshot isolation to access table 'authors' in database 'pubs'. Snapshot transaction aborted due to update conflict. Retry transaction. 26

Snapshot Isolation And Locking § You can lock explicitly during snapshot transaction if needed § Use hint READCOMMITTEDLOCK § Permits combination of versioning and locking § Equivalent of "SELECT FOR UPDATE" § FOR UPDATE supported only in DECLARE CURSOR in SQL Server 27

SQL Server vs. Oracle Transactions § Oracle uses optimistic concurrency by default § Uses the REDO log (transaction log) to implement row versioning (save changes only) § Read locks (pessimistic concurrency) can be acquired explicitly only (SELECT FOR UPDATE) SQL Server Isolation Level Read uncommitted Oracle Isolation Level – SQL Server Concurrency Pessimistic Read committed snapshot Read committed Optimistic Read committed Pessimistic – Oracle Concurrency – Optimistic – Repeatable read (manual locks) Pessimistic Snapshot Serializable Optimistic Serializable (manual locks) Pessimistic – 28

Summary § Transaction Isolation with Locking (Pessimistic concurrency) § Data read as of exact point in time or locked § Four transaction isolation levels § READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE § Accomplished by adjustable lock type/duration § But. . . § No statement level read consistency and multi table read consistency within transaction § No way to read old value of row being updated 29

Summary § Transaction Isolation with Versioning (Optimistic concurrency) § Data read as of beginning of transaction § Two transaction isolation levels § SNAPSHOT and READ_COMMITTED_SNAPSHOT § Statement level and multi-statement read consistency § But. . . § Extra versions of data kept (extra I/O) § Update conflicts possible - rollback if snapshot is used 30

Optimizing Transactions Code in SQL Server 2008 Questions? 31