This paper provides an overview of the locking mechanism management, data consistency, and concurrent access control to data in Oracle database. Each DBMS vendor implements some sort of locking mechanism that ensures data integrity in the database. Therefore, if a DBA wants to write applications that can work with different kinds of database servers, he/she must adapt the program logic to the behavior of the database servers regarding concurrency and consistency management. This requires good knowledge of multi-user application programming, transactions, locking mechanisms, isolation levels and wait mode.

Lock management is a complex issue, affecting data integrity and overall system performance. Locks enable a multi-user DBMS to maintain the integrity of transactions by isolating a transaction from others executing concurrently. Locking data for reads can be important in certain situations, preventing inconsistent analysis of the database. While DBMSs use exclusive locks for writes, share locks are commonly used for reads. Share locks enable other concurrently executing transactions to read the same data but prohibit any transaction from writing the chosen data.

Essentially, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.
The three are common locks used in every Oracle database system are:
  • DML locks – 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 – 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.
Oracle’s automatic locking can be overridden at two levels: That is, Per Session and per Transaction.
To set for a single transaction, first statement in transaction is:
SET TRANSACTION READ ONLY
SET TRANSACTION ISOLATION LEVEL READ COMMITTED (default)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

To set for all subsequent transactions within session:
ALTER SESSION ISOLATION LEVEL READ COMMITTED
ALTER SESSION ISOLATION LEVEL SERIALIZABLE

Transactions that include the following SQL statements override Oracle’s default locking:
  • The SET TRANSACTION ISOLATION LEVEL statement
  • The SELECT ... FOR UPDATE statement
  • The LOCK TABLE statement (which locks either a table or, when used with a view, the underlying base table) e.g. " LOCK TABLE EMP IN EXCLUSIVE MODE”
Although locks can have a significant impact on performance, their benefit on a given application to lock a large amount of data in perhaps one or more tables and hold these locks for a long period of time, inhibits concurrency and increases the likelihood that other applications will have to wait for locked resources. Likewise, locking only small amounts of data and releasing these locks quickly may be inappropriate for some applications, increasing the overhead associated with transaction processing. In addition, certain integrity problems can arise if a single transaction acquires locks after some have already been released.

Another main concern of a multi-user database management system is how to control concurrency, which is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity. If many people are accessing the same data, one way of managing data concurrency is to make each user wait for a turn. The goal of a database management system is to reduce that wait so it is either nonexistent or negligible to each user. Oracle resolves such issues by using various types of locks and a multiversion consistency control. Serialization is also an important concept associated with locking and concurrency control, which is a solution to provide the highest level of isolation between the actions of different users accessing the same data (a situation termed as serializable).

Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users Multiversion read consistency guarantees that a user sees a consistent view of the data he or she requests. To manage the multiversion consistency control, Oracle must create a read-consistent set of data when a table is being queried (read) and simultaneously updated (written). If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began When an update occurs, the original data values changed by the update are recorded in the database's undo records. As long as this update remains part of an uncommitted transaction, any user that later query the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table's data for a query.

A simple way to think of Oracle read consistency is to imagine each user operating a private copy of the database.
  • Readers do not wait for Writers (or other readers of the same data)
  • Writers do not wait for Readers (of the same data) 
  • Writers only wait for other Writers if they attempt to update identical rows in concurrent transactions.
 In addition, Oracle guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution. However, The consistency model for Oracle Database guarantees statement-level read consistency, but does not guarantee transaction-level read consistency (repeatable reads). If you want transaction-level read consistency, and if your transaction does not require updates, then you can specify a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any database table, knowing that the results of each query in the read-only transaction are consistent with respect to a single point in time.

References:
Greenwald, R., Stackowiak, R., & Stern, J. (2004). Oracle essentials 3rd. Oracle 10
Sebastropol, CA: O'Reilly

Rob, P. & Coronel, C. M. (2004). "Database Systems: Design, Implementation, & Management, 6th Ed." Massachusetts: Thomson Technology

Stair R., Reynolds, G. (2006). “Principles of Information Systems, 7th Ed.” Massachusetts: Thomson Technology

Saracco, C. M. and Bontempo Charles J. Database Management: Principles and
Products (Prentice Hall, 1995). ISBN 0-13-380189-6.

Locking and Concurrency:
Retrieved on August 11, 2006
http://www.dbazine.com/oracle/or-articles/kyte3/

SET TRANSACTION
Retrieved on August 12, 2006
http://www.microolap.com/products/database/pgisql/help/pghelp/sql-set-transaction.html

Oracle Essentials: Oracle8 & Oracle8i. By Rick Greenwald, Robert Stackowiak & Jonathan Stern
Retrieved on August 12, 2006 http://www.oreilly.com/catalog/oressentials/chapter/ch07.html

Rokytskyy, R. Origins of conflict
http://www.ibphoenix.com/main.nfs?page=ibp_mvcc_roman