SQL Server 2000 Transaction Processing

1
SQL Server 2000Transaction Processing

• Don Vilen
• Program Manager
• SQL Server Development Team

2
Agenda

• SQL Server Overview
• SQL Server Architecture
• Storage and Access Methods
• Query Processing and Optimization
• Transaction Processing
• Other Topics

3
Transaction Processing
4
Objective

• Provide concurrency and isolation background
• Describe SQL Server row level locking
  implementation
• Describe other concurrency features
• Present tips and techniques for understanding and
  resolving locking issues

5
Isolation in SQL Server

• Isolation concepts
• Multi-granular locking
• Key range locking
• Page versus row locking

6
ACID Transaction Properties

• Transaction properties
• Atomic
• Consistent
• Isolated
• Durable
• Consistency and isolation
• Application defines consistency
• Application requires isolation to achieve
  consistent results
• Locking typically used to achieve isolation

7
Isolation The dependency model

• Think of each transaction as reading a set of
  objects and writing a set of objects
• One way to ensure each transaction reads
  consistent inputs and writes consistent outputs
  is to run each transaction to completion serially
• Serialized
• Input set is stable since the transaction is the
  only one active
• Output (writes) cant interfere since theres no
  other activity
• We can do better than this

T1
T2
T3
T4
8
Isolation The dependency model

• A set of transactions can run concurrently if
  their outputs are disjoint from the union of one
  anothers input and output sets
• I.e., if T1 writes some object thats in T2s
  input or output set theres a dependency between
  T1 and T2, expressed as T1 ? T2

T1 Writes EmpId 32
32, Smith, Don, 30,000
T2 Reads or Writes EmpId 32
T2 is dependent on T1
T1 ? T2
9
Isolation The dependency model

• So how do we schedule transactionssuch that we
  obey the dependencies?
• Locking enforces dependencies allowing concurrent
  execution where possible

Equivalent serial history
10
The Three Bad Dependencies

• Lost update
• Dirty read
• Non-repeatable read
• Plus a 4th one
• The Phantom Problem

11
Lost Update
T1
X10
X25
Time
T2

• Classic unsynchronized update

12
Dirty Read
T1
X10
Time
T2

• Transaction T2 seeing effects of transaction that
  was never committed

13
Non-Repeatable Read
T1
X10
Time
T2

• Transaction sees only committed data but data
  changes when referenced multiple times

14
The Phantom Problem
T1
Smith,4
Time
Brewer,7
T2
Select count () where rank gt 3
Select count () where rank gt 3 2 rows returned
3 rows returned

• Transactions input set defined by a predicate
• Transaction should not see additions/deletions
  from input set
• Must prevent other transactions from inserting
  even if we dont see the effects!

15
IsolationRelaxing things

• True isolation is expensive in terms of
  concurrency
• Many systems allow application to choose the
  phenomena they will live with
• Trade off between correctness and concurrency
• ANSI SQL defines four distinct Isolation
  Levels
• SQL Server 7.0 release correctly implements all
  ANSI defined isolation levels

16
ANSI SQL Isolation Levels
Isolation Level
Phenomena Allowed
Read uncommitted
Dirty read, Non-repeatable read, Phantoms
Non-repeatable read, Phantoms
Read committed
Repeatable read
Phantoms
--
Serializable
17
Multi-Granular Locking
Table
T1 Locks table in X mode
Page
Page
Page
T2 Locks row in X mode
Both T1 and T2 update the same row thinking they
have it covered by locks Result Disaster
18
Multi-Granular Locking
T1 IX
T2 IS
Table
T1 IX
T2 IS
Page
Page
Page
T1 X
T2 S

• To lock a fine granule, we must place intent
  locks at higher granules!

19
MG Lock Modes

• Standard multi-granular lock modes

Mode Description S Share - used for
reading X Exclusive - typically used for
writing U Update - used to evaluate prior to
writing IS Intent Share - share locking at finer
level IX Intent Exclusive - X locking at finer
level SIX Share Intent Exclusive
20
What Do We Lock?

• How do you identify a lock?
• Lock resource
• Table Authors
• Page 23
• Row with Key 23812
• Lock manager knows nothing about resource format
  it simply does a memcmp()
• Lock resource format

Resource Type
Resource Specific Data
Database ID
21
Lock Resource Format

• Example resource formats
• Table
• Page
• RowID
• KeyRange

325658
5
5
Object ID
2328
6
5
File Page
File Page Slot on Page
232811
9
5
Object ID IndexId 6byte Hash
32565822341186
7
5
Resourcetype
Database ID
22
Row IdentifiersBase table organizations - Heap

• Fixed Row Identifier (RID)
• Rows identified by RID (8 bytes consisting of
  File, Page, Slot)
• RIDs do not change unless a row is deleted and
  reinserted elsewhere
• RIDs can be reused once the transaction that
  deletes a row commits
• RIDs used as lock resource

Fixed RID
23
Row IdentifiersBase table organizations
Clustered Index

• Clustered index
• Rows identified by unique clustering key
• Clustering key used as lock resource
• Non-unique clustered indexes still supported
  (SQL Server generates uniquifier if required)

Clustered index
24
Secondary Indexes

• Secondary indices
• Index terms consist of key, locator pairs
• Locators are stable with respect to base table
  organization (unlike 6.X)
• Locator may be either RID (Fixed RID) or
  clustering key (clustered index), possibly with
  partitioning information
• Unique key/locator pair used as lock resource

Secondary index
Key
Locator
Points to either
25
Index ChangesSecondary index lookup on key
Adams
Adams
6
Adams
6
Lewis
1
Smith
11
...
...
6
1
Lewis
Dan
Adams
Kim
11
Smith
Ken
Clustering index data
Key
Locator (clustering Key)
26
Generating Key Resources

• Lock resource is six byte hash generated from all
  key components
• User defined
• Clustering key
• Uniquifier
• Collisions are rare but okay!
• Not invertible

Adams
6
Adams
Hash
0x033807FF9B2C
27
Key Range Locking

• To support serializable transaction semantics
• Need to lock sets of rows specified by a
  predicate
• Where salary between 30,000 and 50,000
• Need to lock data that doesnt exist!
• If where salary between 30,000 and 50,000
  doesnt return any rows the first time I ask, it
  shouldnt return any on subsequent scans
• Page locking prevents phantoms by locking the
  entire page the phantom would exist on
• We can do better than this

28
Key Range Locking

• A range is defined as an open interval between
  instances of rows at the leaf level of an index
• Is described in interval form as
• (0,Al (Al,Bob (Bob,Dave (Dave,Harry
• To lock a key-range (ki,ki1 we associate a lock
  with the key value ki1

Al
Bob
Dave
Harry
29
Key Range Locking

• Key range lock modes
• Special composite lock modes that include both a
  range and row component specified in range-row
  format
• Range Row Mode Comment
• - S S Share row lock
• IS S IS-S Serializable range scan
• Difference between a row and range lock is simply
  the lock mode - the resources are the same
• Only thing that changes are the lock manager mode
  compatibility tables

30
Key Range Locking

• Serializable scan
• where fname between B and C
• Results in the following locks
• Bob - IS-S
• Dave - IS-S

Al
Bob
Dave
Harry
Row Part Prevents modification of row
Range Part Prevents inserts into the range
31
Key Range Locking

• Serializable fetch of non-existent data
• where fname Bill
• Results in the following locks
• Bob - IS-S

Al
Bob
Dave
Harry
Row Part Prevents modification of row
Range Part Prevents inserts into the range
32
Key Range Locking

• Handling unique values
• delete where fname Bob
• Results in the following locks
• Bob X
• Need to prevent adding Bob until deleting
  transaction commits
• The Bob key is marked as deleted and cleaned up
  later

Al
Bob
Dave
Harry
33
RLL Index Operations

• Insert into a range

Al
Dave
34
RLL Index Operations

• Delete from an index (row locking)

1
Lock delete key in a mode that ensures were able
to delete - wait if necessary
2
Mark the key as deleted
2
Bob
Al
Dave
1
X
To commit the delete Release the lockTo roll
back the delete Remove the delete flag and
release lock
35
RLL Index Operations

• Delete from an index (page or table locking)

1
Lock page/table in a mode that ensures were able
to delete - wait if necessary
1
X
2
Remove the key from the page
2
Bob
Al
Dave
To commit the delete Release the lockTo roll
back the delete Put the key back and release
lock
36
Scan Locking

• Read committed
• Locks are released behind the scan allowing
  other transactions to update rows
• Rescan may encounter modified or deleted rows

3
4
6
3
4
6
37
Scan Locking

• Repeatable read
• Locks are retained on scanned rows
• Rescan may see new rows in range but scanned rows
  will not change

4
3
7
3
4
6
38
Scan Locking

• Serializable scan
• Locks are retained on scanned rows and on scanned
  ranges
• Prevents update and phantom insertions into
  scanned range

3
4
6
7
9
10
14
1
39
Dynamic Locking 1 of 4

• Row locking is not always the right choice
• Scanning a table with 100 million rows means 100
  million calls to the lock manager
• Sometimes page or table locking is the optimal
  (lowest cost) way to scan
• SQL Server 7.0 locking dynamically chooses the
  lowest cost locking strategy (Row, Page, or
  Table) at run time based upon input from the
  query optimizer
• Key differentiator!

40
Dynamic Locking 2 of 4
Concurrency cost
Locking cost
Cost
Row
Page
Table

• Row locks are great for concurrency but require
  lots of bookkeeping and lock manager calls
• Table locks dont allow much concurrency but are
  cheaper to acquire and manage
• Each has its place

41
Dynamic Locking 3 of 4

• When modifying individual rows
• Take row locks to maximize concurrency
• E.g., OLTP, order-entry
• When scanning larger volumes of data
• Take page or table locks to minimize cost of
  acquiring locks
• E.g., DSS, data warehouse, reporting
• Its all done automatically

42
Dynamic Locking 4 of 4
Isolationlevel
Scan type (Range, Table, Probe)
Locking strategy(Table, Page, Row)
of rows in scan
of rows/page
Operation type (scan, update)
43
Dynamic Locking Example

• Scan index and then lookup rows based upon scan
• Use page locks to scan index (contiguous compact
  range)
• Use row locks when reading from fixed-RID base
  table
• Minimizes locking cost, maximizes concurrency
• All done dynamically at run time

Secondaryindex
Base table
44
Access Methods ChangesB-tree split with page
locking

• Transaction consistent split
• Pages resulting from split are X locked until
  transaction end
• Could be 100s of records locked
• If transaction rolls back, split is undone

120 122 123 128 130
45
Access Methods ChangesB-tree split with row
locking

• Action consistent split
• System transaction performs B-tree physical
  changes under latches
• System transaction commits after split is done
• If user transaction rolls back, the split is not
  undone

120 122 123 128 130
46
New Lock Hints

• Granularity Hints
• ROWLOCK, PAGLOCK, TABLOCK
• Isolation Level Hints
• HOLDLOCK, NOLOCK
• READCOMMITTED, REPEATABLEREAD, SERIALIZABLE,
  READUNCOMMITTED All New
• READPAST
• Useful for implementing work queues
• Mode Hints
• XLOCK Hint (Shiloh)

47
READPAST Lock Hint

• Will skip rows that are currently row locked
• n worker threads picking up work from an input
  queue can each get their own item to work
• Select TOP 1 from work_queue (rowlock readpast
  updlock) order by entry
• Worker can delete and commit or rollback and let
  next worker try to complete the task

48
Application Locks

• User interface into SQL Lock manager
• New sp_getapplock, sp_releaseapplock system
  procedures
• USE Northwind
• DECLARE _at_result int
• EXEC _at_result sp_getapplock
• _at_Resource Form1,
•     _at_LockMode Shared
• Both Transactional and Session lifetimes
• Other benefits
• Deadlock detection, release at transaction/session
  end

49
Miscellaneous Stuff

• Lock time-out (7.0)
• User configurable lock time-out
• Choose not to wait at all
• SET LOCK_TIMEOUT 10000

50
Deadlock Detection

• SQL Server 7.0
• Changed from on demand to periodic detection
• Implemented flexible framework
• Only detected, locking deadlocks
• SQL Server 2000
• Deadlock detector framework extended
• Now detect any form of resource deadlock
• Locks, Memory, Threads,

51
Analyzing Concurrency Problems

• First step, find out whats blocked
• sp_who shows blocked/blocker information
• sysprocesses contains new columns to help
• waitresource, waittime, lastwaittype
• If waittype or waittime ltgt 0 then you have an
  active wait
• Otherwise, lastwaittype/waitresource indicates
  the last thing you waited on...

52
Using sp_lock

• sp_lock implemented using new syslockinfo virtual
  table
• use tempdbcreate table t1 (c1 int, c2 char
  (20))create index i1 on t1 (c1) ...begin
  tranupdate t1 set c1 c1 where c1 15exec
  sp_lockspid dbid ObjId IndId Type
  Resource Mode Status---- ---- -----------
  ----- ---- -------------- ---- ------2 1 0
  0 DB S GRANT6
  1 0 0 DB S
  GRANT7 2 0 0 DB
  S GRANT7 2 565577053 0 RID
  130414 X GRANT7 2 565577053 2
  PAG 1312 IX GRANT7 2
  565577053 0 PAG 1304 IX
  GRANT7 2 565577053 0 TAB
  IX GRANT7 2 565577053 2 KEY
  (0f0001003e01) X GRANT

53
Controlling lock granularity

• Can remove page and/or row locks from lock
  hierarchy using sp_indexoption
• Two scenarios
• Pure OLTP app. Look up and modify several rows
  gt May want to turn off page locks
• Look up table refreshed monthly from mainframe gt
  Turn off page and row locks

54
Common deadlocks

• Counter tables
• Small 10 row tables like
• In 6.x access was serialized due to page locking
  gt No deadlocks
• In 7.0 table scan results in deadlocks
• Solution Add index on Type

55
Common Deadlocks

• Index maintenance
• T1 holds S lock on index key and attempts to
  acquire S lock on base table row
• T2 hold X lock on base table row and attempts to
  acquire X lock to modify index
• Solutions
• Add additional columns to index to make covering
  for T1
• Partial ordering of access to base table

56
Other Tools

• SQL Server Profiler has been enhanced to capture
  lock events
• Primary way to view deadlock state
• Lock acquisitions, release, lock waits, etc.
• Can be used to capture deadlock events
• Use syslockinfo for querying lock information
• Much richer than previous syslocks
• See sp_helptext sp_lock for details on querying

57
Tracing Deadlocks

• Deadlock tracing can be enabled through trace
  flag 1204
• sqlservr.exe -c -T1204
• Deadlock output goes to errorlog

58
Deadlock Tracing

• Example output

Node1 TAB 21993058136 CleanCnt2
Mode IX Flags 0x0 Grant List
Owner0x2ba2f3a0 Mode IX Flg0x0 Ref3
Life02000000 SPID53 ECID0 SPID 53 ECID 0
Statement Type SELECT Line 1 Input Buf
select from t1 (tablockx) Owner0x2ba2f3c0
Mode IX Flg0x0 Ref4 Life02000000
SPID51 ECID0 SPID 51 ECID 0 Statement
Type SELECT Line 1 Input Buf select
from t1 (tablockx) Requested By
ResTypeLockOwner Stype'OR' Mode X SPID51
ECID0 Ec(0x2bb793c0) Value0x2 ba2f2e0
Cost(0/E4) Node2 TAB 21993058136
CleanCnt2 Mode IX Flags 0x0 Grant List
Owner0x2ba2f3a0 Mode IX Flg0x0 Ref3
Life02000000 SPID53 ECID0 Owner0x2ba2f3c0
Mode IX Flg0x0 Ref4 Life02000000
SPID51 ECID0 Requested By
ResTypeLockOwner Stype'OR' Mode X SPID53
ECID0 Ec(0x2bc3f3c0) Value0x2 ba341c0
Cost(0/E4)
59
How To Get Further Information And Resources

• Isolation concepts
• Transaction Processing Concepts and
  Techniques, Gray and Reuter, Morgan Kaufmann
• http//www.research.microsoft.com/Gray/

60
Questions?