Database Reference
In-Depth Information
When reading about mutexes in various reports, just remember that they are lighter-weight serialization devices.
They enable possibly more scalability than a latch (just as latches are more scalable than enqueues), but they are still
a serialization device.
If you can avoid doing something that requires a mutex, in general, you should, for the same
reason you would avoid requesting a latch if possible.
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 performing 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 ourselves are changing them. It uses latches and locks internally to protect its
own structure.
Next, let's take a look at how we can get involved in some of this locking action. Our options are as follows:
•
Manually lock data via a SQL statement.
DBMS_LOCK
package.
The following sections briefly discuss why you might want to do each of these.
•
Create our own locks via the
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 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 to enforce business rules (e.g., the resource scheduler example from Chapter 1).
We can also manually lock data using the
LOCK TABLE
statement. This statement is 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, this 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.
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.
