无聊之人--除了技术,还是技术,你懂得
分类: DB2/Informix
2016-02-14 14:55:22
在整理了DB2 LOG相关内容的基础之上,这章整理lock的内容,相比较与log,lock的内容更多的与应用相关,涉及内容方方面面更加复杂。DBMS在本质上不同于文件系统的地方在于DBMS系统所支持的事务。 如果log的引入是为了保证持久化,那么LOCK的引入就是保证事务串行化,解决事务的并发带来了资源的RACE CONDITION。不同的事务场景,定义了不同的并发需求,为此定义了4种事务的隔离级别,不同的DBMS引入了不同的锁机制来实现。DB2的串行化机制主要是通过LOCK,LATCH,CLAIM/DRAIM 来实现,具体的LOCK的相关属性以及LOCK属性对应用程序的影响比如OBJECT,SIZE,MODE,DURATION,PARTICIPANTS,PARAMETER LOCATIONS都进行了介绍。当引入了DBMS CLUSTER(DB2中为DATA SHARING GROUP,而ORACLE中为ORACEL RAC) 以后,为了处理不同不同MEMBER之间数据的一致性,DB2引入了PHYSICAL LOCK以及CF LOCK STRTURE 来实现全局LOCK 冲突检测。单个SUBSYSTEM中,主要的冲突有TIMEOUT,DEADLOCK,在DATASHARING GROUP,引入了新的CONTENTION,XES CONTENTION,FALSE CONTENTION,GLOBAL LOCK CONTENTION。引入LOCK的同时不可避免的带来了新的OVERHEAD,DB2主要通过IRLM,XES,CF等处理锁资源的请求,除了地址空间除了锁请求之外,由于应用本身设计不合理或是某些特定的场景带来了很多问题,如TEMEOUT,DEADLOCK,LOCK ESCALATION等,如何有效的避免这些问题,需要系统运维人员以及开发人员共同努力。
本文的行文脉络基于个人对DBMS LOCK的认知层次,行文的逻辑性,合理性,整理内容的知识面的广度和深度都有待进一步的思考。
这篇博客从落笔到完成大体的框架,持持续续时间已经接近2周,应该是自己耗时最长的一篇博客,纵使如此,每一次查看,发下仍有新的东西需要自己补充,等后续会继续补充自己的理解。
DBMS区别与文件系统的最本质区别:DBMS支持事务
DBMS :Allowing multiple users to access a database simultaneously without compromising data integrity.
A transaction (or unit of work) is a recoverable sequence of one or more SQL operations that are grouped together as a single unit, usually within an application process.
One of the mechanisms DB2 uses to keep data consistent is the transaction. A transaction or(otherwise known as a unit of work) is a recoverable sequence of one or more SQL operations that are grouped together as a single unit, usually within an application process. The initiation and termination of a single transaction defines points of data consistency within a database; either the effects of all SQL operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all SQL operations performed are completely "undone" and thrown away (rolled back).
事务并发带来的问题,为了解决这些问题,DB2定义了4种事务隔离级别。
Occurs when two transactions read the same data, both attempt to update the data read, and one of the updates is lost
Transaction 1 and Transaction 2 read the same row of data and both calculate new values for that row based upon the original values read. If Transaction 1 updates the row with its new value and Transaction 2 then updates the same row, the update operation performed by Transaction 1 is lost
Occurs when a transaction reads data that has not yet been committed
Transaction 1 changes a row of data and Transaction 2 reads the changed row before Transaction 1 commits the change. If Transaction 1 rolls back the change, Transaction 2 will have read data that theoretically, never existed.
Occurs when a transaction executes the same query multiple times and gets different results with each execution
Transaction 1 reads a row of data, then Transaction 2 modifies or deletes that row and commits the change. When Transaction 1 attempts to reread the row, it will retrieve different data values
Occurs when a row of data that matches some search criteria is not seen initially
Transaction 1 retrieves a set of rows that satisfy some search criteria, then Transaction 2 inserts a new row that contains matching search criteria for Transaction 1’s query. If Transaction 1 re-executes the query that produced the original set of rows, a different set of rows will be retrieved – the new row added by Transaction 2 will now be included in the set of rows returned
这里的object是指锁所施加的对象,不同的锁所能适用的对象是不同的。如PAGE LOCK的对象肯定是PAGE.
OWNER 表示谁持有锁,有的是TRAN,有的是DB2 MEMBER
LOCK PARTICIPANTS
Locking is a complex interaction of many parts.
Page/Row locks are not compatible with tablespace locks The solution is "Intent Locking" Regardless of the locksize, DB2 will always start with a tablespace lock
:IS/IX USED whenever page or row locks are being usedS | U | X | |
---|---|---|---|
S | Y | Y | N |
U | Y | N | N |
X | N | N | N |
持有数据的时间:commit,across commit(with hold)
FROM:START OF FIRST USE
TO :COMMIT OR MOMENTIALIY
When modifying data (Insert, Update and Delete),DB2 determines all locking actions
每一种事务隔离级别锁所能解决的问题
DECLARE upd_cur CURSOR FOR SELECT data1, data2 FROM table FOR UPDATE OF colx
Read-Only
DECLARE ro_cur CURSOR FOR SELECT DEPTNO, AVG(SALARY) FROM EMP GROUP BY DEPTNO
Ambiguous
DECLARE amb_cur CURSOR FOR SELECT data1, data2 FROM table
=> Use FOR READ ONLY or FOR FETCH ONLY下面分别介绍了系统参数,应用参数如何影响LOCK行为
OBJECT DEFINITION,CREATE TABLESPACE:
NOFOR Option:NO FOR update clause mandatory for positioned updates
STDSQL(YES): Implies NOFOR
ZPARM | MEANING |
---|---|
URCHKTH | UR Checkpoint Frequency Threshold |
URLGWTH | UR Log Write Threshold |
LRDRTHLD | Long-Running Reader |
RELCURHL | Release Held Lock |
EVALUNC | Evaluate Uncommitted |
SKIPUNCI | Skip Uncommitted Inserts |
RRULOCK | U lock for RR/RS |
XLKUPDLT | X lock for searched U/D |
NUMLKTS | Locks per Table(space) |
NUMLKUS | Locks per User |
BETWEEN SQL AND SQL: DB2 USE LOCKS
BETWEEN UTILITY AND SQL:DB2 USE CLAIM AND DRAIN
BASE:
FOR EACH OBJECT(TABLESPACE,PARTITION,TABLE,IDNEXSPACE),THERE IS A CLAIM-COUNT
WHICH IS INCREASED 1 AT START AND DECREASE BY 1 AT COMMIT
A utility starts with a Drain
No new Claims are allowed (wait, maybe timeout)
If the Claim-count >0 the utility waits (maybe timeout)
When the Claim-count reaches zero, the utility can continue
Two important situations:
A claim is a notification to DB2 that a particular object is currently being accessed. Claims usually do not continue to exist beyond the commit point one exception being a Cursor with Hold. In order to access the DB2 object within the next unit of work, an application needs to make a new claim.Claims notify DB2 that there is current interest in or activity on a DB2 object.Even if Uncommitted Read doesn’t take any locks, a claim is taken. As long as there are any claims on a DB2 object, no drains may be taken on the object until those claims are released.
A drain is the action of obtaining access to a DB2 object, by:
Preventing any new claims against the object.
Waiting for all existing claims on the object to be released.
A drain on a DB2 object causes DB2 to quiesce all applications currently claiming that resource, by allowing them to reach a commit point but preventing them (or any other application process) from making a new claim.A drain lock also prevents conflicting processes from trying to drain the same object at the same time.
Utilities detect claimers are present and wait
Drain Write waits for all write claims to be released
Drain All waits for claims on all classes to be released
SHRLEVEL(CHANGE) Utilities are CLAIMers
Latches – managed by DB2
? BM page latching for index and data pages
? DB2 internal latching (many latches grouped into 32 latch classes)
? Latches – managed by IRLM
? Internal IRLM serialization
虽然标题是advanced topics,其实涉及的内容并没有本质的区别,只不过我们在看待问题,分析问题时需要从单个DB2 SUBSYSTEM的视角上升到DBMS CLUSTER的高度,即DATA SHARING GROUP的高度。为了处理不同的member之间共享数据的COHERENCY问题,DB2引入P-LOCK,区别主要在于单个DB2 SUBSYSTEM中的LOCK owner均为tran,而P-LOCK的owner为DB2 SUBSYSTEM.因此这里的内容按照这种分类进行介绍L-LOCK,P-LOCK,RETAINED LOCK,这几种锁之间的区别需要了解。
特点:
THE KIND YOU HAVE IN BOTH DATA SHARING AND NON DATA SHARING SUBSYTEM THEN CONTROL DATA CONCRRRENCY OF ACCESS TO OBJECTS THEN CAN BE LOCAL OR GLOBAL THEY ASSOTIATED WITH PROGRAMS
WHY IS HIERACHICAL
SO THE MEMBER DB2 SUBSYSTEM WILL NOT PROPAGATE L-LOCKS TO THE CF UNNECESSARILY
特点:
EXCLUSIVE TO DATA SHARING
ARE ONLY GLOBAL
USED TO MAINTAIN DATA COHERENCY IN DATA SHARING
ALSO USED TO MAINTAIN EDMPOOL CONSISTENCY AMONG MEMBERS
ASSOTIATED BY(OWNED BY) DB2 MEMBERS
P-LOCK CAN BE NEGOTIATTED BETWEEN DB2 MEMBERS
NO TIMEOUT OR DEADLOCK DETECTION
分类
The ONLY way to clear retained locks is by estarting the failed DB2 subsystem.
A LOCK THAT A DB2 SHARING DB2 MEMBER HAS TO MAKE KNOWN TO OTHERS MEMBERS OF THIS DATA SHARING GROUP
THE IRLM VIA XES,PROPAGATE TO THES LOCKS TO THE STRUCTURE IN THE CF
XCF,XES的内容在XES contention部分介绍。
HOW ARE GLOBAL LOCK REQUESTS MADE KNOWN TO OTHER DB2 MEMBERS?
THEY ARE PROPAGATED TO DB2 LOCK STRUCTURE
Page Set P-Lock Negotiation
与APPLICATION LOCK不同的是,PHYSCIAL LOCK 可以做Page Set P-Lock Negotiation。
上图展示了INERDB2 READ/WRITE INTEREST场景下的PAGE SET P-LOCK NEGOTIATION。所谓的negotiation就是锁请求方以及锁申请方将锁请求均DOWNGRADE,从而达到compatible的目的。需要注意的是,PAGE SET P-LOCK STATE状态的变化需要涉及IRLM以及XCF通讯机制。PAGE SET P-LOCK对GLOBAL BUFFER POOL起一种标志作用,
对于READ的DB2 SUBSYSTEM, must register its
interest in read pages in the GBP directory
对于UPDATER的DB2 SUBSYSTEM,must write updated pages to the group buffer pool and register those updated pages in the GBP directory
DEVIDE INTO TWO PARTS OF CF LOCK STRUCTURE RATIO OF LAST TWO COMPONETS DEFAULT ROUGHLY 50%
CAN BE ALTERED BY IRLM PARAMETER
OVERVIEW OF MRL STRUCTURE
ACTIVE status of active means that the DB2 system is running.
Retained, on the other hand, means that a DB2 and/or its associated IRLM has failed
CONTAINS ENTRIES FOR ALL CRURENTLY HELD MODIFY LOCKS
IN THE EVENT OF DB2 FAILTURE ENSURE THAT OTHER MEMBER OF DB2 KNOW WHICH LOCKS MUST BE
It is essentially a list structure that lists all the update or modify locks in the data sharing group
DISPLAY GROUP ; Display “LIST ENTRIES IN USE”
NUMBER LIST ENTRIES : 7353, LIST ENTRIES IN USE: 1834
WARNING: When the LIST ENTRIES IN USE catches up to the NUMBER LIST ENTRIES, new modify lock requests will not be granted, and transactions will begin to fail with -904s.
A 'POOL' OF HASH POINTERS
CONTAINS ENTRIES FOR ALL READ AND MODIFY LOCKS
USED FOR GLOBAL LOCK CONTENTION
It is very difficult to keep track of how full a randomly populated table is, so the only way to track its utilization is indirectly. In DB2 data sharing, that tracking mechanism is false contention
SIZE OF EACH INDIVIDUAL ENTRY SET BY MAXUSRS PARAMETER IN IRLMPROC OF FIRST IRLM JOIN THE GROUP
NUMBER OF MEMBER IN THE GROUP
其实主要的目的就是提供INTER-SUBSYTEM之间通讯的API,主要的功能有三个:
action | DB2 member interest page set p lock | STAE CHANGE |
---|---|---|
open | PP-LOCK S/IS | NONE->R/O |
PP-LOCK IX/SIX/X/U | R/O->R/W | |
PP-LOCK S/IS | R/W->R/O | |
NONE | R/O->NONE |
产生的根源为XES本身设计时仅支持两种锁类型,
?XES Contention = XES-level resource contention as XES
only understands S or X
eg member 1 asking for IX and member 2 for IS
Big relief in V8
产生的根源主要是由于DB2 lock table定义的太小或是hash算法离散型不够好,导致不同的DB2资源hash到同一lock table hash class上而产生的冲突,可以通过放大CF lock table来解决,因此FALSE CONTENTION标志着 CF LOCK STRUCTURE的大小是否合适。
GLOBAL CONTENTIONGLOBAL CONTENTION 本身包含XES CONTENTION,FALSE CONTENTION,REAL CONTENTION,其中REAL CONTENTION 确实为资源冲突,如资源热点,其它的两种冲突是由于引入DATA SHARING 的OVERHEAD
根据上面的介绍,下面给出A DB2 Performance Tuning Roadmap-2 data sharing BLOCK的一个展示
L-LOCK(SHARING)部分表明了数据共享的程度
P/L XES CONTENTION表明P-LOCK,L-LOCK中需要上送CF的比例。其中P-LOCK需要上送的大部分为PAGE P-LOCK.
WHEN YOU Running Out of Space in the Lock Structure:
DXR170I DJP5005 THE LOCK STRUCTURE DSNDB0G_LOCK1 IS 50%(60%,70%) IN USE
DXR142I DJP5005 THE LOCK STRUCTURE DSNDB0G_LOCK1 IS zz% IN USE
ACTION:
DYNAMICALLY:
SETXCF START,ALTER,STRNAME=DSNDB0G_LOCK1,SIZE=newsize Only Increases Lock List Table (MRL)
REBUILDING – Two Methods:
Letting IRLM Split Lock Structure (1:1) Change the INITSIZE, SIZE in CFRM Policy START New Policy Rebuild Lock Structure Using LTE= or F xxxx,SET,LTE=yy
Concurrency warning signs
(Periodic) monitoring for concurrency problems
MESSAGE | EXPLAIN | STAT CLASS |
---|---|---|
DSNT375I | Deadlock message | IFCID 172 |
DSNT376I | Timeout message | IFCID 196 |
DSNT501I | Resource unavailable msg | NONE |
DSNI031I | Lock escalation | IFCID 337 |
DSNJ031I | IFCID 313 | |
DSNR035I | NONE | |
DSNB260I | NONE |
DISPLAY DATABASE USE/RESTRICT/CLAIMERS/LOCKS
Does not provide info about all locks (eg. page/row locks are not shown)
DISPLAY THD TYPE(* / ACTIVE / INACTIVE / SYSTEM / INDOUBT / POSTPONED / PROC)
Note that ‘*’ displays only active, indoubt, postponed, and system threads
Use LIMIT(*) to avoid output truncation
EXAMPLE:
>
HOW TO INTERPRETING -DISPLAY LOCKS OUTPUT:
DSN397I -DB8A
NAME TYPE PART STATUS CONNID CORRID LOCKINFO
TSNAME TS RW TSO XXXX H-IX,S,C
AGENT TOKEN(9999)
TABLE OF LOCKS
STATUS | STATE-L | STATE-P | TYPE | DURATION |
---|---|---|---|---|
L-LOCK+DRAIN LOCK | P-LOCK | |||
H:HOLDING | IS | IX | S:TS L-LOCK | |
W:WAITING | IX | IX | T:TB L-LOCK | |
R:RETAINED | S | S | P:PT L-LOCK | |
U[^FT1] | C:CS DRAINLOCK | |||
SIX[^FT2] | SIX | R:RR DRAIN LOCK | ||
X | X | W:WR DRAIN LOCK | W:NUMBER IN THE WAITTING QUEUE | |
NSU | PAGE P:PAGE SET P-LOCK | R:N/A |
SOMETHING YOU NEED TO NOTICE:
PLAN_TABLE ‐ TSLOCKMODE column
Info depends on whether or not DB2 can determine the isolation level at bind/prepare time
Only applies to gross L‐ locks (TS/table lock)
关于trace的内容,在新年开篇第一篇博客里面已经进行了介绍,这里主要介绍一些highlight:
subsystem services/drda remote locations
non-data sharing locks
datasharing lock section
system addres cpu time
latch contention section
DML/DDL/DCL
GBP SECTION(稍后单独拿出一章进行整理)
如果你想更深入的一步进行测试,可以通过临时开启对应的Zooming in using detailed trace data进行,最好在vendor的建议下开启。
NEXT WHAT TO CONSIDER ABOUT TRANSACTION LOCKING: