DB2 and Oracle concurrency control (lock) comparison _oracle application

1 Introduction


In relational databases (Db2,oracle,sybase,informix and SQL Server) the smallest recovery and transaction units are one transaction (transactions), and transactions have acid (atomicity, consistency, isolation, and permanence) characteristics. In order to ensure the correctness of concurrent users accessing the same database object (i.e. no lost updates, repeatable reads, no read "dirty" data, no "phantom" reading), the concurrency (locking) mechanism is introduced into the database. There are two basic types of locks: exclusive locks (Exclusive locks for x locks) and shared locks (Share locks are recorded as S locks).


Exclusive Lock: If the transaction t adds x to the data d, then any other transaction can no longer have any type of lock on D until T releases the X lock on D, which is generally required to lock the data before the data is modified, so the exclusive lock is also known as a write lock.


Shared locks: If the transaction T to the data D plus s lock, then other transactions can only be D plus s lock, but not x lock, until T release D on the S lock; it is generally required to add shared locks to the data before reading the data, so shared locks are also known as read locks.


Introduction of multi-granularity blockade mechanism in 2 DB2


2.1 Objects of the lock


DB2 Support for table spaces, tables, rows, and indexes lock (databases on mainframes can also support the lock of data pages) to ensure concurrency integrity of the database. However, when considering the concurrency of user applications, locks used for tablespaces and indexes are not generally checked. The focus of this type of problem analysis is table lock and row lock.


2.2 Strategy of the lock


DB2 can lock tables only, or you can lock rows in tables and tables. If only the table is locked, all rows in the table are affected to the same extent. If the scope of the lock is for the table and subordinate rows, the table is locked and the corresponding data row is locked. Whether the application is a table row lock or a table lock and row lock is determined by the command that the application executes and the isolation level of the system.


Mode of 2.2.1 DB2 table lock


DB2 can be locked at the table with the following locking methods:





Table One: DB2 database table lock mode





The following patterns for several table locks are further elaborated:


The IS, IX, six methods are used at the table level and require row-lock coordination, and they can prevent other applications from adding exclusive locks to the table.


• If an application obtains an IS lock on a table, the application can get an S lock on a row for read-only operations, while other applications can also read the row or make changes to other rows in the table.


• If an application obtains an IX lock on a table, the application can get an X lock on a row to change the operation, while other applications can read or change other rows in the table.


• If an application obtains a six lock on a table, the application can get an X lock on a row to change the operation, while other applications can only read the other rows in the table.


The S, U, X, and Z methods are used at the table level, but do not require row lock matching, which is a more restrictive table locking strategy.


• If an application gets the S lock of a table. The application can read any data in the table. It also allows other applications to obtain a read-only request lock on the table. If an application needs to change the data on the table, the S lock must be released.


• If an application gets a U lock on a table, the application can read any data in the table and eventually get the right to modify any data in the table by acquiring an X lock on the table. Other applications can only read the data in the table. The difference between U lock and S lock mainly lies in the intention of change. The U-Lock is designed primarily to prevent two applications from simultaneously applying for an X lock with an S lock and creating a deadlock.


• If an application gets an X lock on a table, the application can read or modify any data in the table. Other applications cannot read or change the table.


• If an application gets a Z lock on a table, the application can read or modify any data in the table. Other applications, including uncommitted read programs, cannot read or change the table.


In locks are used on a table to allow the concept of uncommitted reads.


2.2.2 DB2 mode of row lock


In addition to table locks, DB2 supports row locks in several ways.





Table II: DB2 Database row lock mode





Compatibility of 2.2.3 DB2 locks





Table III: Compatible matrices for DB2 database table locks








Table IV: DB2 database row lock compatible matrix





The following table summarizes the cases in which the SQL statements in DB2 CREATE TABLE locks (assuming the default isolation level is CS):








Upgrade of 2.3 DB2 lock


Each lock in memory requires a certain amount of memory space, in order to reduce the memory overhead required by the lock, DB2 provides a lock upgrade function. Lock escalation reduces the amount of locks required by locking the table with an unwanted table lock and releasing row locks.) Lock escalation is done automatically by the database manager, and the configuration parameters of two databases directly affect the processing of lock escalation:


locklist--the memory used for lock storage in a database global memory. The Unit is page (4K).


maxlocks--the percentage of the locklist size of memory occupied by an application's allowable locks.


Lock escalation is triggered in both cases:


• A lock requested by an application takes up more memory space than the product size of the maxlocks and locklist. At this point, the database Manager attempts to save space by requesting a table lock for the application that made the lock request and releasing the row lock.


• All locks that have been added to a database have a memory space that exceeds the size of the locklist definition. At this point, the database Manager will also attempt to save space by requesting a table lock for the application that made the lock request and releasing the row lock.


• Lock upgrades can degrade the concurrency performance of OLTP applications, but release locks to occupy memory and increase memory space for available locks.


Lock escalation is a possibility of failure, for example, an application now has an IX lock on one table, an X lock on some rows in the table, another application to request an IS lock on the table, and a lock on many rows, which causes the lock to escalate because the number of locks requested is too high. The database manager attempted to reduce the number of locks required for the S lock on the application request table, but the lock escalation failed because the S lock conflicts with the original IX lock on the table.


If the lock upgrade fails, the application that caused the lock escalation will receive a-912 sqlcode. After the lock upgrade fails, the DBA should consider increasing the size of the locklist or increasing the percentage of maxlocks. It is also possible for programmers to resubmit a transaction after a lock escalation occurs in the program (for example, if sqlca.sqlcode=-912 then rollback and retry).


Introduction of 3 Oracle multi-granularity lock mechanism


Depending on the protection object, Oracle database locks can be grouped into the following categories:


(1) DML Lock (data locks): Used to protect the integrity of the data;


(2) DDL Lock (dictionary locks, Dictionary Lock): Used to protect the structure of database objects (such as tables, views, index structure definitions);


(3) Internal Locks and latches (internal lock and latch): Protection of internal database structure;


(4) Distributed locks (distributed lock): used in OPS (parallel server);


(5) PCM locks (parallel cache management Lock): Used in OPS (parallel server).


The main lock in Oracle is DML (also known as data locks) locks. From the angle of blocking granularity (blocking the size of the object), there are two levels of Oracle DML locks, one-level locks and table-level locks.


3.1 Oracle TX Locks (Row-level locks, transaction locks)


Many technicians who are not familiar with Oracle may assume that each TX lock represents a blocked data row, but it is not. The original meaning of TX is transaction (transaction), when a transaction performs a data change (Insert, Update, Delete) for the first time, or uses a select ... When the FOR UPDATE statement is queried, it obtains a TX (transaction) lock that is released until the end of the transaction (a commit or rollback operation) is performed. So, a TX lock can correspond to multiple rows of data that are locked by the transaction (when we use it, we start a transaction and select ...). For UPDATE nowait).


On each line of data for Oracle, there is a flag bit to indicate whether the row data is locked. Unlike DB2, Oracle builds a linked list to maintain each row of locked data, which greatly reduces the maintenance overhead of row-level locks, and largely avoids locking upgrades that occur when the number of locks often occurs when DB2 use row-level locks. Once the lock flag on the data line is placed, it indicates that the row data is X-locked and that Oracle has no S lock on the data line.


3.2 TM Lock (table-level lock)


The derivation of 3.2.1 intention lock


A table is made up of rows, and when we lock a table, we need to check that the request for the lock is compatible with the original table-level lock, and also check whether the lock is compatible with the lock on each row in the table. For example, a transaction should have an S lock on a table, and if a row in the table has been added an X lock by another transaction, then the application for the lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock flags line by row will be significant and the performance of the system will be affected. In order to solve this problem, a new lock type can be introduced at the table level to represent the lock of the row, which leads to the concept of "intent lock".


The meaning of intent lock is that if an intent lock is added to a node, the underlying node of the node is being lock; When a lock is added to any node, an intent lock must be added to the upper node of the point. For example, when you lock any row in a table, you must first add an intent lock to the table it is in, and then lock the row. As a result, when a transaction locks a table, it no longer needs to check the lock flags of each row in the table, and the system's efficiency can be greatly improved.


Type of 3.2.2 Intent lock


With two basic lock types (s locks, x locks), two kinds of intent locks can be naturally derived:


Intent to share the lock (Intent Share Lock): If you want to a database object plus s lock, first of its superior node Plus is lock, indicating its descendants of the node to be (intent) plus s lock;


Intent exclusive Lock (Intent Exclusive Lock): If you want to add an X lock to a database object, you first have to add an IX lock to its parent node, indicating that its descendants are fitted with an X lock.


In addition, the basic lock type (S, X) and the intent lock type (IS, IX) can also be combined with a new lock type, theoretically can be combined 4 kinds, namely: S+is,s+ix,x+is,x+ix, but a little analysis is not difficult to see, in fact, only s+ix have a new meaning, The other three combinations do not increase the strength of the lock (ie: s+is=s,x+is=x,x+ix=x, where the "=" refers to the same strength of the lock). The so-called strength of the lock refers to the degree of rejection of other locks.


This allows us to introduce a new type of lock:


Shared Intent exclusive Lock (shared Intent Exclusive lock, short six lock): If you add a six lock to a database object, you add an S lock to it, plus an IX lock, that is, Six=s+ix. For example, a transaction that adds a six lock to a table means that the transaction reads the entire table (so you want to add an S lock to the table) and updates the individual rows (so you want to add an IX lock to the table).


There may be 5 types of locks on database objects: S, X, is, IX, SIX.


Any transaction t in a multi-granularity blocking method with intent lock to lock a database object, it must first add an intent lock to its upper node. The application of the blockade should be carried out in a top-down order, and the release of the blockade should be carried out in a bottom-up order; a multi-granularity blocking method with intent lock improves the system concurrency and reduces the overhead of locking and unlocking.


3.3 Oracle's TM Locks (table-level locks)


Oracle's DML Lock (data lock) is the use of the above mentioned method of the multi-granularity blocking, the row-level lock, although only one (that is, X-Lock), however, there are 5 kinds of TM lock (table-level lock) types, namely shared lock (S lock), exclusive lock (x Lock), row-level shared lock (RS lock), row-level exclusive lock (RX Lock), The shared row-level exclusive lock (SRX Lock) corresponds to the S, X, is, IX, and six mentioned above. It is to be noted that, because Oracle is only providing x locks at the row level, the RS lock (via Select ...) is locked. For UPDATE statement (s) the corresponding row-level lock is also an X-lock (but the row data has not actually been modified), which is different from the theoretical is lock. Lock compatibility means that when an application adds some kind of lock to a table (row), whether other applications can add a lock on the table (row), and if so, the two locks are compatible, otherwise the two locks are incompatible and cannot be accessed concurrently with the same data object.


The following table shows the compatibility matrix for the Oracle Database TM Lock (Y=yes, a compatible request; N=no, representing an incompatible request;-Indicates no lock request):





Table V: compatible matrices for TM locks in Oracle databases





On the one hand, when Oracle executes select ... For the DML statements such as update, INSERT, UPDATE, delete, the system automatically requests the table-level RS lock on the table to be manipulated (SELECT ...). For UPDATE) or RX Lock (INSERT, UPDATE, DELETE), when a table-level lock is obtained, the system automatically requests a TX lock, and the lock flag position of the actual locked data row (pointing to the TX lock), and on the other hand, the program or operator can also pass the lock Table statement to specify a type of TM lock to be obtained. The following table summarizes the cases in which the SQL statements in Oracle produce TM Locks:





Table VI: Summary of TM locks for Oracle databases





We can see that the usual DML operation (SELECT ...). For UPDATE, INSERT, update, DELETE, only an intent lock (RS or RX) is obtained at the table level, its true blocking granularity is at the row level, and a notable feature of the Oracle database is that, by default, Simply reading data (SELECT) is not locked, and Oracle uses the rollback segment (Rollback segment) to ensure that users do not read "dirty" data. All these increase the concurrency of the system.


Due to the introduction of intent locks and data line locking flags, the overhead of Oracle maintenance line-level locks is reduced, and the application of these technologies enables Oracle to efficiently handle highly concurrent transaction requests.


4 DB2 monitoring of multi-granularity blocking mechanism


There are two main ways to monitor locks in DB2, the first is snapshot monitoring, and the second is event monitoring.


4.1 Snapshot monitoring mode


Before using the snapshot to monitor the lock, you must turn on the monitor lock switch, which can be opened from the instance level and session level, as follows:





DB2 update dbm CFG using Dft_mon_lock on (instance level)


DB2 Update monitor switches using lock on (session level, recommended)


When the switch is turned on, you can perform the following commands to monitor the lock


DB2 get snapshot to Locks on ebankdb (details of specific locks in the current database are available)


DB2 get snapshot for locks on ebankdb


Fri Aug 15:26:00 Jinan 2004 (red key information for locks)








Database Lock Snapshot


Database name = DEV


Database Path =/db2/dev/db2dev/node0000/sql00001/


Input Database alias = DEV


Locks held = 49


Applications currently connected = 38


Agents currently waiting on locks = 6


Snapshot timestamp = 08-15-2003 15:26:00.951134


Application handle = 6


Application ID = *local.db2dev.030815021007


Sequence Number = 0001


Application name = Disp+work


Authorization ID = SAPR3


Application Status = UOW waiting


Status Change time =


Application code page = 819


Locks held = 0


Total wait Time (ms) = 0


Application handle = 97


Application ID = *local.db2dev.030815060819


Sequence Number = 0001


Application NAME = TP


Authorization ID = SAPR3


Application Status = Lock-wait


Status Change time = 08-15-2003 15:08:20.302352


Application code page = 819


Locks held = 6


Total wait Time (ms) = 1060648


Subsection waiting for lock = 0


ID of Agent holding lock = 100


Application ID Holding lock = *local.db2dev.030815061638


Node lock wait occurred on = 0


Lock Object type = Row


Lock mode = Exclusive Lock (X)


Lock mode requested = Exclusive Lock (X)


Name of tablespace holding lock = PSAPBTABD


Schema of table holding lock = SAPR3


Name of table holding lock = Tplognames


Lock wait start timestamp = 08-15-2003 15:08:20.302356


Lock is a result of escalation = NO


List of Locks


Lock Object Name = 29204


Node number Lock is held at = 0


Object Type = Table


Tablespace Name = psapbtabd


Table Schema = SAPR3


Table Name = Tplognames


Mode = IX


Status = Granted


Lock escalation = NO








DB2 get snapshot to database on dbname |grep-i locks (unix,linux platform)








Locks held currently = 7


Lock waits = 75


Time database waited on locks (ms) = 82302438


Lock list Memory in use (Bytes) = 20016


Deadlocks detected = 0


Lock escalations = 8


Exclusive Lock escalations = 8


Agents currently waiting on locks = 0


Lock Timeouts = 20





DB2 get snapshot to database on dbname |find/i "locks" (NT platform)


DB2 get snapshot for locks for Applications Agentid 45 (note: 45 is the application handle)








Application handle = 45


Application ID = *local.db2dev.030815021827


Sequence Number = 0001


Application NAME = TP


Authorization ID = SAPR3


Application Status = UOW waiting


Status Change time =


Application code page = 819


Locks held = 7


Total wait Time (ms) = 0


List of Locks


Lock Object Name = 1130185838


Node number Lock is held at = 0


Object Type = Key Value


Tablespace Name = psapbtabd


Table Schema = SAPR3


Table Name = Tplognames


Mode = X


Status = Granted


Lock escalation = NO


Lock Object Name = 14053937


Node number Lock is held at = 0


Object Type = Row


Tablespace Name = psapbtabd


Table Schema = SAPR3


Table Name = Tplognames


Mode = X


Status = Granted


Lock escalation = NO








You can also perform the following table functions (note: Only commands are available before DB2 V8, DB2 V8 can be monitored using table functions after the table function is recommended)


DB2 SELECT * FROM table (Snapshot_lock (' dbname ', -1)) as Locktable Monitor lock information


DB2 SELECT * FROM table (snapshot_lockwait (' dbname ', -1) as Lock_wait_table Monitor application lock wait information


4.2 Event Monitoring mode:


When monitoring a lock using the event monitor, the deadlock can only be monitored because the transaction cannot be closed because of a lock request conflict, and the request conflict cannot be resolved within this transaction. Typically, two applications hold each other's locks and do not release existing locks if they do not have the locks they need, as follows:


DB2 Create Event Monitor Dlock for deadlocks with the details write to file ' $HOME/dir '


DB2 Set Event Monitor Dlock State 1


Db2evmon-db DBNAME-EVM Dlock Look at the specific deadlock output (pictured below)








Deadlocked Connection ...


Deadlock Id:4


Participant No.: 1


Participant No. Holding the Lock:2


Appl id:g9b58b1e. d4ea.08d387230817


APPL Seq number:0336


APPL Id of connection holding the lock:g9b58b1e. D573.079237231003


Seq. No. of connection holding the lock:0126


Lock Wait start time:06/08/2005 08:10:34.219490


Lock name:0x000201350000030e0000000052


Lock attributes:0x00000000


Release flags:0x40000000


Lock count:1


Hold count:0


Current Mode:ns-share (and Next Key Share)


Deadlock Detection time:06/08/2005 08:10:39.828792


Table of Lock waited on:orders


Schema of Lock waited On:db2inst1


Tablespace of Lock waited On:userspace1


Type of Lock:row


Mode of Lock:ns-share (and Next Key Share)


Mode application requested on LOCK:X-Exclusive


Node Lock occured on:0


Lock Object name:782


Application handle:298


Deadlocked Statement:


Type:dynamic


Operation:execute


Section:34


Creator:nullid


package:syssn300


Cursor:sql_cursn300c34


Cursor was Blocking:false


Text:update ORDERS SET totaltax =?, totalshipping =?,


LOCKED =?, Totaltaxshipping =?, STATUS =?, FIELD2 =?, timeplaced =?,


FIELD3 =?, CURRENCY =?, SEQUENCE =?, Totaladjustment =?, Ormorder =?,


Shipascomplete =?, Providerordernum =?, Totalproduct =?, DESCRIPTION =?,


member_id =?, orgentity_id =?, FIELD1 =?, storeent_id =?, ordchnltyp_id =?,


address_id =?, Lastupdate =?, COMMENTS =?, Notificationid =? WHERE orders_id =?


List of Locks:


Lock name:0x000201350000030e0000000052


Lock attributes:0x00000000


Release flags:0x40000000


Lock Count:2


Hold count:0


Lock Object name:782


Object Type:row


Tablespace Name:userspace1


Table Schema:db2inst1


Table name:orders


Mode:x-Exclusive


Lock name:0x00020040000029b30000000052


Lock attributes:0x00000020


Release flags:0x40000000


Lock count:1


Hold count:0


Lock Object name:10675


Object Type:row


Tablespace Name:userspace1


Table Schema:db2inst1


Table Name:bkorditem


Mode:x-Exclusive (omit later information)





5 Oracle Multi-granularity blockade mechanism monitoring


To monitor the status of locks in Oracle systems, we need to understand several system views:


5.1 V$lock View


The V$lock view lists all locks held or being requested by the current system, with the primary fields described as follows:





Table Seven: V$lock View primary field description





In the value of Type field, only the TM, TX two kinds of DML lock types are concerned;


5.2 V$locked_object View


The V$locked_object view lists which objects in the current system are being locked, and its primary fields are described below:





Table Eight: V$locked_object view field Descriptions





5.3 Oracle Lock Monitoring script


Depending on the system view above, you can prepare scripts to monitor the status of locks in the database.


5.3.1 Showlock.sql


The first script, Showlock.sql, shows which objects are locked by which sessions by connecting V$locked_object with the All_objects two view:








* Showlock.sql * *


Column O_name format A10


Column Lock_type format A20


Column object_name format A15


Select Rpad (oracle_username,10) o_name,session_id SID,


Decode (locked_mode,0, ' None ', 1, ' Null ', 2, ' Row share ',


3, ' Row Exclusive ', 4, ' Share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Lock_type,


object_name, XIDUSN,XIDSLOT,XIDSQN


From V$locked_object,all_objects


where v$locked_object.object_id=all_objects.object_id;


5.3.2 Showalllock.sql





The second script showalllock.sql, which mainly displays all the current TM, TX lock information;








* Showalllock.sql * *


Select Sid,type,id1,id2,


Decode (lmode,0, ' None ', 1, ' Null ', 2, ' Row share ',


3, ' Row Exclusive ', 4, ' Share ', 5, ' Share Row Exclusive ', 6, ' Exclusive '


Lock_type,request,ctime,block


From V$lock


where TYPE in (' TX ', ' TM ');


6 DB2 example of multi-granularity blocking mechanism


The following examples are run in DB2 udb and apply to all database versions. First, three command-line windows (DB2 CLP) are opened, two of which (hereinafter represented by sess#1, sess#2) are connected to the database by db2admin users to manipulate the sample tables (employee) provided in the sample library, and the other (for the following sess# 3 indicates that the Db2admin user is connected to the database to monitor the locking of each type of SQL statement executed; It is hoped that the reader will monitor the locking of each type of SQL statement in this manner. (Because the example is very large, the author does not do this here, we recommend that readers use similar methods to verify the lock)








/HOME/DB2INST1>DB2 +c Update employee set comm=9999 (sess#1)


/HOME/DB2INST1>DB2 +c SELECT * FROM Employee (Sess#2 is in lock wait)


/HOME/DB2INST1>DB2 +c get snapshot for locks on sample (Sess#3 monitor lock case)





Note: DB2 +c is not an autocommit (commit) SQL statement, you can also turn off autocommit (autocommit, by default, autocommit) by using C-off DB2 update command options


7 Summary


In general, there are major differences between DB2 locks and Oracle Locks:


1. Oracle has concurrent control through a multiple granularity blocking mechanism with intent locks to ensure data consistency. Its DML lock (data lock) is divided into two levels (granularity): Table-level and row-level. A typical DML operation obtains only an intent lock (RS or RX) at the table level, and its true blocking granularity is still at the row level; DB2 is also controlled by a multiple-granularity blocking mechanism with intent locks to ensure data consistency. Its DML lock (data lock) is divided into two levels (granularity): Table-level and row-level. The usual DML operation obtains only the intent lock (Is,six or IX) at the table level. Its true blocking granularity is also at the line level; In addition, simply reading data (SELECT) is not locked in an Oracle database, which increases the system's concurrency, and Oracle emphasizes the ability to "read" To data and is able to read data quickly. While the DB2 lock emphasizes "read consistency", the read data (SELECT) will be added according to the different isolation level (RR,RS,CS) and the S,is,is lock, only when using the UR isolation level is not locked. This ensures that the data read by different applications and users is consistent.


2. While supporting high concurrency, DB2 and Oracle have different control mechanisms for locks: Oracle uses design techniques such as intent lock and lock sign on data lines to reduce the overhead of Oracle maintenance line-level locks, which has some advantages in database concurrency control. In DB2, each lock in the locked memory (locklist) request to allocate a certain amount of memory space, specifically x lock 64 bytes of memory, S lock 32 bytes of memory (note: DB2 V8 before is x lock 72 bytes of memory and S lock 36 bytes of memory).


3. There is no lock escalation in the Oracle database, and lock escalation occurs when the use of row-level locks in the database table exceeds locklist*maxlocks in the DB2 database.


4. In Oracle when a session insert,update,delete a table, another session can still read the front image (before image) of the table from the Orace rollback segment or restore the table space; In DB2, when a session insert,update,delete the table, another session is still in the lock wait state when reading the table data, unless the uncommitted value of the first session can be read using the UR isolation level ; so oracle at the same time different sessions have read inconsistent phenomenon, and DB2 at the same time all sessions are "read consistent".


8 concluding remarks


DB2 on concurrency control (lock)


1. Properly adjust the database configuration parameters related to Locklist,maxlocks,dlchktime and locktimeout (locktimeout best Not equal to-1). If the lock memory is not enough, it will report SQL0912 error and affect concurrency.


2. Write efficient and concise SQL statements (very important).


3. Commit to releasing locks as quickly as possible after the business logic has been processed.


4. Create the most reasonable index for SQL statements that cause lock wait (SQL0911 return code 68) and Deadlock (SQL0911 return code 2) (It is important to create composite indexes and include indexes as much as possible).


5. Use the Locksize parameter of the AltER table statement to control how a particular table is locked on a persistent basis. Check the Locksize field in the Syscat.tables, as far as possible, in the case of business logic, the field in each table is "R" (row-level lock).


6. Use the correct isolation level (RR,RS,CS and UR) based on the business logic.


7. When a large number of updates are performed, the entire table is locked throughout the transaction (using the SQL lock table statement) before the update. This only uses a lock to prevent other transactions from making these updates, but it does reduce data concurrency for other users.