locking

DBMS Locking Part I (DBMS only)
TECHNICAL ARTICLES -> PERFORMANCE ARTICLES
[ Back ] [ Next ]

DBMS is often criticized for excessive locking – resulting in poor database performance when sharing data among multiple concurrent processes. Is this criticism justified, or is DBMS being unfairly blamed for application design and implementation shortfalls? To evaluate this question, we need to understand more about DBMS locking protocols. In this article, we examine how, why, what and when DBMS locks and unlocks database resources. Future articles will address how to minimize the impact of database locking.

THE NEED FOR LOCKING

In an ideal concurrent environment, many processes can simultaneously access data in a DBMS database, each having the appearance that they have exclusive access to the database. In practice, this environment is closely approximated by careful use of locking protocols.

Locking is necessary in a concurrent environment to assure that one process does not retrieve or update a record that is being updated by another process. Failure to use some controls (locking), would result in inconsistent and corrupt data.

In addition to record locking, DBMS implements several other locking mechanisms to ensure the integrity of other data structures that provide shared I/O, communication among different processes in a cluster and automatic recovery in the event of a process or cluster failure. While these other lock structures use additional VMS lock resources, they rarely hinder database concurrency, but can actually improve database performance.

How DBMS Uses Locks

DBMS makes extensive use of the VMS Distributed Lock Manager for controlling virtually every aspect of database access. Use of the Distributed Lock Manager ensures cluster-wide control of database resources, thus allowing DBMS to take advantage of OpenVMS' clustering technology.

VMS locks consume system resources. A typical process, running a DBMS application may lock hundreds or thousands of records and database pages at a time. Using a VMS lock for each of these resources in a busy database could easily exhaust these resources. The system parameters: LOCKIDTBL, LOCKIDTBL_MAX, and REHASHTBL parameters determine the number of locks that exist on the system at any one time.

To minimize the number of VMS locks required to maintain record and page integrity, DBMS implements a technique called adjustable locking granularity. This allows DBMS to manage a group of resources (pages or records) using a single VMS lock. When a conflicting request is made for the same resource group, the process that is holding the lock is notified that it is blocking another process and automatically reduces the locking-level of the larger group.

Adjustable page locking is mandatory and hidden from the database administrator, while adjustable record locking can be enabled and tuned or disabled for each database. When adjustable record locking is enabled, DBMS attempts to minimize the number of VMS locks required to maintain database integrity without impacting database concurrency.

Types of Locks

DBMS employs many types of locks to ensure database integrity in a concurrent environment. By using various lock types for different functions, DBMS can provide optimal performance in many different environments.

Area Locks

DBMS uses area locks to implement the DML (Data Manipulation Language) READY statement. If a realm is readied by another run unit, later READY usage modes by other run-units must be compatible with all existing READY usage modes.

Area locks can significantly affect database concurrency – however, their impact is only felt during a DML READY statement. Lock conflicts for area locks occur only when you attempt to READY a realm. Once you successfully READY a realm, concurrent locking protocols (if required) are handled at the page and record level. Table I displays compatible area ready modes.

TABLE I – AREA READY MODE COMPATIBILITY TABLE 

First Run Unit	Second Run Unit
00	Concurrent Retrieval	Protected Retrieval	Concurrent Update	Protected Update	Exclusive
Concurrent Retrieval	GO	GO	GO	GO	WAIT
Protected Retrieval	GO	GO	WAIT	WAIT	WAIT
Concurrent Update	GO	WAIT	GO	WAIT	WAIT
Protected Update	GO	WAIT	WAIT	WAIT	WAIT
Exclusive	WAIT	WAIT	WAIT	WAIT	WAIT

Page Locks

Page locks are used to manage the integrity of the page buffer pool. DBMS automatically resolves page lock conflicts by using the blocking AST features of the VMS lock manager. Thus, page locks are not typically a major impediment to database concurrency unless long-DML verbs are frequently executed in your environment. DBMS utilizes adjustable locking to minimize the number of VMS locks required to maintain consistency of the buffer pool. A high level of blocking ASTs is an indication that there is a lot of contention for database pages in the buffer pool. Reducing the buffer length may help to reduce the overhead of page level blocking ASTs.

Record Locks

Record locks are typically the largest source of lock conflicts in a DBMS environment. Record locks are used to manage the integrity of your data, and to implement the "adjustable record locking granularity" feature of DBMS. Adjustable locking is the default for record locks, but can be tuned or disabled by the DBA.

Quiet Point Locks

Quiet point locks are used to control online database and afterimage journal backup operations. Large quiet point lock stall times indicate that processes are waiting for online backups to begin, or for the primary after-image journal file to be written to secondary storage. To minimize the effects (duration) of quiet point locks, it is important that all concurrent database processes (except for batch retrieval transactions) periodically execute commits (or commit retaining). Even "concurrent retrieval" transactions should periodically "commit [retaining]" their transactions. This ensures that the online backups will achieve a "quiet point" quickly and allow new transactions to proceed.

Freeze Locks

Freeze locks are used to stop (freeze) database activity during database process recovery. When a process terminates abnormally (as a result of a process or node failure, STOP/ID, or a CTRL-Y/STOP), all locks held by that process are automatically released. If transactions were allowed to continue, database corruption would result. Thus, when a process terminates abnormally, DBMS uses the freeze lock to stop database activity until the failed process(es) can be recovered. Freeze locks typically are not a major source of contention in most environments. However, if you are subject to frequent system or process failures, or users are using CTRL-Y/STOP to exit from programs, freeze locks could hinder database concurrency.

Database qualifiers

Several of the DBMS creation and modification qualifiers have a direct impact on database locking characteristics. Establishing the appropriate mix of qualifiers in your environment can help minimize the impact of database locking.

/HOLD_RETRIEVAL_LOCKS

The [no]hold_retrieval_locks qualifier, determines whether DBMS holds read-only record locks on all records read for the duration of the transaction (until the next COMMIT [without the RETAINING option] or ROLLBACK). Holding retrieval locks guarantees that any records previously read during a transaction will not have been changed by another run-unit during the same transaction. While this increases the consistency of your transaction, it can significantly degrade concurrency. This option should only be used if your transactions read very few records and consistency of all records read must be guaranteed throughout the transaction. By default, DBMS uses /NOHOLD_RETRIEVAL_LOCKS. The logical name, DBM$BIND_HOLD_RETRIEVAL_LOCKS may be used to override the default established in the root file. If DBM$BIND_HOLD_RETRIEVAL_LOCKS translates to "1" then all records read by the transaction are locked until the end of the transaction. Software Concepts International recommends against using hold retrieval locks in most environments.

/[NO]WAIT_RECORD_LOCKS

The [no]wait_record_locks qualifier determines whether a run-unit waits when requesting a record that is locked in a conflicting mode by another run-unit or if it receives a "lock conflict" exception. This qualifier only determines if the requesting run-unit will receive a "lock conflict" exception – not a "deadlock" exception (deadlock exceptions are always returned when they occur). When the default (WAIT_RECORD_LOCKS) is used, DBMS will not generate a "lock conflict" exception, and the blocked process will continue to wait until the record is unlocked. Thus, the process can continue to wait indefinitely until the record is unlocked by the other run-unit. 

The logical name, DBM$BIND_WAIT_RECORD_LOCKS may be used to override the default established in the root file. Again, a value of "1" enables wait on record lock conflicts, and a value of "0" causes the process to receive the "lock conflict" exception. Software Concepts International recommends clients to WAIT on record conflicts. This allows the application to trap for "deadlocks," and avoids "live-lock" situations that cannot be detected. In addition, the wait on record conflicts can be used with the /TIMEOUT to give the application control over records locked for an excessive duration.

/TIMEOUT=LOCK=seconds

The timeout qualifier allows you to specify the amount of time that a run-unit waits for a locked record before returning a "lock timeout" exception. This qualifier must be used with the "wait" on record locks (above). The logical name, DBM$BIND_LOCK_TIMEOUT_INTERVAL may be used to override the default established in the root file. The value of the translation determines the number of seconds to wait for a locked record. If your applications trap the ‘DBM$TIMEOUT’ exceptions, then Software Concepts International recommends using lock timeouts with a time of at least 60 seconds. Using the /TIMEOUT qualifier only if your application is designed to handle "lock timeout" exceptions. COBOL shops that use declaratives, may want to handle "DBM$_DEADLOCK", "DBM$LCKCNFLCT", and "DBM$TIMEOUT" exceptions in the same "USE" section.

/ADJUSTABLE_LOCKING

Enabling, disabling, or modifying the values of the adjustable locking features of DBMS will not significantly reduce record lock conflicts. However, adjustable locking can significantly affect the amount of lock resources your application uses, as well as the overall overhead associated with record locking.

The DBO/SHOW STATISTICS (record locking) screen provides useful insights into the potential benefits and costs of adjustable locking. If you observe a blocking AST rate that is more than 20-25% of the number of locks requested plus locks promoted, then this may indicate significant adjustable locking overhead. In this case, try disabling adjustable locking, or reducing the number of levels in its tree.

/[NO]LOCK_OPTIMIZATION

Lock optimization sounds so obvious. Who wouldn't want "lock optimization?" Lock optimization (the default) only controls whether area locks are held from one transaction to another. This avoids the overhead of acquiring and releasing locks for each transaction.

In environments where long DML verbs are frequently executed, lock optimization may actually degrade performance. This is because the process holding the lock does not release the NOWAIT lock until the end of its current DML verb. Thus, if the current DML verb takes a long time to complete, the process trying to ready the realm may experience a long delay.

/SNAPSHOTS=(option)

Snapshots are included in this discussion of locking, because the use of snapshots (batch retrieval transactions) can significantly reduce the level of lock contention in your database. Although snapshot transactions are subject to page and other resource lock conflicts, they are never involved in record lock conflicts – thus providing significantly increased concurrency between read-only and update transactions.

Enabling snapshots are not however a panacea – All update processes (except EXCLUSIVE or BATCH) must write before-images of their updates to the snapshot files. The use of /DEFERRED qualifier minimizes this affect by allowing update processes to write to the snapshot file only when snapshot transactions are active.

Buffer Count

Additional or excessive buffers require additional page level locking to manage the buffer pool. If you are using large buffer counts, you may need to increase the enque limits on your processes, as well as the SYSGEN parameters, LOCKIDTBL, LOCKIDTBL_MAX and REHASHTBL.

DBMS Lock Exceptions

DBMS signals one of three types of exceptions when a process encounters a locked record – a deadlock, a lock conflict or a lock timeout.

Deadlocks Exceptions

A deadlock exception, DBM$_DEADLOCK, is returned when two run-units attempt to access a resource in mutually exclusive modes, and each run-unit is waiting for a resource that the other run-unit holds. This indicates that neither run-unit can continue unless one of the run-units releases its locks. When a deadlock occurs, DBMS will choose a "victim," and signal that run-unit of the deadlock condition. This does not cause the "victim" to automatically release its locks. The victim process should immediately execute a 'rollback' to release its locks.

Lock Conflict Exceptions

DBMS will only return the lock conflict exception, DBM$_LCKCNFLCT, when the run-unit is bound to a database with "/NOWAIT_RECORD_LOCKS" enabled and it attempts to access a record that is locked in a mutually exclusive mode by another run-unit. Note, that only the "blocked" run-unit receives the exception.

Lock Timeout Exceptions

The third type of exception is the lock timeout exception, DBM$TIMEOUT. A lock timeout only occurs when the "/TIMEOUT=LOCK=nnn" and "/NOWAIT_RECORD_LOCKS" are enabled and a run-unit attempts to access a record that is locked in a mutually exclusive mode by another run-unit.

• Pulse
• Blog
• Wiki
• Oracle Architecture
• Database Administration
• Database Tuning
• Network Management
• PL/SQL Reference
• PL/SQL Coding Techniques
• PL/SQL Code Library
• How to Write and Edit Articles
• Podcasts
• Contributors
• Subscribe
• Log in

Programs (DBMS LOCK)

• Page
• Discussion
• View source
• History

From OraDBPedia

Jump to: navigation, search

[/w/res/OTOC231.html ALLOCATE_UNIQUE] [/w/res/OTOC231.html CONVERT] [/w/res/OTOC231.html RELEASE] [/w/res/OTOC231.html REQUEST] [/w/res/OTOC231.html SLEEP] The SLEEP procedure suspends the session for the number of seconds specified in the seconds parameter. Sleep periods can be specified with accuracy down to the hundredth of a second (e.g., 1.35 and 1.29 are recognized as distinct sleep times). Here's the header for this program: Contents [hide] 1 DBMS_LOCK.SLEEP 2 DBMS_LOCK.CONVERT 3 DBMS_LOCK.RELEASE 4 DBMS_LOCK.ALLOCATE_UNIQUE 5 DBMS_LOCK.REQUEST DBMS_LOCK.SLEEP view source print? 1.PROCEDURE DBMS_LOCK.SLEEP 2.    (seconds IN NUMBER); Remarks Applications using resources to which concurrent access is restricted may need to try again later if the resource is busy. The SLEEP procedure provides a mechanism for including low-overhead wait times into PL/SQL programs. After waiting, an application can retry the operation that failed to acquire the busy resource. Exceptions This program does not raise any package exceptions. Restrictions Do not specify a null value for the seconds parameter; this may result in an ORA-00600 error. Example The following SQL*Plus script displays a screen message and pauses for ten seconds before continuing: view source print? 1.prompt ************************************** 2.prompt * This is a very important message 3.prompt * ************************************ 4.  5.BEGIN 6.   DBMS_LOCK.SLEEP(10); 7.END; 8./ The CONVERT function is used to convert a previously acquired lock to the mode specified by the lockmode parameter. If the mode conversion cannot be granted within the specified time, the function call completes with a nonzero return value. CONVERT is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or a VARCHAR2 lockhandle. The headers for this program, corresponding to each type, follow: DBMS_LOCK.CONVERT view source print? 01.FUNCTION DBMS_LOCK.CONVERT 02.    (id IN INTEGER 03.    ,lockmode IN INTEGER 04.    ,timeout IN NUMBER DEFAULT MAXWAIT) 05.RETURN INTEGER; 06.  07.FUNCTION DBMS_LOCK.CONVERT 08.    (lockhandle IN VARCHAR2 09.    ,lockmode IN INTEGER 10.    ,timeout IN NUMBER DEFAULT MAXWAIT) 11.RETURN INTEGER; Parameter Description lockhandle The handle returned by ALLOCATE_UNIQUE lockmode The new lock mode of the request. Timeout Wait in a blocked mode for the lock request for this period of time. If timer expires before lock request is granted, return a value of 1. Returns view source print? 1.0 - success 2.1 - timeout 3.2 - deadlock 4.3 - parameter error 5.4 - don't own lock specified by 'id' or 'lockhandle' 6.5 - illegal lockhandle Remarks This procedure converts a lock to a different mode. The conversion may be up, for example from a shared lock to an exclusive lock. An example of a lock being down-graded is a conversion from exclusive to shared. It is safest to use the form of CONVERT that identifies the lock by a lockhandle (returned by [/w/res/OTOC231.html ALLOCATE_UNIQUE]). This minimizes the potential for inadvertent use of the same lock by different applications for different purposes, which is possible when locks are identified by integer values chosen by the application. Exceptions The program does not raise any package exceptions. Restrictions User-defined lock identifiers must be in the range 0 to 1073741823. Lock identifiers in the range 2000000000 to 2147483647 are reserved for use by Oracle Corporation. Examples The following anonymous PL/SQL block converts a previously acquired lock to null mode, reporting success or failure to the screen: view source print? 01.DECLARE 02.   call_status INTEGER; 03.BEGIN 04.   /* convert lock 9999 down to null mode with no wait */ 05.   call_status := DBMS_LOCK.CONVERT(9999,DBMS_LOCK.nl_mode,0); 06.  07.   IF call_status = 0 08.   THEN 09.      DBMS_OUTPUT.PUT_LINE('SUCCESS'); 10.   ELSE 11.      DBMS_OUTPUT.PUT_LINE('FAIL, RC = '||TO_CHAR(call_status)); 12.   END IF; 13.END; The next example below illustrates how to downgrade a lock. view source print? 01.-- Example 2 - HOLDER 02.-- 03.-- Procedure HOLDER is executed from one Oracle session first. 04.-- You pass it the time to sleep before downgrading the 05.-- exclusive lock. The HOLDER gets a lock for exclusive use and 06.-- then goes to sleep. You then run the REQUESTER from an 07.-- Oracle session. You can make the requester timeout or 08.-- successfully get the lock request by the amount of time 09.-- you wait before you run REQUESTER. 10.-- 11.CREATE OR REPLACE PROCEDURE HOLDER(s in NUMBER) IS 12.   handle VARCHAR2(128); 13.   status INTEGER; 14.BEGIN 15.   dbms_lock.allocate_unique( 'TEST_LOCK', handle ); 16.   status := dbms_lock.request( handle, dbms_lock.X_MODE ); 17.   dbms_lock.sleep( s ); 18.   status := dbms_lock.convert(handle, dbms_lock.SS_MODE); 19.END holder; 20.  21.-- Example 2 - REQUESTER 22.-- 23.-- Run this procedure from a separate Oracle session after 24.-- you run REQUESTER. 25.-- 26.CREATE OR REPLACE PROCEDURE requester IS 27.   handle VARCHAR2(128); 28.   status INTEGER; 29.BEGIN 30.   dbms_lock.allocate_unique( 'TEST_LOCK', handle ); 31.   status := dbms_lock.request( handle, dbms_lock.SS_MODE, 32.                                timeout => 10 ); 33.   IF ( status = 0 ) THEN 34.      dbms_output.put_line('TEST_LOCK in shared mode'); 35.   ELSE 36.      dbms_output.put_line('Timeout waiting for lock'); 37.   END IF; 38.END holder; The RELEASE function releases a previously acquired lock. RELEASE is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or a VARCHAR2 lockhandle. The program headers for each corresponding type follow: DBMS_LOCK.RELEASE view source print? 1.FUNCTION DBMS_LOCK.RELEASE 2.    (id IN INTEGER) 3.RETURN INTEGER; 4.  5.FUNCTION DBMS_LOCK.RELEASE 6.    (lockhandle IN VARCHAR2) 7.RETURN INTEGER; Parameter Description lockhandle The handle returned by ALLOCATE_UNIQUE Returns view source print? 1.0 - success 2.3 - parameter error 3.4 - don't own lock specified by 'id' or 'lockhandle' 4.5 - illegal lockhandle Remarks It is good practice to release locks as soon as possible. Doing so minimizes the potential for unnecessary wait times or deadlocks in applications where concurrent access to resources is serialized using DBMS_LOCK. Exceptions The program does not raise any package exceptions. Restrictions User-defined lock identifiers must be in the range 0 to 1073741823. Lock identifiers in the range 2000000000 to 2147483647 are reserved for use by Oracle Corporation. Example The following procedure calls the RELEASE function to relinquish control of the printer lock (see also the [/w/res/OTOC231.html example for the REQUEST function]): view source print? 01.PROCEDURE release_printer 02.   (return_code_OUT OUT INTEGER) 03.IS 04.   /* initialize variable with desired lockhandle */ 05.   temp_lockhandle printer_lockhandle%TYPE := get_printer_lockhandle; 06.  07.   call_status  INTEGER; 08.BEGIN 09.  10.   /* 11.   || release the printer lock 12.   */ 13.   call_status := DBMS_LOCK.RELEASE 14.                     (lockhandle => temp_lockhandle); 15.  16.   return_code_OUT := call_status; 17.END release_printer; The ALLOCATE_UNIQUE procedure returns a unique "handle" to a lock specified by the lockname parameter. The handle can be used to safely identify locks in calls to other DBMS_LOCK programs. Using lockhandles avoids the potential for lock identifier collisions that exists when identifiers are determined by applications. The header for this program follows: DBMS_LOCK.ALLOCATE_UNIQUE view source print? 1.PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE 2.    (lockname IN VARCHAR2 3.    ,lockhandle OUT VARCHAR2 4.    ,expiration_secs IN INTEGER DEFAULT 864000); Parameter Description lockname This is your identifier symbol for the resource you wish to lock or share with other processes. If you are requesting lock services on "DEVICE_X" you will make all subsequent lock calls, not specifying "DEVICE_X", but with the lockhandle for "DEVICE_X". lockhandle The lockhandle returned can be up to 128 bytes. This is your token for this resource about which you are requesting lock services. expiration_secs Oracle "cleans up" locks from the DBMS_LOCK_ALLOCATED table if no calls to ALLOCATE_UNIQUE have been made after this time. The default is 10 days. You should do your own clean up with a call to RELEASE or call REQUEST with the option RELEASE_ON_COMMIT. Remarks The procedure always performs a commit, hence make calls to this procedure during program initialization. This procedure returns a handle to the resource identified by LOCKNAME. Locks allocated using ALLOCATE_UNIQUE can be viewed in the Oracle data dictionary via the DBMS_LOCK_ALLOCATED view. It is good practice to avoid the possibility of lockname conflicts between applications by adopting standard naming conventions for locknames. Just as Oracle reserves names that begin with "ORA$", you may want to prefix locknames with your own company and application identifier string. The ALLOCATE_UNIQUE procedure needs to be called to identify a lockhandle for each named lock. This procedure issues a COMMIT, which presents some usability issues. For one, the procedure cannot be called from a database trigger, so using named locks from a database trigger requires that the lockhandle be acquired outside of the trigger and saved for use in the trigger. Another problem is the COMMIT itself: an application may want to utilize a named lock but not necessarily COMMIT the current transaction. Thus, it is desirable when using named locks to limit the number of calls to ALLOCATE_UNIQUE to exactly one call per named lock used. Exceptions The program does not raise any package exceptions. Restrictions Note the following restrictions on calling ALLOCATE_UNIQUE: Lock names can be up to 128 characters in length and are case-sensitive.   Lock names must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.   The ALLOCATE_UNIQUE procedure always performs a COMMIT, so it cannot be called from a database trigger. Example The following function returns the lockhandle of a specific named lock. It calls ALLOCATE_UNIQUE only if the lockhandle has not already been determined, and avoids the COMMIT unless it is necessary. The function manipulates global variables and thus needs to be included in a PL/SQL package. view source print? 01.PACKAGE BODY printer_access 02.IS 03.   /* global variables for lock name and handle */ 04.   printer_lockname  VARCHAR2(128) := 'printer_lock'; 05.   printer_lockhandle  VARCHAR2(128); 06.  07.   FUNCTION get_printer_lockhandle 08.   RETURN VARCHAR2 09.   IS 10.   BEGIN 11.      IF printer_lockhandle IS NULL 12.      THEN 13.         <b>DBMS_LOCK.ALLOCATE_UNIQUE 14.            (lockname => printer_lockname 15.            ,lockhandle => printer_lockhandle);</b> 16.      END IF; 17.  18.      RETURN printer_lockhandle; 19.   END get_printer_lockhandle; 20.  21.END printer_access; As illustrated in the example, it is a good idea to call ALLOCATE_UNIQUE only once for any given lockname per session. This is why the function stashes the lockhandle in the global variable, printer_lockhandle, and calls ALLOCATE_UNIQUE only if this global has not been initialized. There are two reasons for using this technique: efficiency and avoidance of extra COMMITs. Remember that ALLOCATE_UNIQUE will always return the same handle for a given lockname and that it always performs a COMMIT. Thus, best practice for using DBMS_LOCK includes calling ALLOCATE_UNIQUE only once per named lock. The next example below illustrates how to downgrade a lock. view source print? 01.-- Example 2 - HOLDER 02.-- 03.-- Procedure HOLDER is executed from one Oracle session first. 04.-- You pass it the time to sleep before downgrading the 05.-- exclusive lock. The HOLDER gets a lock for exclusive use and 06.-- then goes to sleep. You then run the REQUESTER from an 07.-- Oracle session. You can make the requester timeout or 08.-- successfully get the lock request by the amount of time 09.-- you wait before you run REQUESTER. 10.-- 11.CREATE OR REPLACE PROCEDURE HOLDER(s in NUMBER) IS 12.   handle VARCHAR2(128); 13.   status INTEGER; 14.BEGIN 15.   <b>dbms_lock.allocate_unique( 'TEST_LOCK', handle );</b> 16.   status := dbms_lock.request( handle, dbms_lock.X_MODE ); 17.   dbms_lock.sleep( s ); 18.   status := dbms_lock.convert(handle, dbms_lock.SS_MODE); 19.END holder; 20.  21.-- Example 2 - REQUESTER 22.-- 23.-- Run this procedure from a separate Oracle session after 24.-- you run REQUESTER. 25.-- 26.CREATE OR REPLACE PROCEDURE requester IS 27.   handle VARCHAR2(128); 28.   status INTEGER; 29.BEGIN 30.   <b>dbms_lock.allocate_unique( 'TEST_LOCK', handle );</b> 31.   status := dbms_lock.request( handle, dbms_lock.SS_MODE, 32.                                timeout => 10 ); 33.   IF ( status = 0 ) THEN 34.      dbms_output.put_line('TEST_LOCK in shared mode'); 35.   ELSE 36.      dbms_output.put_line('Timeout waiting for lock'); 37.   END IF; 38.END holder; The REQUEST function is used to acquire a lock in the mode specified by the lockmode parameter. If the lock cannot be acquired in the requested mode within the specified time, the function call completes with a nonzero return value. The REQUEST function is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or by a VARCHAR2 lockhandle. The release_on_commit parameter indicates whether the lock should persist across RDBMS transactions or be automatically released upon COMMIT or ROLLBACK. The headers for this program, corresponding to each type, are as follows: DBMS_LOCK.REQUEST view source print? 01.FUNCTION DBMS_LOCK.REQUEST 02.    (id IN INTEGER 03.    ,lockmode IN INTEGER DEFAULT X_MODE 04.    ,timeout IN INTEGER DEFAULT MAXWAIT 05.    ,release_on_commit IN BOOLEAN DEFAULT FALSE) 06.RETURN INTEGER; 07.  08.FUNCTION DBMS_LOCK.REQUEST 09.    (lockhandle IN VARCHAR2 10.    ,lockmode IN INTEGER DEFAULT X_MODE 11.    ,timeout IN INTEGER DEFAULT MAXWAIT 12.    ,release_on_commit IN BOOLEAN DEFAULT FALSE) 13.RETURN INTEGER; Parameter Description lockhandle The handle returned by ALLOCATE_UNIQUE lockmode The lock mode of the request. Timeout Wait in a blocked mode for the lock request for this period of time. If timer expires before lock request is granted, return a value of 1. release-on-commit True -- release on commit or rollback. False -- release only on explicit release of end-of-session. Returns view source print? 1.0 - success 2.1 - timeout 3.2 - deadlock 4.3 - parameter error 5.4 - already owned lock specified by 'id' or 'lockhandle' 6.5 - illegal lockhandle Remarks It is safest to use the form of REQUEST that identifies the lock by a lockhandle (returned by ALLOCATE_UNIQUE). This minimizes the potential for inadvertent use of the same lock by different applications for different purposes, which is possible when locks are identified by integer values chosen by the application. Sessions connected to Oracle using the multithreaded server configuration will not be released from their shared server until all held locks are released. Thus, be careful of specifying FALSE for the release_on_commit parameter in MTS (multithreaded server) environments, as holding locks for long periods could have a negative impact on MTS efficiency. Be sure that distributed transactions specify TRUE for the release_on_commit parameter. If a distributed transaction does not release locks after COMMIT, it is possible for a distributed deadlock to occur, which will be undetectable by either of the databases involved. When two sessions request locks with modes resulting in a deadlock, this is detected by Oracle, and one of the sessions is notified of the deadlock status. Exceptions The program does not raise any package exceptions. Restrictions User-defined lock identifiers must be in the range 0 to 1073741823. Lock identifiers in the range 2000000000 to 2147483647 are reserved for use by Oracle Corporation. Examples The following procedure calls the REQUEST function to get exclusive access to a lock designated to serialize access to a printer by Oracle sessions. It uses the get_printer_lockhandle function (see the example for the ALLOCATE_UNIQUE procedure) to identify the correct value for the lockhandle parameter. view source print? 01.PROCEDURE lock_printer 02.   (return_code_OUT OUT INTEGER) 03.IS 04.   /* initialize variable with desired lockhandle */ 05.   temp_lockhandle printer_lockhandle%TYPE := get_printer_lockhandle; 06.  07.   call_status  INTEGER; 08.BEGIN 09.  10.   /* 11.   || lock in exclusive mode, wait for up to 5 seconds 12.   */ 13.   call_status := <b>DBMS_LOCK.REQUEST 14.                     (lockhandle => temp_lockhandle 15.                     ,lockmode => DBMS_LOCK.x_mode 16.                     ,timeout => 5 17.                     ,release_on_commit => TRUE);</b> 18.  19.   return_code_OUT := call_status; 20.END lock_printer; The next example below illustrates how to downgrade a lock. view source print? 01.-- Example 2 - HOLDER 02.-- 03.-- Procedure HOLDER is executed from one Oracle session first. 04.-- You pass it the time to sleep before downgrading the 05.-- exclusive lock. The HOLDER gets a lock for exclusive use and 06.-- then goes to sleep. You then run the REQUESTER from an 07.-- Oracle session. You can make the requester timeout or 08.-- successfully get the lock request by the amount of time 09.-- you wait before you run REQUESTER. 10.-- 11.CREATE OR REPLACE PROCEDURE HOLDER(s in NUMBER) IS 12.   handle VARCHAR2(128); 13.   status INTEGER; 14.BEGIN 15.   dbms_lock.allocate_unique( 'TEST_LOCK', handle ); 16.   status := <b>dbms_lock.request( handle, dbms_lock.X_MODE );</b> 17.   dbms_lock.sleep( s ); 18.   status := dbms_lock.convert(handle, dbms_lock.SS_MODE); 19.END holder; 20.  21.-- Example 2 - REQUESTER 22.-- 23.-- Run this procedure from a separate Oracle session after 24.-- you run REQUESTER. 25.-- 26.CREATE OR REPLACE PROCEDURE requester IS 27.   handle VARCHAR2(128); 28.   status INTEGER; 29.BEGIN 30.   dbms_lock.allocate_unique( 'TEST_LOCK', handle ); 31.   status := <b>dbms_lock.request( handle, dbms_lock.SS_MODE, 32.                                timeout => 10 );</b> 33.   IF ( status = 0 ) THEN 34.      dbms_output.put_line('TEST_LOCK in shared mode'); 35.   ELSE 36.      dbms_output.put_line('Timeout waiting for lock'); 37.   END IF; 38.END holder;

A lock is an object associated with a shared resource such as a data item of an elementary type, a row in a database, or a page of memory. In a database, a lock on a database object (a data-access lock) may need to be acquired by a transaction before accessing the object. Correct use of locks prevents undesired, incorrect or inconsistent operations on shared resources by other concurrent transactions. When a database object with an existing lock acquired by one transaction needs to be accessed by another transaction, the existing lock for the object and the type of the intended access is checked by the system. If the existing lock type does not allow this specific attempted concurrent access type, the transaction attempting access is blocked (according to a predefined agreement/scheme). In practice a lock on an object does not directly block a transaction’ operation upon the object, but rather blocks that transaction from acquiring another lock on the same object, needed to be held/owned by the transaction before performing this operation. Thus, with a locking mechanism, needed operation blocking is controlled by a proper lock blocking scheme, which indicates which lock type blocks which lock type.

Two major types of locks are utilized:

• Write-lock (exclusive lock) is associated with a database object by a transaction (the transaction locks it; acquires lock for it) before writing (inserting/modifying/deleting) this object.
• Read-lock (shared lock) is associated with a database object by a transaction before reading (retrieving the state of) this object.

The common interactions between these lock types are defined by blocking behavior as follows:

• An existing write-lock on a database object blocks an intended write upon the same object (already requested/issued) by another transaction by blocking a respective write-lock from being acquired by the other transaction. The second write-lock will be acquired and the requested write of the object will take place (materialize) after the existing write-lock is released.
• A write-lock blocks an intended (already requested/issued) read by another transaction by blocking the respective read-lock .
• A read-lock blocks an intended write by another transaction by blocking the respective write-lock .
• A read-lock does not block an intended read by another transaction. The respective read-lock for the intended read is acquired (shared with the previous read) immediately after the intended read is requested, and then the intended read itself takes place.

Several variations and refinements of these major lock types exist, with respective variations of blocking behavior. If a first lock blocks another lock the two locks are incompatible; otherwise the locks are compatible. Often lock types blocking interactions are presented in the technical literature by a Lock compatibility table. The following is an example with the common, major lock types:

Lock compatibility table
Lock type	read-lock	write-lock
read-lock		X
write-lock	X	X

X indicates incompatibility, i.e, a case when a lock of the first type (in left column) on an object blocks a lock of the second type (in top row) from being acquired on the same object (by another transaction). An object typically has a queue of waiting requested (by transactions) operations with respective locks. The first blocked lock for operation in the queue is acquired as soon as the existing blocking lock is removed from the object, and then its respective operation is executed. If a lock for operation in the queue is not blocked by any existing lock (existence of multiple compatible locks on a same object is possible concurrently) it is acquired immediately.