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.
•\ Create our own locks via the DBMS_LOCK package.
The following sections 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 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.
Summary
This chapter covered a lot of material that, at times, may have made you scratch your head. While locking is rather straightforward, some of its side effects are not. However, it is vital that you understand these issues. For example, if you were not aware of the table lock Oracle uses to enforce a foreign key relationship when the foreign key is not indexed, then your application would suffer from poor performance. If you did not understand how to review the data dictionary to see who was locking whom, you might never figure that one out. You would just assume that the database hangs sometimes. I sometimes wish I had a dollar for every time I was able to solve the insolvable hanging issue by simply running the query to detect unindexed foreign keys and suggesting that we index the one causing the problem. I would be very rich.
Leave a Reply