Concurrency controls are the collection of functions that the database provides to allow many people to access and modify data simultaneously. As noted in the previous two chapters, the lock is one of the core mechanisms by which Oracle regulates concurrent access to shared database resources and prevents interference between concurrent database transactions.
To briefly summarize, Oracle uses a variety of locks, including the following:
•\ TX (transaction) locks: These locks are acquired for the duration of a data-modifying transaction.
•\ TM (DML enqueue) and DDL locks: These locks ensure that the structure of an object is not altered while you are modifying its contents (TM lock) or the object itself (DDL lock).
•\ Latches and mutexes: These are internal locks that Oracle employs to mediate access to its shared data structures. We’ll refer to both as latches in this chapter, although they might be implemented by a mutex on your operating system, depending on the Oracle version.
In each case, there is minimal overhead associated with lock acquisition. TX transaction locks are extremely scalable both in terms of performance and cardinality. TM and DDL locks are applied in the least restrictive mode whenever possible. Latches and enqueues are both very lightweight and fast (enqueues are slightly the heavier of the two, though more feature-rich). Problems only arise from poorly designed applications that hold locks for longer than necessary and cause blocking in the database. If you design your code well, Oracle’s locking mechanisms will allow for scalable, highly concurrent applications.
Note I used the phrase “longer than necessary.” That does not mean you should attempt to commit (end your transaction) as soon as possible. Transactions should be exactly as long as they need to be—and no longer than that. That is, your transaction is your unit of work; it is all or nothing. You should commit when your unit of work is complete and not before—and not any later either!
But Oracle’s support for concurrency goes beyond efficient locking. It implements a multiversioning architecture that provides controlled yet highly concurrent access to data. Multiversioning describes Oracle’s ability to simultaneously materialize multiple versions of the data and is the mechanism by which Oracle provides read-consistent views of data (i.e., consistent results with respect to a point in time).
A rather pleasant side effect of multiversioning is that a reader of data will never be blocked by a writer of data. In other words, writes do not block reads. This is one of the fundamental differences between Oracle and other databases. A query that only reads information in Oracle will never be blocked; it will never deadlock with another session, and it will never get an answer that didn’t exist in the database.
Note There is a short period of time during the processing of a distributed two-phase commit where Oracle will prevent read access to information. As this processing is somewhat rare and exceptional (the problem applies only to queries that start between the prepare and the commit phases and try to read the data before the commit arrives), I will not cover it in detail.
Oracle’s multiversioning model for read consistency is applied by default at the statement level (for each and every query) and can also be applied at the transaction level. This means that each and every SQL statement submitted to the database sees a read-consistent view of the database, at least—and if you would like this read-consistent view of the database to be at the level of a transaction (a set of SQL statements), you may do that as well, as we’ll see in the “SERIALIZABLE” section in this chapter.
The basic purpose of a transaction in the database is to take the database from one consistent state to the next. The ISO SQL standard specifies various transaction isolation levels, which define how sensitive one transaction is to changes made by another. Thegreater the level of sensitivity, the greater the degree of isolation the database must provide between transactions executed by your application. In the following section, we’ll look at how, via its multiversioning architecture and with absolutely minimal locking, Oracle can support each of the defined isolation levels.
