Transcript Presentation Title Up to Four Lines of Text. Lorem Ipsum Delorum
Locking – What Locking?
Currently Committed and other Lock Avoidance Enhancements
Mike Winer
IBM [email protected]
Session Code: D10 May 13, 2010. 9:45 - 10:45 Platform: DB2 for LUW
Agenda
• • • • • • • •
Locks and Memory
Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
Locks – What “things” does DB2 lock?
• • • • • • • • •
Table, Table Partition Block Row
Table space, object table Catalog/Package cache Package/Variation lock Table alter, sequence, long (no LOB as of DB2 9.5) Online backup Many others for internal concurrency control
Lock Resource Block - LRB
• • Each distinct lock in the database consists of an LRB at the database level, containing the "union" or summary of all information from the transactions • Name, group mode, link to owning transactions, many others Each transaction has its own LRB with transaction specific information • Duration/count, mode, hold count, attributes, cursor flags, many others Architecture Word Size 32-bit 64-bit
DB2 9.7 (all)
LRB Size 48 bytes 64 bytes
128 bytes
First Lock (uses 2 LRBs) 96 bytes 128 bytes
256 bytes
Subsequent Locks 48 bytes 64 bytes
128 bytes
• Recent history of LRB size increase • • DB2 V8 GA through to V8.2.2, and again in DB2 9 for 32-bit systems Prior to DB2 9, LRB's on 32-bit systems were 40 bytes •
DB2 9.7 increases to 128 bytes, locklist limit increase from 2GB -> 512GB
Configuration Parameter – Lock Memory Mgmt
• Database Configuration Parameter:
LOCKLIST
• Amount of memory to be used for locks (LRB’s) • Range 4 – 524288 (2GB) • • Unit is 4K pages Can be modified online • New AUTOMATIC setting in DB2 9.1
• • Default setting Allows dynamic management of memory along with at least 1 other AUTOMATIC memory setting • • Enables self tuning of MAXLOCKS (next slide)
Increased to maximum of 512GB in DB2 9.7
Configuration Parameter – Lock Memory Mgmt
• Database Configuration Parameter:
MAXLOCKS
• Maximum percentage of memory that a single transaction can consume of the LOCKLIST memory • Range 1 - 100 • • Unit is percentage Can be modified online • New AUTOMATIC setting in DB2 9.1
• Default setting • Requires LOCKLIST set to AUTOMATIC
Agenda
• • • • • • • • Locks and Memory
Isolation Levels
Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
DB2 Isolation Levels
• • • •
UR
• • – Uncommitted Read For read only statements, no row locking , no modifications See uncommitted changes, skip uncommitted deletes
CS
• • – Cursor Stability May lock rows, processes only committed data Default isolation level
RS
• • – Read Stability Like CS, but acquires and retains locks on qualifying rows until end of transaction Use when future actions (may) need to be taken on processed rows
RR
• • – Repeatable Read
Like
RS, but locks all rows seen and keeps locks until end of transaction Use only when consistent results are required, no change to results
DB2 and ANSI Isolation Levels Naming Differences and Anomalies
DB2 Isolation
Uncommitted Read (UR) Cursor Stability (CS) Read Stability (RS)
Repeatable Read (RR) ANSI Isolation
Read Uncommitted (Level 0) Read Committed (Level 1)
Repeatable Read (Level 2)
Serializable (Level 3)
Dirty Write Dirty Read Fuzzy Read Phantom Read
Selecting an Isolation Level
• • •
Selecting isolation level must be based on functional requirements, not performance considerations Locking controls and dictates concurrency, each isolation level requires different locking If it doesn't work, does it matter how fast it is?
• • • What behavior does my application, session, transaction, or statement require?
My isolation level dictates my behavior, but also affects other transactions executing concurrently Isolation clause is now available at sub-select level
DB2 Doesn’t Always Respect Isolation Level
• • • • UR upgraded to CS for non-read only statements • • Searched UPDATE/DELETE/MERGE statements Cursors defined FOR UPDATE (or possibly ambiguous) Internal access to user tables for maintenance • • Referential Integrity, parent/child validation/enforcement MQT maintenance and refresh Internal access to DB2 catalog tables will use whatever isolation level (CS, RS, or RR) is required • • SQL compilation DDL Utilities
The Access Plan - Effect on Locking
• An access plan is the set of operations chosen by the DB2 compiler to satisfy and execute an SQL statement • Different access plans cause access to different data, the granularity and order in which data is accessed, and sometimes the lock hierarchy/modes used
Plan Type
Table Scan Index Scan Block Index Mostly Index - Fetch Block Index - Fetch Deferred Fetch
Description
Each page of the table is read, and every row in the table is read and considered. There may be block predicates to filter unwanted blocks (MDC tables only) and/or data predicates to filter unwanted rows.
The index is scanned from the beginning / start key until the end / stop key. There may be index predicates to filter unwanted rows. RID Index only access does not require evaluation or data from the data rows themselves.
An Index Scan (BLOCK based index, no data rows required), but due to potentially empty blocks in block indexes, a single page access to the block must be done to see if the block is empty or not (for "Exists" or Deferred Fetch).
An Index Scan (RID based index), then for qualifying rows from the index the row is fetched from the table, where there may be data predicates to further filter the rows.
An Index Scan (BLOCK based index), then for qualifying blocks from the index the pages of the block are scanned (block scan) where there may be additional block or data predicates to filter the entire block or individual rows.
RIDs and/or BIDs (from any variety of previous access plans) are provided to access the data rows. This is done when the RIDs/BIDs were qualified against multiple indexes (ANDing/ORing), or sorted for more efficient access.
Agenda
• • • • • • • • Locks and Memory Isolation Levels
Locking Registry Variables
Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
REGVAR - No Change to Isolation Semantics
•
Registry variable DB2_SKIPINSERTED=ON
• Allows statements using CS or RS isolation levels to skip uncommitted inserted rows as if they had not yet been inserted •
Implemented through lock attributes and feedback on locks
• INSERT lock attribute is visible in snapshot output • Lock Attributes = 0x00000008 •
Applies to newly allocated blocks during insert into MDC tables when LOCKSIZE of table is BLOCKINSERT
REGVAR - Change to Isolation Semantics
•
Registry variable DB2_EVALUNCOMMITTED=ON
• Allows statements using CS or RS isolation levels to defer or avoid locking until a row/key is known to satisfy predicates • Deleted rows are skipped unconditionally on table access, but deleted keys only skipped when DB2_SKIPDELETED is used •
Queries behave similar to UR isolation until row/block qualifies
• Only use if application behavior is well known and can tolerate skipping rows due to uncommitted updates and deletes • Can also be described as “assume COMMIT” for evaluation • Will
not
process uncommitted data, only evaluate uncommitted •
Block locking is deferred on block indexes until index predicates
REGVAR - Change to Isolation Semantics
•
Registry variable DB2_SKIPDELETED=ON
• Allows statements using CS or RS isolation levels to unconditionally skip deleted rows and deleted keys •
Queries will not test for “commit” of deleted rows/keys
• Only use if application behavior is well known and can tolerate skipping rows due to uncommitted deletes and updates (index) • Will
not
process uncommitted data, only skip deletes •
Is not implemented based on locking and lock attributes
• don't confuse with DELETE lock attribute, which identifies a possible deleted key but no guarantee for a deleted key/row • Lock Attributes = 0x00000020
Registry Variable Applicability
•
Registry Variables
• • •
DB2_SKIPINSERTED DB2_EVALUNCOMMITTED DB2_SKIPDELETED
•
Do not apply and are not respected for some CS/RS use cases
• • Access to DB2 catalog tables, explicit or internal Scans for insert, update, delete for integrity processing, such as RI cascade/validation or MQT maintenance • Type-1 index access (DB2_EVALUNCOMMITTED) • NOTE: Type-1 index support is discontinued in DB2 9.7!
Agenda
• • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables
Currently Committed (new to DB2 9.7!)
SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
Currently Committed (CC) – New CS Semantics
Motivation and value proposition for
Currently Committed
Oracle Snapshot Isolation DB2 V9.5 and earlier CS Isolation DB2 9.7
CS Isolation w/CC
Blocks ->
Reader Writer
Blocks ->
Reader Writer
Blocks ->
Reader Writer Reader No
No
Reader No
Yes
Reader No
No
Writer
No Yes
Writer
Maybe Yes
Writer
No Yes
•
Readers don’t block writers (readers avoid locking)
•
Writers don’t block readers (readers bypass locks)
Currently Committed: How it works
emp rowid 3 4 1 2 5 empid 4245 6354 7836 1325 5456 name Jones Smith Chan Tata Baum office Y2/11 A1/21 C3/46 X1/03 D2/18 salary 11 43 21 33 22 1 3 5
Locklist
rowid lock X(D) X(U) X(I) log
Log Buffer DEL: Emp,1,4245,Jones,Y2/11,11 Log Files UPD: Emp,3,7836,Chan,D2/18
C3/46 INS: Emp,5,5456,Baum,D2/18 Log Archive
(
TSM
) • •
INS:Emp,2,6354,Smith,A1/21, 43 INS:Emp,4,1325,Tata,X1/03,33
Lock Avoidance is applied first • no locking for known committed data Currently Committed uses lock manager to store information to allow access to currently committed version of the data/row
How To Get/Request Currently Committed
•
CUR_COMMIT
• • •
ON AVAILABLE DISABLED
database configuration parameter : default for new DB’s created in DB2 9.7 - all read only CS is CC : need to request CC (below – similar to planned DB2/z support) : default value on upgrade doesn’t support CC, even explicit requests •
BIND option
>--+-------------------------------------------------------------+--> '--CONCURRENTACCESSRESOLUTION--+--USE CURRENTLY COMMITTED--+--' '--WAIT FOR OUTCOME---------' •
PREPARE attribute (DB2 z/OS only)
• • • CLI/JCC have hooks to specify these options at connection level • CLI connection attribute SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION CC does not apply to target table of write, catalog tables, “integrity scans” • Target table of write still exhibit “skip uncommitted inserts” aspect of CC CC extends “skip uncommitted inserts” to Read Stability isolation as well • When using CC, no longer require DB2_SKIPINSERTED registry variable
What CC activity is going on?
•
db2pd –d
• Will indicate the type and number of reads from log. Total Disk=LogBuffer. Increasing the log buffer size may be able to achieve a reduction of Disk Log Reads.
• • Cur Commit Disk Log Reads 7 Cur Commit Total Log Reads 24 •
db2pd –d
• Will indicate, for each table partition, how many times the CC image was retrieved from the log • • Directly represents how many lock waits were avoided Does not include the number of lock waits avoided due to skipping uncommitted inserted rows – no log reads for this
• • •
CC Interaction With Existing Registry Variables
DB2_SKIPINSERTED
• Allows CS/RS scans to skip uncommitted inserted rows
DB2_SKIPDELETED
• Allows CS/RS scans to skip uncommitted deleted rows and index keys (index updates = delete + insert)
DB2_EVALUNCOMMITTED
• Allows CS/RS scans apply and perform query predicate evaluation on uncommitted data. Also allows the scans to skip uncommitted deleted rows • In effect, treats the scan as UR until it finds a qualifying row, at which time it may need to lock to ensure only committed data is processed/returned
Registry Variable DB2_SKIPINSERTED Implicit CC Explicit CC
CS Read Only
Implicit CC
CS Write RS Read/Write
Explicit CC
CS Write RS Read/Write N/A (included) N/A (included) N/A (included)
Wait For Outcome
CS/RS No
DB2_SKIPDELETED DB2_EVALUNCOMMITTED
No (n/a) No (n/a) Yes Yes No No No No
Agenda
• • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!)
SQL options
Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
Granularity of Locking
•
ALTER TABLE
• • Granularity of the lowest possible locking on the table Default value when tables are created is ROW •
Use to avoid finer grained locking codepath and to reduce LRB memory, but can lead to concurrency issues at higher levels in lock hierarchy
• Less ideal alternative to modify applications to issue LOCK TABLE statements • •
Set to TABLE to have all table access use S or X table locks with no subordinate locking in the hierarchy required
• Ideal for read only tables, or non-concurrent batch table updates
Set to BLOCKINSERT for MDC tables to have blocks locked X instead of IX, eliminating the need for X row locking on insert
• Great for bulk insert workloads, even concurrent insert transactions
FOR READ ONLY or FOR UPDATE?
•
Not specifying on SELECT indicates ambiguous
• • ORDER/GROUP BY, cursor blocking, etc – can be READ ONLY Best to specify one of
FOR READ ONLY
or
FOR UPDATE [OF ...]
• •
FOR READ ONLY
always gives optimizer best choice of plans, cursor blocking enablement,
lock avoidance!
FOR UPDATE [OF …]
indicates to optimizer updates may be made; impacts lock mode selection and plan selection •
SELECT INTO … FOR UPDATE [OF …]
• • • SELECT INTO is a read only statement, returns a single row FOR UPDATE clause on SELECT INTO is
new to DB2 9.7
U locking without use of WITH [RS|RR] USE AND KEEP clause
Specifying Lock Mode
• •
USE AND KEEP {SHARE|UPDATE|EXCLUSIVE} LOCKS
• Part of isolation level clause, for RS and RR only Allows fetch/read only cursors to benefit from BLOCKING while still U or X locking. FOR UPDATE clause disables BLOCKING!
• • Avoids lock waits/deadlocks on subsequent UPDATE/DELETE statements Can force X locking with EXCLUSIVE, no lock conversion when using the same cursor (i.e.. WHERE CURRENT OF) • Can override FOR UPDATE use of U locks with SHARE •
Examples:
• DECLARE c1 CURSOR FOR SELECT col1, col2 FROM mytable WHERE col1 BETWEEN 1 AND 3 FOR FETCH ONLY WITH RR USE AND KEEP UPDATE LOCKS • DECLARE c1 CURSOR FOR SELECT col1, col2 FROM mytable WHERE col1 BETWEEN 1 AND 3 FOR UPDATE WITH RS USE AND KEEP EXCLUSIVE LOCKS • DECLARE c1 CURSOR FOR SELECT col1, col2 FROM mytable WHERE col1 BETWEEN 1 AND 3 FOR UPDATE WITH RS USE AND KEEP SHARE LOCK
Override all Registry Variables, Configuration, Connection & BIND Options, Lock Avoidance
• • Lock avoidance has (positive) impact to query results!
• Internal scans and catalog access do not respect such optimizations, ensuring all in flight changes are visible • Only
Repeatable Read
isolation level can avoid for user queries How can applications or tools get old/original query results via locking every
potential qualifying row
using
CS
or
RS
isolation?
•
SELECT … WAIT FOR OUTCOME
(new to DB2 9.7 FP2) • • SQL syntax right on the statement, overrides all registry variables, configuration parameters, connect attributes, bind options No lock avoidance – will block on any incompatible lock • DB2 uses internally in ADMIN_MOVE_TABLE stored procedure
Release Locks Prior to End of Transaction
• • • •
CLOSE
• • attempt to release read locks acquired by cursor on CLOSE RS/RR isolation level is provided only while cursor is open • non-repeatable read and phantom rows are possible possible after CLOSE and before COMMIT
Has no effect when closing cursors defined in functions or methods, or in procedures called from functions or methods Implemented using bits in the cursor flags visible in lock snapshot
• E.g. Release Flags = 0x00000001 (30 bits per connection) • • bit value of 0x4000000 indicates lock is not being tracked Cursors not closed WITH RELEASE reserve bit for UOW
DB2's SQL compiler uses this technology to remove catalog locks acquired during statement compilation (bit value 0x80000000)
Agenda
• • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options
Lock Avoidance – the new standard
Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
Deferring and Avoiding (Row) Locks
• • • • Data rows and Index entries (RIDs/BIDs) have a flags byte, includes a “PUNC” bit • • PUNC = Possibly UNCommitted If PUNC bit not set, guaranteed committed, otherwise not known Pages have a “pageLSN”, the LSN (Log Sequence Number) of the log record which last modified the page • If pageLSN is older than DB commitLSN or table readLSN, then the row/key is guaranteed committed, otherwise only lock can tell us • commitLSN – first LSN of oldest active and uncommitted transaction • readLSN – tracks age of oldest lock/scan on table based on log activity PUNC bits are reset on scans if/when commit state is determined DB2 defers/avoids locking for CS/RS when PUNC bit is not set • • For read only CS, can avoid entirely if client is not dependant on lock
New to DB2 9.7 – All CS/RS access until row is known to qualify
Evaluate Uncommitted for ISARG Predicates
• • • • Indexes contain key values, committed or uncommitted • Key values seen by queries
are
the potential values CS/RS queries will now evaluate ISARG predicates prior to locking, avoiding locks on non-qualifying entries • • Improved concurrency and performance Only when predicates are not on include columns Locks tested/acquired if necessary after qualifying • Not allowed to process/return uncommitted data Eval-uncommitted can be found in output of db2expln | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability |
Evaluate Predicates Before Locking for Key
Avoid Index Next Key Locks When no RR Exists
• • Inserting keys into indexes need to test lock on next index entry to determine if it is locked by an RR scanner • Cannot insert/commit new entry and cause
phantom
to RR scan Lock test consumes CPU, even when no conflict occurs • When RR conflict occurs, must unlatch leaf page and wait on lock •
New to DB2 9.7
,
RR Scan Attribute
• • 1 st at table level RR scanner sets in memory bit (never unset – only set) Check on insert, if never an RR scan - next key lock test avoided •
New to DB2 9.8
,
RR Existence Lock
• • at table level RR scanners acquire RR Existence Lock, release at end of UOW If never an RR scan, or if no current RR scan, avoid next key lock
rrIID Helps Prevent False Lock Conflicts in Index
Table T1 f f
Common behavior prior to and in DB2 9.7
•
Tran 1: SELECT * FROM T1 WHERE C1 > 4 AND C1 <= 6 WITH RR
S row lock on RID R3 with RR lock attribute, also on RID R4 (end of range lock) • •
Tran 2: INSERT INTO T1 VALUES (3, ‘e’)
New RID R5, Table T1 has RR scan Insert key value
3
into IID=1, next key (4) RID R2 is not locked with RR attribute R5 3 e
C1 IID=1
2 4 6 8 R1 R2
R3 R4 Scan C2 IID=2
b d f h
R4
R1
R3
R2
Insert Prior to DB2 9.7
Tran 2:
• Insert key value
’e’
into IID=2, RR next key lock on R3 (f), wait for lock R3 held by tran 1 •
False Lock Conflict – insert would not create phantom for RR scan by Transaction 1 New to DB2 9.7
Tran 1:
• RR lock on RID R3 contains the new
rrIID attribute of 1
•
rrIID
is the IID of the index used by the RR scan
Tran 2:
• Insert key value
’e’
into IID=2, RR next key lock on R3 (f) with
rrIID=1
•
Since this is index with IID=2, insert proceeds, no lock wait
Agenda
• • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard
Lock Escalation, Timeout, and Deadlock
Monitoring: Lock Events and Functions
Lock Escalation
• • • • Lock requests receive an escalation warning/trigger when either: • • Total DB lock memory usage is approaching LOCKLIST Application usage of total DB lock memory exceeds MAXLOCKS Escalation is the process of replacing lower granular locks with a non-intent lock on an object higher in the hierarchy • The goal is to reduce the number of locks by 50%, long (LONG VARCHAR/GRAPH) locks are escalated first, these control space reuse vs. concurrency • row/block locks then removed and replaced by an S or X table level lock, freeing up LRB memory used for the low level locks. Descending based on total locks per table/partition Knowledge of lock escalations in a variety of locations • • • Messages in db2diag.log
Snapshot monitor (database and application – total and exclusive (X) escalations) ESCALATED attribute (0x00000002) in lock snapshot, db2pd Possible actions to help reduce lock escalations • • AUTOMATIC for
locklist
and
maxlocks
(or increase hard coded values) – next slide Use of LOCKSIZE, choice of isolation level, frequency of COMMIT
Lock Waits and Deadlocks
•
Lock Wait
- when an unconditional lock request/upgrade must wait on another transaction (compatibility) •
LOCKTIMEOUT
• • db config parm Default is -1, unlimited wait Range 0..32767 seconds • •
CURRENT LOCK TIMEOUT
register to override db cfg • • • special NULL indicates to use DB config Values -1 to 32767 as with DB config Only table, block, and row locks!
DB2LOCK_TO_RB
• • registry variable Default is to ROLLBACK transaction If set to STATEMENT, a lock timeout will result in statement failure only • • •
Deadlock
– when 2/more transactions are in lock wait on one another, and a lock timeout does not break the cycle
DLCHKTIME
• • db config parm Default is 10000 milliseconds (10s) Range is 1000..600000 milliseconds Deadlock detector (db2dlock) • victim to ROLLBACK will check every DLCHKTIME interval for a deadlock cycle between transactions If a cycle is found to exist, it will choose a • Local deadlock detector for each database partition, global deadlock detector for a multi-partition database
Agenda
• • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks
Monitoring: Lock Events and Functions
New LOCKING Event Monitor in DB2 9.7
• • • • CREATE EVENT MONITOR
MON_DEADLOCK
-> WITHOUT_HIST • •
MON_LOCKTIMEOUT MON_LOCKWAIT
-> WITHOUT_HIST -> NONE Workload settings (CREATE/ALTER WORKLOAD) can override/
increase
DB configuration settings Extract event information from Unformatted Event TABLE via: • db2evmonfmt java tool • EVMON_FORMAT_UE_TO_XML table function • EVMON_FORMAT_UE_TO_TABLES stored procedure
Deadlock Event Monitor
• •
How can one determine what locks, applications, statements, and host variable values were involved in a deadlock?
The DEADLOCK EVENT MONITOR >>-CREATE EVENT MONITOR--event-monitor-name--FOR----------------> >----DEADLOCKS--+---------------------------------------+-------> '-WITH DETAILS--+---------------------+-' '-HISTORY--+--------+-' '-VALUES-' >--*--WRITE TO--+-TABLE--| Table Options |----------+--*--------> +-PIPE--pipe-name-------------------+ '-FILE--path-name--| File Options |-' .-MANUALSTART-.
>--+-------------+--*-------------------------------------------> '-AUTOSTART---'
• •
A default of DEADLOCK WITH DETAILS, WRITE TO FILE (
Deadlock Event Monitor
• • • • WITH DETAILS • • • - all locks for transactions in the deadlock cycle HISTORY - not just current statement, all statements in UOW VALUES - not just statements, values in parameter markers too!
extra MONHEAP usage, rolling window of 250 statements per UOW SET EVENT MONITOR • •
Lock Timeout Reporting Tool
• • • •
DB2_CAPTURE_LOCKTIMEOUT=ON registry variable setting will cause a lock timeout report to be dumped to a timestamp based filename
• • V8.1 fp16, V9.1 fp4, V9.5 GA Flat file containing ASCII text Documented in DB2 9.5 documentation: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0052973.html
Placed in the diagnostic data directory path • Controlled by DIAGPATH database manager configuration parameter Report name format is: db2locktimeout.
par
.
AGENTID
.
yyyy-mm-dd-hh-mm-ss
• • •
par
is the database partition number (000 for non-DPF).
AGENTID
is the agent ID.
yyyy-mm-dd-hh-mm-ss
is the time stamp of the lock timeout.
• If there is an active deadlock event monitor with history enabled, then the lock timeout report is supplemented with statement history
Monitoring Locks
• GET SNAPSHOT
FOR LOCKS
and
LOCKS FOR APPLICATION
are no longer enhanced, still available • SNAP_GET_LOCK and SNAP_GET_LOCKWAIT functions (and associated views, which use snapshot) are now deprecated, no longer enhanced, still available • New monitor table functions introduced in DB2 9.7
• •
MON_GET_LOCKS MON_GET_APPL_LOCKWAIT
•
MON_FORMAT_LOCK_NAME
MON_GET_LOCKS Table Function
•
MON_GET_LOCKS (search_args, member)
• Search_args (and member) allows filtering on data extraction (front end) vs. using query predicates (back end).
• application_handle • lock_name / lock_object_type • lock_mode / lock_type • table_schema / table_name •
SELECT lock_name, lock_status, application_handle FROM TABLE (MON_GET_LOCKS( CLOB('
• This query returns the following output: LOCK_NAME LOCK_STATUS APPLICATION_HANDLE -------------------------- ----------- ----------------- 00030005000000000280000452 W 12545 00030005000000000280000452 W 34562 00030005000000000280000452 G 65545 00030005000000000280000452 W 47111
MON_GET_APPL_LOCKWAIT MON_FORMAT_LOCK_NAME
•
MON_GET_APPL_LOCKWAIT (application_handle, member)
• Retrieve information for all locks being waited on or specific to an application and/or member •
MON_FORMAT_LOCK_NAME (lockname
) • • Lockname can be from MON functions, or from db2notify/diag log files Returns applicable key-value pairs • NAME (VARCHAR 256) – element of the lock name • VALUE VARCHAR(1024) – value of the elemnt • Example elements include: • lock_object_type • tabschema, tabname • tbsp_name, data_partition_id • rowid, pageid • Others depending on lock_object_type
Using db2pd to View Locks
• db2pd -db
Mike Winer [email protected]
Agenda
• • • • • • • • • Locks and Memory Isolation Levels Locking Registry Variables Currently Committed (new to DB2 9.7!) SQL options Lock Avoidance – the
new
standard Lock Escalation, Timeouts, and Deadlocks Monitoring: Lock Events and Functions
Optimistic Locking (new to DB2 9.5)
Optimistic Locking – New Progamming Model
Client 1 (Cursor Stab.)
SELECT c1,c2,…
ROW CHANGE TOKEN RID_BIT
(t1), FROM t1 WHERE … FOR t1,
DB2 Server Client 2 (Cursor Stab.)
SELECT c1,c2,…
ROW CHANGE TOKEN RID_BIT
(t1), FROM t1 WHERE … FOR t1, N Buy?
Y UPDATE t1 SET c1,… WHERE
ROW CHANGE TOKEN
AND
RID_BIT
(t1) = :hv2 = :hv1 COMMIT Y Buy?
N UPDATE t1 SET c1,… WHERE
ROW CHANGE TOKEN
AND
RID_BIT
(t1) = :hv2 = :hv1 (retry)
Optimistic Locking – New SQL (1)
• For best results, create tables with ROW CHANGE TIMESTAMP ALTER TABLE … ADD COLUMN …) • • • Implicitly hidden column (not visible to SELECT *) column (or If not present,
any
page update will change the ROW CHANGE TOKEN for
all
rows on the page All rows existing at the time of an added ROW CHANGE TIMESTAMP column will inherent a timestamp of 12am, Jan 1, 0001 CREATE TABLE t1 ( …, rct TIMESTAMP NOT NULL
GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
) •
ROW CHANGE TOKEN
– select & reuse results as a future predicate • Use to identify is target row is guaranteed to be the same as before (unchanged) or not • False Negatives can occur • No matching row found, but in fact row was not concurrently updated. Can occur when: • Different row on page updated, table doesn’t have ROW CHANGE TIMESTAMP • • NOT LOGGED INITIALLY transaction updated same page REORG moved row • False Positives
cannot
occur
Optimistic Locking – New SQL (2)
•
RID_BIT( • • • scalar function Use to significantly improve performance of initial and subsequent access No need to “over” select columns for subsequent row access • Extra columns often needed to re-access the same row (e.g. Primary key columns) • Extra columns often needed to verify same row is unchanged (e.g. all columns) Statement with “WHERE RID_BIT(T1) = :rb” dictates new access plan • Direct row/page access, guaranteed optimal plan, no setup and probe on index! • When combined with ROW CHANGE TIMESTAMP predicate, searched UPDATE/DELETE statement requires only 2 predicates and network traffic • ROW CHANGE TIMESTAMP • • clause Can use ROW CHANGE TIMESTAMP for historical queries Timestamp values in row may not be 100% accurate SELECT … WHERE ROW CHANGE TIMESTAMP FOR t1 <= CURRENT TIMESTAMP AND ROW CHANGE TIMESTAMP FOR t1 >= CURRENT TIMESTAMP – 30 days)