请帮忙解释db2中的Internal Plan Lock？

ganquan

wait。。。。

我笨故我在

讲的还算全面,看看吧

wangzhonnew

LOCK_MODE means the following:                                            
=============================                                             
  0             No Lock                                                   
  1             Intention Share Lock                                      
  2             Intention Exclusive Lock                                 
  3             Share Lock                                                
  4             Share with Intention Exclusive Lock                       
  5             Exclusive Lock                                            
  6             Intent None (For Dirty Read)                              
  7             Super Exclusive Lock                                      
  8             U-Lock                                                   
  9             Next key S lock                                          
10             Next key X lock                                          
11             Weak-exclusive lock                                       
12             Next-Weak-exclusive lock                                 
Lock_status:                                                              
==============                                                            
1                     Granted State                                       
2                     Converting state                                    
Lock_Object_type:                                                         
=================                                                         
1        table lock type                                                  
2        table row lock type                                             
3        Internal lock type                                               
4        Tablespace lock type                                             
5        end of table lock                                                
6        key value lock                                                   
7        Internal lock on the sysboot table                              
8        Internal Plan lock                                               
9        Internal Variation lock                                          
10       Internal Sequence lock                                          
11       Bufferpool lock                                                  
12       Internal Long/Lob lock                                          
13       Internal Catalog Cache lock                                      
14       Internal Online Backup lock                                      
15       Internal Object Table lock                                       
16       Internal Table Alter lock                                       
17       Internal DMS Sequence lock                                       
18       Inplace reorg lock                                               
19       Block lock type

An internal plan lock is a lock on the access plan that is generated      
during the runtime for a dynamic SQL statement.

and i got more information for Internal V lock:
A internal variation is a lock on a dynamic sql statement in              
the sql cache (package cache). Any execution of dynamic sql is done via   
an entry in the sql cache known as a variation.  A variation is a term   
we use to describe the result of combining the unique text of an sql      
statement with a unique compilation environment and it represents the     
executable section that would result from compiling that statement text   
with that compilation environment.  "The compilation environment         
encompasses all those elements that affect the nature and result of the   
section generated by the SQL compiler for a dynamic SQL statement (e.g.,  
special registers, relevant package compilation defaults, use of default  
qualifiers, use of function path, etc.)."                                 
Each variation in use has a V lock held on it by the appl using it for   
the life of their use. This lock is used to protect the integrity of the  
section from ddl while it is executing as well as to protect it from      
deletion by cache space management. When a variation is being "loaded"   
(i.e. compiled), an exclusive V lock is held on the variation until the   
variation is filled or the compile fails or is abandoned by the user.     
If a number of requests come in for the same variation that is being      
loaded, one will load it and the others will wait for that to complete.   
Then they will all begin executing it.                                    
A 'variation' is normally locked in share mode in order to ensure that   
the variation remains valid while executing. The variation lock is        
exclusive mode in the following 3 situations:                             
1) the variation is in the process of being placed in the cache.  This   
is known as a variation 'loading' lock, and it's held exclusively         
because we do not want duplicate entries in the cache - so we ensure      
that only one agent is involved in 'loading' by use of the loading lock.  
2) There is ddl (e.g. the drop of an object) that is invalidating         
entries in the dynamic cache.  Invalidation ensures that all variations   
that have dependencies on the object being dropped/modified are removed   
from the cache, and an X lock is used to ensure that noone can acquire   
this lock                                                                 
3) An application created or modified an object and then issed sql on     
the object before commiting the creation/modification.  The variation X   
lock in this case ensures that noone else can execute this variation as   
the changes haven't been commited yet.

heroru

Hi wangzhonnew,

I remember in db2 recovery algorithm (ARIES/IM), all locks are in data records(rows), there is no key locks!

Could you post more about the 'key value locks' you described here.

wangzhonnew

A key value lock is a lock on a unique index key value that we get when deleting/updating a unique key when the DB2_RR_TO_RS registry variable is set.  This registry variable disables next key locking, which is the standard technique DB2 uses to ensure that deleted keys are waited upon for both scans and inserts.  Next key locking traditionally causes lock wait and deadlock scenarios.  The registry variable was created back in V2 for SAP to disable next key locking on non-catalog tables and instead implemented key value locking to guarantee uniqueness for unique indexes.

For a unique index, say we delete the key with value 1 but do not commit.  Another transaction comes in and wants to insert key value 1.  With next key locking the delete would have locked the next key above 1 (say 2), and the insert would have to lock the next key above 1 (thus waiting for the lock on 2 to see if there is or isn't a value of 1 already as the first transaction can either commit or rollback the delete.

With next key locking disabled, the insert of 1 cannot occur until we know the status of the delete of 1.  The key value lock is a hash of the unique key value.  The delete acquires this lock in X mode.  An insert will wait for lock (similar to how it would have with next key locking) in order to see the uncommitted status of the possible delete.

heroru

Please see my questions/challenges below ---

A key value lock is a lock on a unique index key value that we get when deleting/updating a unique key when the DB2_RR_TO_RS registry variable is set. This registry variable disables next key locking, which is the standard technique DB2 uses to ensure that deleted keys are waited upon for both scans and inserts. Next key locking traditionally causes lock wait and deadlock scenarios. The registry variable was created back in V2 for SAP to disable next key locking on non-catalog tables and instead implemented key value locking to guarantee uniqueness for unique indexes.

--- so key value lock is actually a real key lock in DB2! but if it's a derivation from ARIES/KVL algorithm, key value lock should apply to unique/non-unique indexes, not just unique one! Besides, as what you said, even I set the DB2_RR_TO_RS to YES, I still can not see any key value lock(KVL), except row lock in key row.

--- 'Next key locking' is a must use method in ARIES/IM/KVL if RR is required, I think DB2_RR_TO_RS registry variable is only for lowering down the isolation level from serializable(RR) to less restricted level (RS) but in the cost of lossing the semantic transaction isolation. (it will allow the phantom inserts)

For a unique index, say we delete the key with value 1 but do not commit. Another transaction comes in and wants to insert key value 1. With next key locking the delete would have locked the next key above 1 (say 2), and the insert would have to lock the next key above 1 (thus waiting for the lock on 2 to see if there is or isn't a value of 1 already as the first transaction can either commit or rollback the delete.

--- I agree with this, it is how the next key locking works behind the sence. But is this happen only for unique index?

With next key locking disabled, the insert of 1 cannot occur until we know the status of the delete of 1. The key value lock is a hash of the unique key value. The delete acquires this lock in X mode. An insert will wait for lock (similar to how it would have with next key locking) in order to see the uncommitted status of the possible delete.

--- can next key locking(NKL) really be disabled in DB2? I knew the SQL server make use of Key Range Locking(KRL), which let them overcome some of weakness of next key locking and enable them to get better transaction concurrency. but without any improvement in ARIES algorithm. can db2 simply bypass next key locking?

--- what you said here is looked like the benefit of type-2 index in v8 gave out, but have nothing to do with key value locking. can you give out an example?

wangzhonnew

cannot answer, i'm not L3 lol. this is the doc provided by Micheal Cornish by 2002, it's the only one discussed about key value lock in the database, and i think this is the only one not confidential too. if you have further question maybe you have to open PMR. but i'm not sure if they can provide you more info other that this, coz the detailed implementation is always confidential.

heroru

ooo....h...It's information age today!

Don't you think keep these undisclosed thing would help DB2 win in the future market!?
Probably that's why db2 is not much popular than Oracle and SQL Server.
As what I can see today, db2 engine technically has been surpassed by other dbms vendors, since 1992 they released the common db server V2. (not much enhancement ibm did on the engine side, even after acquisition of informix & redbrick !)

bpmfhu

关于db2得锁的开销和oracle的一个比较
推荐一篇文档:

wangzhonnew

i definitly agree with you heroru, but i'm not the CEO lol ^_^
they just decide to keep everything confidential
i don't have much expereinece about db2 actually, so i'm not sure the deveopment history of it, however from the source code i can always see the changes by different version, even between fixpaks, so i guess the L3 teams are doing something on the engine side lol