Chapter 3. Locking and Concurrency - Part 2
From Expert Oracle, Signature Edition: Programming Techniques and Solutions for Oracle 7.3 through 8.1.7, Berkeley, Apress, April 2005.
Types of Lock
The five general classes of locks in Oracle are listed below. The first three are common (used in every Oracle database) and the last two are unique to OPS (Oracle Parallel Server). We will introduce the OPS-specific locks, but will concentrate on the common locks:
- DML locks – DML stands for Data Manipulation Language, in general SELECT, INSERT, UPDATE, and DELETE. DML locks will be, for example, locks on a specific row of data, or a lock at the table level, which locks every row in the table.
- DDL locks – DDL stands for Data Definition Language, in general CREATE, ALTER, and so on. DDL locks protect the definition of the structure of objects.
- Internal locks and latches – These are the locks Oracle uses to protect its internal data structures. For example, when Oracle parses a query and generates an optimized query plan, it will ‘latch’ the library cache in order to put that plan in there for other sessions to use. A latch is a lightweight low-level serialization device employed by Oracle – similar in function to a lock.
- Distributed locks – These are used by OPS to ensure that resources on the various nodes remain consistent with respect to each other. Distributed locks are held by a database instance, not by individual transactions.
- PCM (Parallel Cache Management) Locks – These are locks that protect one or more cached data blocks in the buffer cache across multiple instances.
We will now take a more detailed look at the specific types of locks within each of these general classes and implications of their use. There are more lock types than I can cover here. The ones I am covering are the most common ones and are the ones that are held for a long duration. The other types of lock are generally held for very short periods of time.
DML Locks
DML locks are used to ensure only one person at a time modifies a row, and that no one can drop a table upon which you are working. Oracle will place these locks for you, more or less transparently, as you do work.
TX – (Transaction) Locks
A TX lock is acquired when a transaction initiates its first change, and is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queueing mechanism so that other sessions can wait for the transaction to complete. Each and every row you modify or SELECT FOR UPDATE will ‘point’ to an associated TX lock. While this sounds expensive, it is not. To understand why, we need a conceptual understanding of where locks ‘live’ and how they are managed. In Oracle, locks are stored as an attribute of the data (see Chapter 2, Architecture, for an overview of the Oracle block format). Oracle does not have a traditional lock manager that keeps a big long list of every row that is locked in the system. Many other databases do it that way because, for them, locks are a scarce resource the use of which needs to be monitored. The more locks in use, the more they have to manage so it is a concern in these systems if ‘too many’ locks are being used.
If Oracle had a traditional lock manager, the act of locking a row would resemble:
- Find the address of the row you want to lock.
- Get in line at the lock manager (must be serialized, it is a common in-memory structure.)
- Lock the list.
- Search through the list to see if anyone else has locked this row.
- Create a new entry in the list to establish the fact that you have locked the row.
- Unlock the list.
Now that you have the row locked, you can modify it. Later, as you commit your changes you must:
- Get in line again.
- Lock the list of locks.
- Search through it and release all of your locks.
- Unlock the list.
As you can see, the more locks acquired, the more time spent on this operation, both before and after modifying the data. Oracle does not do it that way. Oracle does it more like this:
- Find the address of the row you want to lock.
- Go to the row.
- Lock it (waiting for it if it is already locked, unless we are using the NOWAIT option).
That’s it, period. Since the lock is stored as an attribute of the data, Oracle does not need a traditional lock manager. Our transaction will simply go to the data and lock it (if it is not locked already). The interesting thing is that the data may appear locked when we get to it, even if it is not. When we lock rows of data in Oracle a transaction ID is associated with the bock containing the data and when the lock is released, that transaction ID is left behind. This transaction ID is unique to our transaction, and represents the rollback segment number, slot, and sequence number. We leave that on the block that contains our row to tell other sessions that ‘we own this data’ (not all of the data on the block, just the one row we are modifying). When another session comes along, it sees the lock ID and, using the fact that it represents a transaction, it can quickly see if the transaction holding the lock is still active. If it is not active, the data is theirs. If it is still active, that session will ask to be notified as soon as it finishes. Hence, we have a queueing mechanism: the session requesting the lock will be queued up waiting for that transaction to complete and then it will get the data.
Here is a small example showing how this happens. We will use three V$ tables in order to see how this works:
- V$TRANSACTION, which contains an entry for every active transaction/
- V$SESSION, which shows us the sessions logged in.
- V$LOCK, which contains an entry for all locks being held as well as for sessions that are waiting on locks.
First, let’s start a transaction:
tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.
Now, let’s look at the state of the system at this point:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = ‘TX’
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160
The interesting points to note here are:
- The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the definition of the V$LOCK table in the Oracle Server Reference Manual, you will find that LMODE=6 is an exclusive lock. A value of 0 in the request means we are not making a request – we have the lock.
- There is only one row in this table. This V$LOCK table is more of a queueing table than a lock table. Many people expect there would be four rows in V$LOCK since we have four rows locked. What you must remember however is that Oracle does not store a master list of every row locked anywhere. To find out if a row is locked, we must go to that row.
- I took the ID1 and ID2 columns, and performed some manipulation on them. Oracle needed to save three 16 bit numbers, but only had two columns in order to do it. So, the first column ID1 holds two of these numbers. By dividing by 2^16 with trunc(id1/power (2,16)) rbs and by masking out the high bits with bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot, I am able to get the two numbers that are hiding in that one number back out.
- The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my transaction ID.
Now I’ll start another session using the same user name, update some rows in EMP, and then try to update DEPT:
tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.
tkyte@TKYTE816> update dept set deptno = deptno-10;
I am now blocked in this session. If we run the V$ queries again, we see:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = ‘TX’
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
TKYTE 9 2 46 160 0 6
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
2 46 160
What we see here is that a new transaction has begun, with a transaction ID of (3,82,163). Our new session, SID=9, has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a request for an exclusive lock. The interesting thing to note here is that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with SID=8 is blocking the transaction with SID=9. We can see this more explicitly simply by doing a self-join of V$LOCK:
tkyte@TKYTE816> select
(select username from v$session where sid=a.sid) blocker,
2 a.sid,
3 ‘ is blocking ‘,
4 (select username from v$session where sid=b.sid) blockee,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and a.id2 = b.id2
11 /
BLOCKER SID ‘ISBLOCKING’ BLOCKEE SID
-------- ---------- ------------- -------- ----------
TKYTE 8 is blocking TKYTE 9
Now, if we commit our original transaction, SID=8, and rerun our query, we find that the request row has gone:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
5 id2 seq,
6 lmode,
7 request, block
8 from v$lock, v$session
9 where v$lock.type = ‘TX’
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
The request row disappeared the instant the other session gave up its lock. That request row was the queuing mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed.
There are infinitely more ‘pretty’ displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is very useful.
However, before we can say that we have a good understanding of how
the row locking in Oracle works we must look at one last piece, that
is, how the locking and transaction information is managed with the
data itself. It is part of the block overhead. In Chapter 2, Architecture,
we discussed how the basic format of a block included some leading
‘overhead’ space in which to store a transaction table for that block.
This transaction table contains an entry for each ‘real’ transaction
that has locked some data in that
block. The size of this structure is controlled by two physical attribute parameters on the CREATE statement for an object:
- INITRANS – the initial, pre-allocated size of this structure. This defaults to 2 for indexes and 1 for tables.
- MAXTRANS – the maximum size to which this structure may grow. It defaults to 255.
So, each block starts life with, by default, one or two transaction slots. The number of simultaneous active transactions that a block can ever have is constrained by the value of MAXTRANS, and by the availability of space on the block. You may not be able to achieve 255 concurrent transactions on the block if there is not sufficient space to grow this structure.
We can artificially demonstrate how this works by creating a table with a constrained MAXTRANS. For example:
tkyte@TKYTE816> create table t ( x int ) maxtrans 1;
Table created.
tkyte@TKYTE816> insert into t values ( 1 );
1 row created.
tkyte@TKYTE816> insert into t values ( 2 );
1 row created.
tkyte@TKYTE816> commit;
Commit complete.
Now, in one session we issue:
tkyte@TKYTE816> update t set x = 3 where x = 1;
1 row updated.
and in another:
tkyte@TKYTE816> update t set x = 4 where x = 2;
Now, since those two rows are undoubtedly on the same database block and we set MAXTRANS (the maximum degree of concurrency for that block) to one, the second session will be blocked. This demonstrates what happens when more than MAXTRANS transactions attempt to access the same block simultaneously. Similarly, blocking may also occur if the INITRANS is set low and there is not enough space on a block to dynamically expand the transaction. In most cases the defaults of 1 and 2 for INITRANS is sufficient as the transaction table will dynamically grow (space permitting), but in some environments you may need to increase this setting to increase concurrency and decrease waits. An example of when you might need to do this would be a table, or even more frequently, on an index(since index blocks can get many more rows on them than a table can typically hold) that is frequently modified. We may need to increase INITRANS to set aside ahead of time sufficient space on the block for the number of expected concurrent transactions. This is especially true if the blocks are expected to be nearly full to begin with, meaning there is no room for the dynamic expansion of the transaction structure on the block.
TM – (DML Enqueue) Locks
These locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If they attempt to perform DDL on the table while you have a TM lock on it, they will receive the following error message:
drop table dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
This is a confusing messag, at first, since there is no method to specify NOWAIT or WAIT on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be blocked, but the operation does not permit blocking. As we’ve seen before, it is the same message that you get if you issue a SELECT FOR UPDATE NOWAIT against a locked row.
Below, we see how these locks would appear to us in the V$LOCK table:
tkyte@TKYTE816> create table t1 ( x int );
Table created.
tkyte@TKYTE816> create table t2 ( x int );
Table created.
tkyte@TKYTE816> insert into t1 values ( 1 );
1 row created.
tkyte@TKYTE816> insert into t2 values ( 1 );
1 row created.
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 id1, id2,
4 lmode,
5 request, block, v$lock.type
6 from v$lock, v$session
7 where v$lock.sid = v$session.sid
8 and v$session.username = USER
9 /
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
-------- ---------- ---------- ---------- ------- ---------- ---------- --
TKYTE 8 24055 0 3 0 0 TM
TKYTE 8 24054 0 3 0 0 TM
TKYTE 8 327697 165 6 0 0 TX
tkyte@TKYTE816> select object_name, object_id from user_objects;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T1 24054
T2 24055
Whereas we only get one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DMLlocked object so it easy to find the object on which the lock is being held.
An interesting aside to the TM lock: the total number of TM locks
allowed in the system is configurable by you (for details, see the
DML_LOCKS init.ora parameter definition in the Oracle8i Server Reference
manual). It may in fact be set to zero. This does not mean that your
database becomes a read-only database (no locks), but rather that DDL
is not permitted. This is useful in very specialized applications, such
as OPS, to reduce the amount of intra-instance coordination that would
otherwise take place. You can also remove the ability to gain TM locks
on an objectbyobject basis, using the ALTER TABLE
TABLENAME DISABLE TABLE LOCK command.
DDL Locks
DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTERTABLE T, the table T will have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table. DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). It is for this reason that DDL always commits in Oracle. Every CREATE, ALTER, and so on, statement is really executed as shown in this pseudo-code:
Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;
So, DDL will always commit, even if it is unsuccessful. DDL starts by committing – be aware of this. It commits first so that if it has to rollback, it will not roll back your transaction. If you execute DDL, it’ll make permanent any outstanding work you have performed, even if the DDL is not successful. If you need to execute DDL, but do not want it to commit your existing transaction, you may use an autonomous transaction (see Chapter 15, Autonomous Transactions, for further details).
There are three types of DDL locks:
- Exclusive DDL locks – These prevent other sessions from gaining a DDL lock or TM (DML) lock themselves. This means that you may query a table during a DDL operation but may not modify it in any way.
- Share DDL locks – This protects the structure of the referenced object against modification by other sessions, but allows modifications to the data.
- Breakable Parse locks – This allows an object, such as a query plan cached in the shared pool, to register its reliance on some other object. If you perform DDL against that object, Oracle will review the list of objects that have registered their dependence, and invalidate them. Hence, these ‘locks’ are ‘breakable’; they do not prevent the DDL from occurring.
Most DDL takes an exclusive DDL lock. If you issue a statement such as:
Alter table t add new_column date;
the table T will be unavailable for modifications during the execution of that statement. The table may be queried using SELECT during this time, but most other operations will be prevented, including all DDL statements. In Oracle 8i, some DDL operations may now take place without DDL locks. For example, I can issue:
create index t_idx on t(x) ONLINE;
The ONLINE keyword modifies the method by which the index is actually built. Instead of taking an exclusive DDL lock, preventing modifications of data, Oracle will only attempt to acquire a low-level (mode 2) TM lock on the table. This will effectively prevent other DDL from taking place, but will allow DML to occur normally. Oracle accomplishes this feat by keeping a record of modifications made to the table during the DDL statement, and applying these changes to the new index as it finishes the CREATE. This greatly increases the availability of data.
Other types of DDL take share DDL locks. These are taken out against dependent objects when you create stored, compiled objects, such as procedures and views. For example, if you execute:
Create view MyView
as
select *
from emp, dept
where emp.deptno = dept.deptno;
Share DDL locks will be placed against both EMP and DEPT, while the CREATE VIEW command is being processed. We can modify the contents of these tables, but we cannot modify their structure.
The last type of DDL lock is a breakable parse lock. When our session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a referenced object is dropped or altered in some way.
A view that is invaluable for looking at this information is the DBA_DDL_LOCKS view. There is no V$ view for us to look at. The DBA_DDL_LOCKS view is built on the more mysterious X$ tables and, by default, it will not be installed in your database. You can install this and other locking views by running the CATBLOCK.SQL script found in the directory [ORACLE_HOME]/rdbms/admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example in a single user database I see:
tkyte@TKYTE816> select * from dba_ddl_locks;
session mode mode
id OWNER NAME TYPE held reqe
------- ------ ------------------------------ -------------------- ---- ----
8 SYS DBMS_APPLICATION_INFO Body Null None
8 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Body Null None
8 TKYTE TKYTE 18 Null None
8 SYS DATABASE 18 Null None
6 rows selected.
These are all the objects that my session is ‘locking’. I have breakable parse locks on a couple of the DBMS_* packages. These are a side effect of using SQL*PLUS; it calls DBMS_APPLICATION_INFO, for example. I may see more than one copy of various objects here – this is normal, and just means I have more than one thing I’m using in the shared pool that references these objects. It is interesting to note that in the view, the OWNER column is not the owner of the lock; rather it is the owner of the object being locked. This is why you see many SYS rows with – SYS owns these packages, but they all belong to my session.
To see a breakable parse lock in action, we will first create and run a stored procedure, P:
tkyte@TKYTE816> create or replace procedure p as begin null; end;
2 /
Procedure created.
tkyte@TKYTE816> exec p
PL/SQL procedure successfully completed.
The procedure, P, will now show up in the DBA_DDL_LOCKS view. We have a parse lock on it:
tkyte@TKYTE816> select * from dba_ddl_locks;
session mode mode
id OWNER NAME TYPE held reqe
------- ------ ------------------------------ -------------------- ---- ----
8 TKYTE P Table/Procedure/Type Null None
8 SYS DBMS_APPLICATION_INFO Body Null None
8 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Body Null None
8 TKYTE TKYTE 18 Null None
8 SYS DATABASE 18 Null None
7 rows selected.
We then recompile our procedure, and query the view again:
tkyte@TKYTE816> alter procedure p compile;
Procedure altered.
tkyte@TKYTE816> select * from dba_ddl_locks;
session mode mode
id OWNER NAME TYPE held reqe
------- ------ ------------------------------ -------------------- ---- ----
8 SYS DBMS_APPLICATION_INFO Body Null None
8 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Body Null None
8 TKYTE TKYTE 18 Null None
8 SYS DATABASE 18 Null None
6 rows selected.
We find that P is now missing from the view – our parse lock has been broken.
This view is useful to you, as a developer, when it is found that some piece of code won’t compile in the test or development system – it hangs and eventually times out. This indicates that someone else is using it (actually running it) and you can use this view to see who that might be. The same will happen with GRANTS and other types of DDL against the object. You cannot grant EXECUTE on a procedure that is running, for example. You can use the same method as above to discover the potential blockers and waiters.
Latches and Internal Locks (Enqueues)
Latches and enqueues are lightweight serialization devices used to coordinate multi-user access to shared data structures, objects and files.
Latches are locks that are held for extremely short periods of time, for example the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool (as described in Chapter 2, Architecture). Latches are typically requested internally in a ‘willing to wait’ mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an ‘immediate’ mode, meaning that the process will go do something else rather than sit and wait for the latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the ‘luck of the draw’, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters, just a ‘mob’ of waiters constantly retrying.
Oracle uses atomic instructions like ‘test and set’ for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets it. Since it is only one instruction, it can be quite fast. Latches are held for short periods of time and provide a mechanism for clean-up in case a latch holder ‘dies’ abnormally while holding it. This cleaning up process would be performed by PMON.
Enqueues are another, more sophisticated, serialization device, used
when updating rows in a database table, fro example. They differ from
latches in that they allow the requestor to ‘queue up’ and wait for the
resource. With a latch request, the requestor is told right away
whether they got the latch or not. With an enqueue, the requestor will
be blocked until they actually attain it. As such, they are not as fast
as a latch can be, but they do provided functionality over and above
that which a latch can offer. Enqueues may be obtained at various
levels, so you can have many ‘share’ locks and locks with various
degrees of ‘shareability’.
Manual Locking and User-Defined Locks
So far we have looked mostly at locks that Oracle places for us transparently. When we update a table, Oracle places a TM lock on it to prevent other sessions from dropping that table (or perform most DDL in fact). We have TX locks that are left on the various blocks we modify so others can tell what data we ‘own’. The database employs DDL locks to protect objects from change while we are ourselves changing them. It uses latches and locks internally to protect its own structure. Now, let’s take a look at how we can get involved in some of this locking action. Our options are:
- Manually locking data via a SQL statement.
- Creating our own locks via the DBMS_LOCK package.
We will briefly discuss why you might want to do each of these.
Manual Locking
We have, in fact, already seen a couple of cases where we might want to use manual locking. The SELECT...FOR UPDATE statement is the predominant method of manually locking data. We used it in previous examples in order to avoid the lost update issue, whereby one session would overwrite another session’s changes. We’ve seen it used as a method to serialize access to detail records in order to enforce business rules (the resource scheduler example from Chapter 1, Developing Successful Oracle Applications).
We can also manually lock data using the LOCK TABLE statement. This
is actually only used rarely, because of the coarseness of the lock. It
simply locks the table, not the rows in the table. If you start
modifying the rows they will be ‘locked’ as normal. So, it is not a
method to save on resources (as it might be in other RDBMSs). You might
use the LOCK TABLE IN EXCLUSIVE MODE statement if you were writing a
large batch update that would affect most of the rows in a given table,
and you wanted to be sure that no one would ‘block’ you. By locking the
table in this manner, you can be assured that your update will be able
to do all of its work without getting blocked by other transactions. It
would be the
rare application however that has a LOCK TABLE statement in it.
Creating your own Locks
Oracle actually exposes to developers the enqueue lock mechanism that it uses internally, via the DBMS_LOCK package (which we will cover in much more detail in Appendix A). You might be wondering why you would want to create your own locks. The answer is typically application-specific. For example, you might use this package to serialize access to some resource external to Oracle. Say you are using the UTL_FILE routine that allows you to write to a file on the server’s file system. You might have developed a common message routine that every application calls to record messages. Since the file is external, Oracle won’t coordinate the many users trying to modify it simultaneously. In comes the DBMS_LOCK package. Now, before you open, write, and close the file, you will request a lock named after the file in exclusive mode and after you close the file, you will manually release the lock. In this fashion, only one person at a time will be able to write a message to this file. Everyone else will queue up. The DBMS_LOCK package allows you to manually release a lock when you are done with it, or to give it up automatically when you commit, or even to keep it as long as you are logged in.
--
Thomas Kyte is the Vice President of the Core Technologies Group at Oracle Corporation and has been with them since version 7.0.9 was released in 1993. Kyte, however, has been working with Oracle since version 5.1.5c. At Oracle Corp., Kyte works with the Oracle database, and more specifically, he helps clients who are using the Oracle database and works directly with them specifying and building their systems or rebuilding and tuning them. Prior to working at Oracle, Kyte was a systems integrator who built large scale, heterogeneous databases and applications for military and government clients. Tom Kyte is the same “Ask Tom” whose column appears in Oracle Magazine, where he answers questions about the Oracle database and tools that developers and DBAs struggle with every day.
Contributors : Tom Kyte
Last modified 2005-12-01 02:42 PM
