Locking – What Locking? Currently Committed and other Lock Avoidance Enhancements

Locking – What Locking?Currently Committed and otherLock Avoidance Enhancements Mike Winer IBM mikew@ca.ibm.com 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 • 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

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 LevelsNaming Differences and Anomalies

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

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!

Currently Committed (CC) – New CS Semantics Motivation and value proposition for Currently Committed • Readers don’t block writers (readers avoid locking) • Writers don’t block readers (readers bypass locks)

LogBuffer Locklist DEL: Emp,1,4245,Jones,Y2/11,11 rowid lock log emp 1 X(D) rowid empid name office salary LogFiles 3 X(U) 1 4245 Jones Y2/11 11 UPD: Emp,3,7836,Chan,D2/18C3/46 INS: Emp,5,5456,Baum,D2/18 2 6354 Smith A1/21 43 5 X(I) - 3 7836 Chan C3/46 21 LogArchive (TSM) 4 1325 Tata X1/03 33 INS:Emp,2,6354,Smith,A1/21, 43 INS:Emp,4,1325,Tata,X1/03,33 5 5456 Baum D2/18 22 Currently Committed: How it works • 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

CUR_COMMIT database configuration parameter ON : default for new DB’s created in DB2 9.7 - all read only CS is CC AVAILABLE : need to request CC (below – similar to planned DB2/z support) DISABLED : 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 How To Get/Request Currently Committed

What CC activity is going on? • db2pd –d <dbname> -logs • 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 <dbname> -tcbstats • 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

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 CC Interaction With Existing Registry Variables

Granularity of Locking • ALTER TABLE <tname> LOCKSIZE {ROW | BLOCKINSERT | 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 <cursor-name> WITH RELEASE • 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)

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 at table level • 1st 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 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 • 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 C1 IID=1 C2 IID=2 2 4 6 8 R1 R2 R3 R4 b d f h R4 R1 R3 R2 Scan Insert

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) • LOCKTIMEOUTdb config parm • Default is -1, unlimited wait • Range 0..32767 seconds • CURRENT LOCK TIMEOUTspecial register to override db cfg • NULL indicates to use DB config • Values -1 to 32767 as with DB config • Only table, block, and row locks! • DB2LOCK_TO_RBregistry 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 • DLCHKTIMEdb config parm • Default is 10000 milliseconds (10s) • Range is 1000..600000 milliseconds • Deadlock detector (db2dlock) will check every DLCHKTIME interval for a deadlock cycle between transactions • If a cycle is found to exist, it will choose a victim to ROLLBACK • Local deadlock detector for each database partition, global deadlock detector for a multi-partition database

New LOCKING Event Monitor in DB2 9.7 • CREATE EVENT MONITOR <name> FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE … • Events for: DEADLOCK, LOCKTIMEOUT, LOCKWAIT • New DB Configuration Parameters to support default events and level of content • MON_DEADLOCK -> WITHOUT_HIST • MON_LOCKTIMEOUT -> WITHOUT_HIST • MON_LOCKWAIT -> 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 DEADLOCK WITH DETAILS, WRITE TO FILE (<dbpath>/db2event/db2detaildeadlock), AUTOSTART deadlock event monitor is created for every new or migrated database in V8, with a name of DB2DETAILDEADLOCK • Check out SYSCAT.EVENTMONITORS

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<event monitor name>STATE = [1 | 0] • activates or deactivates an event monitor • not under transaction control (immediate, not dependent on COMMIT) • FLUSH EVENT MONITOR<event monitor name> • event data may still be in memory, this will flush it to the target • Easiest way to format and view event monitor output - db2evmon • db2evmon [-db]<database name>[-evm]<event monitor name> • db2evmon -path<event monitor target> • db2evmon -path /u/mikew/historydeadlock

Lock Timeout Reporting Tool • DB2_CAPTURE_LOCKTIMEOUT=ONregistry 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('<lock_name>00030005000000000280000452</lock_name>), -2)) • 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_LOCKWAITMON_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 <dbname> -locks [tran =<tranhdl>] [file=<filename>] [showlocks] [wait] hotel75:/home/hotel75/mikew> db2pd -d test -locks showlocks Database Member 0 -- Database TEST -- Active -- Up 0 days 00:03:46 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID 0x00002AC6F01374C8 3 02000500090000000000000052 Row ..S G 3 1 0 0x00000010 0x00000001 1 02000500090000000000000052 SQLP_RECORD (obj={2;5}, rid=d(0;0;9), x0900000000000000) 0x00002AC6F01373B8 3 41414141415A425ABEFE0CE0C1 Internal P ..S G 3 1 0 0x00000000 0x40000000 0 41414141415A425ABEFE0CE0C1 SQLP_PLAN ({41414141 5A425A41 E00CFEBE}, loading=0) 0x00002AC6F0138088 3 02000500080000000000000052 Row ..S G 3 1 0 0x00000010 0x00000001 1 02000500080000000000000052 SQLP_RECORD (obj={2;5}, rid=d(0;0;8), x0800000000000000) 0x00002AC6F0137BB0 3 02000500070000000000000052 Row ..S G 3 1 0 0x00000010 0x00000001 1 02000500070000000000000052 SQLP_RECORD (obj={2;5}, rid=d(0;0;7), x0700000000000000) 0x00002AC6F01372A8 3 02000500000000000000000054 Table .IS G 3 1 0 0x00002000 0x00000001 0 02000500000000000000000054 SQLP_TABLE (obj={2;5}) [1114] mikew @ q /home/mikew > db2pd -db test -locks wait

Mike Winermikew@ca.ibm.com

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 DB2 Server Client 1 (Cursor Stab.) Client 2 (Cursor Stab.) SELECT c1,c2,… ROW CHANGE TOKEN FOR t1, RID_BIT(t1), FROM t1 WHERE … SELECT c1,c2,… ROW CHANGE TOKEN FOR t1, RID_BIT(t1), FROM t1 WHERE … No row locks held Buy? Buy? Y N N Y Direct fetch Direct fetch UPDATE t1 SET c1,… WHEREROW CHANGE TOKEN = :hv1 AND RID_BIT(t1) = :hv2 UPDATE t1 SET c1,… WHEREROW CHANGE TOKEN = :hv1 AND RID_BIT(t1) = :hv2 COMMIT Row not found (retry)

Optimistic Locking – New SQL (1) • For best results, create tables with ROW CHANGE TIMESTAMP column (or ALTER TABLE … ADD COLUMN …) • Implicitly hidden column (not visible to SELECT *) • 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

Locking – What Locking? Currently Committed and other Lock Avoidance Enhancements