[Chapter 4] User Lock and Transaction ManagementChapter 4 4. User Lock and Transaction ManagementContents:DBMS_LOCK: Creating and Managing Resource LocksDBMS_TRANSACTION: Interfacing to SQL Transaction StatementsComplex, multiuser applications that manage new types of resources (objects, BLOBs, etc.) require the ability to manage contention for those resources. The Oracle database manages concurrent, multiuser contention for data using sophisticated locking mechanisms. This chapter describes two packages that provide interfaces to the Oracle lock and transaction management facilities:DBMS_LOCKOracle now provides developers with the "keys" to its locking mechanisms through the DBMS_LOCK package; watch out, though, this deceptively powerful package might also put your applications to "sleep!" DBMS_TRANSACTION Complements DBMS_LOCK by providing a programmatic interface to a number of transaction-oriented SQL statements.4.1 DBMS_LOCK: Creating and Managing Resource LocksThe DBMS_LOCK package makes Oracle lock management services available to PL/SQL developers. User locks created and managed using DBMS_LOCK are functionally identical to native RDBMS locks, even down to the various sharing modes and the deadlock detection.Locks are typically used to provide serialized access to some resource. Within the database, the most familiar use of locking is to prevent multiple users from updating the same row in a table at the same time. Using DBMS_LOCK, applications can be written that serialize and coordinate access or usage of nondata resources. For instance, user locks can be used to do the following:Provide exclusive access to an external device or service (e.g., a printer)Coordinate or synchronize parallelized applicationsDisable or enable execution of programs at specific timesDetect whether a session has ended a transaction using COMMIT or ROLLBACK4.1.1 Getting Started with DBMS_LOCKThe DBMS_LOCK package is created when the Oracle database is installed. The dbmslock.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction) contains the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_LOCK for the package. Under Oracle7, no privileges are automatically granted on DBMS_LOCK. Under Oracle8, the EXECUTE_CATALOG_ROLE role is granted EXECUTE privilege on DBMS_LOCK. Thus, the DBMS_LOCK programs are not generally available to users. Access to DBMS_LOCK is obtained by granting EXECUTE privilege explicitly to users or roles that require use of the package.4.1.1.1 DBMS_LOCK programsTable 4.1 lists the programs available in the DBMS_LOCK package.Table 4.1: DBMS_LOCK ProgramsNameDescriptionUse in SQL?ALLOCATE_UNIQUEGenerates a unique lock ID for a given lock nameNoCONVERTConverts lock to specified modeNoRELEASEReleases previously acquired lockNoREQUESTRequests lock with specified modeNoSLEEPSuspends the session for a specified timeNoDBMS_LOCK does not declare any package exceptions, and none of its programs assert a purity level with the RESTRICT_REFERENCES pragma.4.1.1.2 DBMS_LOCK nonprogram elementsDBMS_LOCK declares a number of constants, most of which identify specific locking modes. Table 4.2 describes these elements.Table 4.2: DBMS_LOCK Declared ConstantsConstantDescriptionnl_mode CONSTANT INTEGERNull lock modess_mode CONSTANT INTEGERSub-shared lock modesx_mode CONSTANT INTEGERSub-exclusive lock modes_mode CONSTANT INTEGERShared lock modessx_mode CONSTANT INTEGERSub-shared exclusive lock modex_mode CONSTANT INTEGERExclusive lock modemaxwait CONSTANT INTEGERUsed as default for timeout parameters4.1.1.3 Lock compatibility rulesA lock held by one user session in a certain mode may prevent another session from being able to obtain that lock in the same or another mode. There are lock compatibility rules determining the success or failure of acquiring and converting locks from one mode to another, depending on the modes in which the same lock is held by other sessions. Table 4.3 indicates the compatibility rules for the various lock modes. The HELD MODE column indicates the mode in which the lock is currently held by some session. The other columns indicate whether the lock can be obtained by other sessions in the mode specified by the column header. Table 4.3: Lock Mode CompatibilityHELD MODEGET NLGET SSGET SXGET SGET SSXGET XNLSucceedSucceedSucceedSucceedSucceedSucceedSSSucceedSucceedSucceedSucceedSucceedFailSXSucceedSucceedSucceedFailFailFailSSucceedSucceedFailSucceedFailFailSSXSucceedSucceedFailFailFailFailXSucceedFailFailFailFailFail4.1.2 The DBMS_LOCK InterfaceThis section contains descriptions of all of the procedures and functions available through DBMS_LOCK.4.1.2.1 The DBMS_LOCK.ALLOCATE_UNIQUE procedureThe 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:PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE (lockname IN VARCHAR2 ,lockhandle OUT VARCHAR2 ,expiration_secs IN INTEGER DEFAULT 864000);Parameters for this procedure are summarized in the following table.ParameterDescriptionlocknameName of the locklockhandleUnique handle to lock by nameexpiration_secsLength of time to leave lock allocated The program does not raise any package exceptions.4.1.2.1.1 RestrictionsNote 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.4.1.2.1.2 ExampleThe following function returns the lockhandle of a specific named lock. It calls ALLOCATE_UNIQUE only if the lockhandle has not already been determined, and avoid the COMMIT unless it is necessary. The function manipulates global variables and thus needs to be included in a PL/SQL package.PACKAGE BODY printer_access IS /* global variables for lock name and handle */ printer_lockname VARCHAR2(128) := 'printer_lock'; printer_lockhandle VARCHAR2(128); FUNCTION get_printer_lockhandle RETURN VARCHAR2 IS BEGIN IF printer_lockhandle IS NULL THEN DBMS_LOCK.ALLOCATE_UNIQUE (lockname => printer_lockname ,lockhandle => printer_lockhandle); END IF; RETURN printer_lockhandle; END get_printer_lockhandle; 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.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.4.1.2.2 The DBMS_LOCK.REQUEST functionThe 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 (see the parameter table).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:FUNCTION DBMS_LOCK.REQUEST (id IN INTEGER ,lockmode IN INTEGER DEFAULT X_MODE ,timeout IN INTEGER DEFAULT MAXWAIT ,release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER; FUNCTION DBMS_LOCK.REQUEST (lockhandle IN VARCHAR2 ,lockmode IN INTEGER DEFAULT X_MODE ,timeout IN INTEGER DEFAULT MAXWAIT ,release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;Parameters for this function are summarized in the following table.ParameterDescriptionidNumeric identifier of the locklockhandleHandle for lock returned by DBMS_LOCK.ALLOCATE_UNIQUElockmodeLocking mode requested for locktimeoutTime in seconds to wait for successful conversionrelease_on_commitIf TRUE, release lock automatically on COMMIT or ROLLBACKThe following table summarizes the return values of the function. Return ValueDescription0Success1Timed out2Deadlock3Parameter error4Do not own lock; cannot convert5Illegal lockhandleThe program does not raise any package exceptions.4.1.2.2.1 RestrictionsUser-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.4.1.2.2.2 ExampleThe 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.PROCEDURE lock_printer (return_code_OUT OUT INTEGER) IS /* initialize variable with desired lockhandle */ temp_lockhandle printer_lockhandle%TYPE := get_printer_lockhandle; call_status INTEGER; BEGIN /* || lock in exclusive mode, wait for up to 5 seconds */ call_status := DBMS_LOCK.REQUEST (lockhandle => temp_lockhandle ,lockmode => DBMS_LOCK.x_mode ,timeout => 5 ,release_on_commit => TRUE); return_code_OUT := call_status; END lock_printer;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.4.1.2.3 The DBMS_LOCK.CONVERT functionThe 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 (see the following parameter table). 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:FUNCTION DBMS_LOCK.CONVERT (id IN INTEGER ,lockmode IN INTEGER ,timeout IN NUMBER DEFAULT MAXWAIT) RETURN INTEGER; FUNCTION DBMS_LOCK.CONVERT (lockhandle IN VARCHAR2 ,lockmode IN INTEGER ,timeout IN NUMBER DEFAULT MAXWAIT) RETURN INTEGER;Parameters for this program are summarized in the following table.ParameterDescriptionidNumeric identifier of the locklockhandleHandle for lock returned by ALLOCATE_UNIQUElockmodeLocking mode to which to convert the lock timeoutTime in seconds to wait for successful conversionThe return values for this function are summarized in the following table. Return ValueDescription0Success1Timed out2Deadlock3Parameter error4Do not own lock, cannot convert5Illegal lockhandleThe program does not raise any package exceptions.4.1.2.3.1 RestrictionsUser-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.4.1.2.3.2 ExampleThe following anonymous PL/SQL block converts a previously acquired lock to null mode, reporting success or failure to the screen:DECLARE call_status INTEGER; BEGIN /* convert lock 9999 down to null mode with no wait */ call_status := DBMS_LOCK.CONVERT(9999,DBMS_LOCK.nl_mode,0); IF call_status = 0 THEN DBMS_OUTPUT.PUT_LINE('SUCCESS'); ELSE DBMS_OUTPUT.PUT_LINE('FAIL, RC = '||TO_CHAR(call_status)); END IF; END;See the discussion in the "Section 4.1.2.2.2, "Example"" section for the Section 4.1.2.2, "The DBMS_LOCK.REQUEST function"; all of that discussion also applies to CONVERT.4.1.2.4 The DBMS_LOCK.RELEASE functionThe 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:FUNCTION DBMS_LOCK.RELEASE (id IN INTEGER) RETURN INTEGER; FUNCTION DBMS_LOCK.RELEASE (lockhandle IN VARCHAR2) RETURN INTEGER;Parameters are summarized in the following table.ParameterDescriptionidNumeric identifier of the locklockhandleHandle for lock returned by ALLOCATE_UNIQUEThe return values for this function are summarized in the following table.Return ValueDescription0Success3Parameter error4Do not own lock; cannot release5Illegal lockhandleThe program does not raise any package exceptions.4.1.2.4.1 RestrictionsUser-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.4.1.2.4.2 ExampleThe following procedure calls the RELEASE function to relinquish control of the printer lock (see also the example for the REQUEST function):PROCEDURE release_printer (return_code_OUT OUT INTEGER) IS /* initialize variable with desired lockhandle */ temp_lockhandle printer_lockhandle%TYPE := get_printer_lockhandle; call_status INTEGER; BEGIN /* || release the printer lock */ call_status := DBMS_LOCK.RELEASE (lockhandle => temp_lockhandle); return_code_OUT := call_status; END release_printer;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.4.1.2.5 The DBMS_LOCK.SLEEP procedureThe 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:PROCEDURE DBMS_LOCK.SLEEP (seconds IN NUMBER);4.1.2.5.1 ExceptionsThis program does not raise any package exceptions.WARNING: The following nasty Oracle exception was raised on Windows NT when the SLEEP procedure was called with a NULL value for seconds: ORA-00600: internal error code, arguments: [15454], [0], [ ], [ ], [ ], [ ], [ ], [ ].4.1.2.5.2 RestrictionsDo not specify a null value for the seconds parameter; this may result in an ORA-00600 error, as noted previously.4.1.2.5.3 ExampleThe following SQL*Plus script displays a screen message and pauses for ten seconds before continuing:prompt ************************************** prompt * This is a very important message prompt * ************************************ BEGIN DBMS_LOCK.SLEEP(10); END; /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.4.1.3 Tips on Using DBMS_LOCKIn this section I've pulled together a number of best practices for using the DBMS_LOCK package.4.1.3.1 Named locks or lock ids?Oracle provides two methods of identifying and manipulating user locks: integer lock identifiers and handles for named locks. Using names and lockhandles to identify locks is considered safer than using integer identifiers directly because naming standards can be adopted to virtually guarantee that different applications will not use the same lock for different purposes. Therefore, best practices for using DBMS_LOCK include the use of named locks and lockhandles.4.1.3.2 Issues with named locksThere are a couple of drawbacks to using named locks that are worth pointing out. In particular:Named locks are recorded in the catalog, and thus may be slower.The DBMS_LOCK.ALLOCATE_UNIQUE procedure issues a COMMIT.Applications need to keep track of lockhandles for each named lock used.It is worth investigating these drawbacks and developing techniques to minimize their impact, thus further encouraging the use of named locks.4.1.3.3 Performance of named locksWe can investigate the performance penalty for using named locks, and quantify that penalty in a relatively straightforward manner. Consider the following PL/SQL script:/* Filename on companion disk: lock1.sql */* set timing on set serveroutput on size 100000 DECLARE lockname VARCHAR2(30) := 'OPBIP_TEST_LOCK_10'; lockhandle VARCHAR2(128); lockid INTEGER := 99999; call_status INTEGER; timer NUMBER; BEGIN /* || timed test using lockhandles */ timer := DBMS_UTILITY.GET_TIME; DBMS_LOCK.ALLOCATE_UNIQUE(lockname,lockhandle); FOR i IN 1..10000 LOOP call_status := DBMS_LOCK.REQUEST(lockhandle,timeout=>0); call_status := DBMS_LOCK.RELEASE(lockhandle); END LOOP; DBMS_OUTPUT.PUT_LINE('Using lockhandles: '|| TO_CHAR(ROUND((DBMS_UTILITY.GET_TIME-timer)/100,2)) ||' secs'); /* || timed test using lockids */ timer := DBMS_UTILITY.GET_TIME; FOR i IN 1..10000 LOOP call_status := DBMS_LOCK.REQUEST(lockid,timeout=>0); call_status := DBMS_LOCK.RELEASE(lockid); END LOOP; DBMS_OUTPUT.PUT_LINE('Using lockids: '|| TO_CHAR(ROUND((DBMS_UTILITY.GET_TIME-timer)/100,2)) ||' secs'); END;The PL/SQL block reports on the elapsed times to request and release a lock 10,000 times using either a lockhandle or an integer lock identifier. The test yielded the following results on a Personal Oracle7 database with no other activity:SQL> @l2 Using lockhandles: 9.57 secs Using lockids: 3.02 secs PL/SQL procedure successfully completed. real: 12740 SQL> spool offThese results confirm that use of lockhandles is significantly slower than use of lock identifiers. However, the results also indicate that the overhead of named locks was less than one-thousandth of a second per usage. Thus, the performance impact of using named locks is negligible and is probably not a legitimate concern for most applications.4.1.3.4 ALLOCATE_UNIQUE drawbacksThe other issues mentioned with named locks are usage related. 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.4.1.3.5 Optimizing named locksOne way to achieve the objective of minimizing calls to ALLOCATE_UNIQUE is to use private package global variables to store lockhandles for each named lock. A function that will return the lockhandle can then be written, calling ALLOCATE_UNIQUE only if the lockhandle has not been previously identified. This technique is illustrated as follows:PACKAGE BODY print_pkg IS /* private globals for lock identification */ printer_lockname VARCHAR2(128) := 'printer_lock'; printer_lockhandle VARCHAR2(128); FUNCTION get_printer_lockhandle RETURN VARCHAR2 IS BEGIN IF printer_lockhandle IS NULL THEN DBMS_LOCK.ALLOCATE_UNIQUE (lockname => printer_lockname ,lockhandle => printer_lockhandle); END IF; RETURN printer_lockhandle; END get_printer_lockhandle; END print_pkg;Using this technique ensures that the ALLOCATE_UNIQUE procedure is called only once per session requiring use of the printer lock. The lock can even be used in a database trigger if the function get_printer_lockhandle has been called prior to the triggering event.One drawback to this technique is code redundancy: each named lock used by an application requires adding a specific package global variable for the lockhandle and an associated function to return it. Referencing a new named lock in an application involves adding a nontrivial amount of code before it can be used.4.1.3.6 REQUEST or CONVERT?Another usability issue with DBMS_LOCK (not specific to named locks): applications using multiple lock modes need to have intelligence about whether to call the REQUEST function or the CONVERT function. If the user has requested and received a lock in a specific mode, then that mode can only be changed by calling CONVERT. On the other hand, a lock conversion can only take place if it is preceded by a successful call to REQUEST. Getting it right can mean developing code that checks and tracks return codes from the calls to these two procedures.4.1.4 DBMS_LOCK ExamplesIn response to the usability issues described in the previous section, I have developed a utility package called dblock to simplify, and consequently encourage, the use of named locks in PL/SQL applications. 4.1.4.1 The dblock packageThe dblock package specification follows:/* Filename on on companion disk: dblock.sql */* CREATE OR REPLACE PACKAGE dblock /* || Adds value to DBMS_LOCK by allowing easier manipulation || of named locks. Calling programs use lock names only, || corresponding lockhandles are automatically identified, || used and saved for subsequent use. || || || Author: John Beresniewicz, Savant Corp || || 10/26/97: added expiration_secs_IN to lockhandle || 10/21/97: added release || 10/21/97: added dump_lockhandle_tbl || 10/17/97: created || || Compilation Requirements: || || EXECUTE on DBMS_LOCK || EXECUTE on DBMS_SESSION || || Execution Requirements: || */ AS /* variables to anchor other variables */ lockname_var VARCHAR2(128); lockhandle_var VARCHAR2(128); /* || returns TRUE if a COMMIT has taken place between || subsequent calls to the function || NOTE: returns TRUE on first call in session */ FUNCTION committed_TF RETURN BOOLEAN; /* || returns lockhandle for given lockname, only calls || DBMS_LOCK.ALLOCATE_UNIQUE if lockhandle has not been || previously determined */ FUNCTION lockhandle (lockname_IN IN lockname_var%TYPE ,expiration_secs_IN IN INTEGER := 864000) RETURN lockhandle_var%TYPE; /* || returns TRUE if named lock is acquired in mode || specified */ FUNCTION get_lock_TF (lockname_IN IN lockname_var%TYPE ,mode_IN IN INTEGER := DBMS_LOCK.x_mode ,timeout_IN IN INTEGER := 1 ,release_on_commit_TF IN BOOLEAN := FALSE) RETURN BOOLEAN; /* releases named lock */ PROCEDURE release (lockname_IN IN lockname_var%TYPE); /* print contents of lockhandle_tbl for debugging */ PROCEDURE dump_lockhandle_tbl; END dblock;The dblock programs allow the user to identify and acquire locks by name only. Lockhandles for each named lock are managed within the package, transparent to the application. The package associates locknames with lockhandles by using a private global PL/SQL table called lockhandle_tbl. The table is defined as follows: /* rectype to pair handles with names */ TYPE handle_rectype IS RECORD (name lockname_var%TYPE ,handle lockhandle_var%TYPE ); /* table to store lockhandles by name */ TYPE handle_tbltype IS TABLE OF handle_rectype INDEX BY BINARY_INTEGER; lockhandle_tbl handle_tbltype;4.1.4.1.1 The lockhandle function The lockhandle function takes a lockname as an IN parameter and returns the associated lockhandle. If the lockhandle has already been identified and stored in the lockhandle_tbl table, it is returned directly. Otherwise, DBMS_LOCK.ALLOCATE_UNIQUE is called to determine the lockhandle, which is then stored in lockhandle_tbl and is also returned to the caller. Here is the body of lockhandle:/* Filename on companion disk: dblock.sql */* FUNCTION lockhandle (lockname_IN IN lockname_var%TYPE ,expiration_secs_IN IN INTEGER := 864000) RETURN lockhandle_var%TYPE IS call_status INTEGER; temp_lockhandle lockhandle_var%TYPE; temp_index BINARY_INTEGER; BEGIN /* || if lockhandle_tbl empty must call ALLOCATE_UNIQUE */ IF lockhandle_tbl.COUNT = 0 THEN DBMS_LOCK.ALLOCATE_UNIQUE (lockname => lockname_IN ,lockhandle => temp_lockhandle ,expiration_secs => expiration_secs_IN); lockhandle_tbl(1).handle := temp_lockhandle; lockhandle_tbl(1).name := lockname_IN; /* || check lockhandle_tbl for matching lockname */ ELSE FOR i IN lockhandle_tbl.FIRST..lockhandle_tbl.LAST LOOP IF lockhandle_tbl(i).name = lockname_IN THEN temp_lockhandle := lockhandle_tbl(i).handle; END IF; END LOOP; END IF; /* || if temp_lockhandle still null, call ALLOCATE_UNIQUE || and load entry into lockhandle_tbl */ IF temp_lockhandle IS NULL THEN DBMS_LOCK.ALLOCATE_UNIQUE (lockname => lockname_IN ,lockhandle => temp_lockhandle); /* || add to end of lockhandle_tbl */ temp_index := lockhandle_tbl.LAST+1; lockhandle_tbl(temp_index).handle := temp_lockhandle; lockhandle_tbl(temp_index).name := lockname_IN; END IF; RETURN temp_lockhandle; END lockhandle;The lockhandle function alone is enough to make using named locks much easier. It relieves the programmer of having to create lockhandle variables for each named lock and also guarantees that the ALLOCATE_UNIQUE procedure is called only once per named lock. New named locks can be used immediately without coding supporting routines, as these are handled generically in the function. Furthermore, the lockhandle function can be invoked directly in calls to REQUEST or CONVERT. In the following procedure, the printer_lockname variable holds the name of a lock being used to serialize access to a printer: PROCEDURE get_printer_lock (lock_status_OUT OUT INTEGER) IS BEGIN lock_status_OUT := DBMS_LOCK.REQUEST (dblock.lockhandle(printer_lockname)); END get_printer_lock;4.1.4.1.2 get_lock_TF functionApplications using DBMS_LOCK usually must check return values from calls to the REQUEST or CONVERT functions to determine if access to the locked resource has been acquired. The dblock package includes a function called get_lock_TF, which takes a lockname and lock mode as IN parameters and returns the Boolean value TRUE if the named lock has been acquired in the desired mode. Using get_lock_TF, we can write code like the following: IF dblock.get_lock_TF (printer_lockname,DBMS_LOCK.x_mode) THEN /* invoke print routine here */ ELSE /* cannot print, tell user to try later */ END IF;Code like this is far easier to understand and maintain than code that calls DBMS_LOCK programs directly. All the complexity of using DBMS_LOCK is eliminated; the program merely calls get_lock_TF and proceeds directly to appropriate logic based on the return value. Here is the body of get_lock_TF:/* Filename on companion disk: dblock.sql */* FUNCTION get_lock_TF (lockname_IN IN lockname_var%TYPE ,mode_IN IN INTEGER := DBMS_LOCK.x_mode ,timeout_IN IN INTEGER := 1 ,release_on_commit_TF IN BOOLEAN := FALSE) RETURN BOOLEAN IS call_status INTEGER; /* handle for the named lock */ temp_lockhandle lockhandle_var%TYPE := lockhandle(lockname_IN); BEGIN call_status := DBMS_LOCK.REQUEST (lockhandle => temp_lockhandle ,lockmode => mode_IN ,timeout => timeout_IN ,release_on_commit => release_on_commit_TF ); /* || if lock already owned, convert to requested mode */ IF call_status = 4 THEN call_status := DBMS_LOCK.CONVERT (lockhandle => temp_lockhandle ,lockmode => mode_IN ,timeout => timeout_IN ); END IF; RETURN (call_status = 0); END get_lock_TF;Notice that get_lock_TF first calls REQUEST and then CONVERT if the lock is already owned. This relieves the programmer of yet another bit of housekeeping, and the return value accurately reflects whether the lock is owned in the requested mode. The temp_lockhandle variable is used in the calls to DBMS_LOCK programs to avoid calling the lockhandle function more than once.4.1.4.1.3 The committed_TF and release functionsThe dblock package also includes a procedure called release, which releases a named lock, and a function called committed_TF. The latter demonstrates using the release_on_commit parameter of the REQUEST function to determine whether a COMMIT has taken place in the session. The body of committed_TF looks like this: /* Filename on companion disk: dblock.sql */* /* used by committed_TF, unique to each session */ commit_lockname lockname_var%TYPE := DBMS_SESSION.UNIQUE_SESSION_ID; FUNCTION committed_TF RETURN BOOLEAN IS call_status INTEGER; BEGIN /* get unique lock, expire in one day */ call_status := DBMS_LOCK.REQUEST (lockhandle => lockhandle(commit_lockname,86400) ,lockmode => DBMS_LOCK.x_mode ,timeout => 0 ,release_on_commit => TRUE); RETURN (call_status = 0); END committed_TF;The committed_TF function uses a named lock called commit_lockname that is unique to each session, having been initialized by calling DBMS_SESSION.UNIQUE_SESSION_ID. It then calls DBMS_LOCK.REQUEST to acquire an exclusive lock on commit_lockname, making sure to specify TRUE for the release_on_commit parameter. Once the lock has been acquired initially, the success of subsequent calls indicates that the lock has been released, and thus a COMMIT (or ROLLBACK) has taken place. The function is probably not that useful in practice, but it makes a nice academic exercise.4.1.4.2 Using locks to signal service availabilityOne way in which DBMS_LOCK can be usefully employed is to indicate the availability of service programs to database sessions. The basic steps are quite simple:Assign specific locks to the server and/or each service provided.The server process holds the lock(s) in exclusive mode when services are available.Client programs request the lock to determine service availability.To make this more concrete, the following code fragments might be part of a package used to coordinate access to a computation server called calcman:PACKAGE calcman IS /* the actual service provider program */ PROCEDURE calcman_driver; /* function called by clients to determine availability */ FUNCTION calcman_available RETURN BOOLEAN; END calcman; PACKAGE BODY calcman IS /* lock name used to flag service availability */ calcman_lockname VARCHAR2(100):= 'CALCMAN_LOCK'; PROCEDURE calcman_driver IS BEGIN /* || get the special lock in exclusive mode */ IF dblock.get_lock_TF (lockname_IN => calcman_lockname ,mode_IN => DBMS_LOCK.x_mode ,timeout_IN => 1 ,release_on_commit_TF => FALSE) THEN /* || execute the service loop here, which probably || involves listening on a database pipe for || service requests and sending responses on pipes */ /* || loop forever and process calc requests */ WHILE NOT terminate_TF LOOP receive_unpack_calc_request (timeout_IN => DBMS_PIPE.maxwait ,request_rec_OUT=> request_rec ,return_code_OUT => temp_return_code); IF temp_return_code != 0 THEN DBMS_PIPE.PURGE(request_pipe); ELSE process_request(request_rec); END IF; END LOOP; ELSE /* service is already running in another process, exit */ RETURN; END IF: END calcman_driver; FUNCTION calcman_available RETURN BOOLEAN IS got_lock BOOLEAN; BEGIN got_lock := dblock.get_lock_TF (lockname => calcman_lockname ,mode_IN => DBMS_LOCK.sx_mode ,timeout_IN => 0 ,release_on_commit_TF => TRUE); /* || do not hold lock, this could conflict with || starting service */ dblock.release(calcman_lockname); /* failure to get lock indicates server available */ RETURN NOT got_lock; END calcman_available; END calcman;The calcman_driver procedure grabs and holds the lock as long as it is executing. If the lock is not available within one second, the procedure is already running in another session and exits silently in the current session. Thus, the lock ensures that only one calcman_driver will be executing at any time. Note the importance of not releasing the lock at COMMIT, ensuring that the lock is held as long as the service process is alive. The service can make itself unavailable at any time by simply releasing the lock.The service that calcman_driver provides is not specified in the previous code fragments. It could be a complex calculation requiring large PL/SQL tables for which the overhead of having all users execute the calculation individually is too great. Or it could be connected to an external service routine of some kind. A fuller discussion of how to implement such service procedures using database pipes can be found in Chapter 3, Intersession Communication.Client programs call the calcman_available function to determine whether the server is executing and providing its computation services. The function attempts to get the lock and, if it succeeds, this indicates that the service is not available. The lock is requested in shared mode exclusive; as a consequence, concurrent calls to the get_lock_TF function from different sessions may all succeed and indicate unavailability. If the lock is requested in exclusive mode, there is a chance that simultaneous execution of the function by two users could falsely indicate to one user that the service is available. The calcman_available function also releases the lock immediately to keep it from interfering with the calcman_driver program, which is attempting to secure the lock.3.2 DBMS_ALERT: Broadcasting Alerts to Users4.2 DBMS_TRANSACTION: Interfacing to SQL Transaction Statements Copyright (c) 2000 O'Reilly & Associates. All rights reserved.