Oracle's Locking Mechanism: A Comparative Study from Oracle 11g to Oracle 13c

Oracle Database, as one of the leading relational database management systems, employs a robust locking mechanism to guarantee data integrity, consistency, and isolation across multiple concurrent transactions. These locking schemes are integral in preventing the undesirable effects of conflicting operations, such as overwriting data. This paper aims to explore the evolution of Oracle's locking mechanisms from version 11g to version 13c.

In Oracle 11g, the locking mechanism is primarily row-level, where locks are applied at the granularity of individual database rows. Types of locks include:

1. Shared Locks: These locks enable multiple transactions to read a row but prevent any from writing until the lock is released.
2. Exclusive Locks: This type of lock prevents other transactions from either reading or writing to the data until the lock is relinquished.
3. Intention Locks: These locks indicate a transaction's intent to acquire a specific type of lock in the future.

The locking in Oracle 11g also includes a variety of specialized locks, such as DML locks, DDL locks, and latches, each with a specific function and scope.

Oracle Database 13c builds upon the robust foundation laid by its predecessors but incorporates some nuanced improvements. For instance:

1. Enhanced Deadlock Detection: Oracle 13c has improved algorithms for detecting and resolving deadlocks, thus reducing the transaction time.
2. Adaptive Locking: Oracle 13c introduces mechanisms to adaptively adjust the granularity of locks depending on transaction patterns, allowing for optimized performance.
3. Partitioned Locks: One significant addition is the support for partition-specific locks, allowing more granular control for partitioned tables.
4. Advanced Queuing Locks: These are new types of locks that specifically deal with the advanced queuing operations, providing a more efficient way of handling messaging functionalities.

While the core principles underlying Oracle's locking mechanisms have largely remained consistent from Oracle 11g to Oracle 13c, the latter introduces optimizations and new features to better accommodate contemporary requirements for data integrity and performance. Thus, Oracle 13c can be seen as an evolutionary rather than a revolutionary step in the development of Oracle's locking schemes.
Oracle's locking mechanism, foundational in maintaining data integrity in concurrent transaction environments, has seen incremental improvements from version 11g to 13c. These improvements primarily focus on increasing efficiency, reducing transactional bottlenecks, and providing more granular control over data. While the core mechanisms have been preserved, the refinements introduced in Oracle 13c make it a more adaptive and intelligent system for managing locks in complex, multi-user, and multi-transactional environments. Therefore, organizations operating with Oracle databases should consider these evolutionary changes when planning upgrades or new implementations.

Oracle locking is used at two levels: Oracle locks are used by the Oracle database to ensure that database rows are not inadvertently written-over by concurrent tasks.

1. First, Oracle will automatically lock the target of all UPDATE statements for the duration of the task to ensure that no data inconsistency occurs.
2. Second, Oracle programmers can explicitly lock row and tables with Oracle's LOCK TABLE and SELECT FOR UPDATE clauses.

In this module we will explore the internal constructs of Oracle locking and see how appropriate locking can improve Oracle performance. By the end of this module, you'll be able to:

1. Describe Oracle's locking scheme
2. Describe the purpose of shared and exclusive Oracle locks
3. Describe Oracle lock modes
4. Identify an Oracle deadlock
5. Prevent a database deadlock
6. List the dictionary lock views
7. Run the lock utility scripts
8. Use the dbms_lock package

An Oracle database contains two types of locks:

1. shared and
2. exclusive.

The most common type of locks are shared locks that are issued using SQL SELECT statements, and exclusive locks that are issued with DELETE and UPDATE statements.
In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool.
Exclusive locks are issued for the duration of all 1) UPDATE or 2) DELETE statements to ensure that all modifications are single-threaded within the tables of the database. Oracle must obtain exclusive control of the segment header block whenever a row is inserted or deleted.

The size of a lock is managed internally by Oracle and the lock will be held by the database until a COMMIT, END, or ABORT message releases the lock. Oracle's locking schemes use a coexistence method ^[1]. Many clients may have shared locks against the same database, but shared locks cannot coexist with exclusive locks. Whenever an update event occurs, the database attempts to post an exclusive lock against the target row. To understand the difference between shared and exclusive locks, assume that a task wants to lock a row for update. The task will need to wait until all shared locks are released for this row before it can issue the exclusive lock. The exclusive lock will wait if any other tasks hold a shared lock against the target row. We will begin by looking at Oracle's overall locking scheme.

[1]coexistence method: A technique for ensuring coexistence between two methods for access to the Oracle database.