Oracle's Locking Mechanism: A Comparative Study from Oracle 12c to Oracle 19c

Oracle's fundamental locking mechanisms have not significantly changed from Oracle Database version 12c to 19c. Oracle has long employed a multi-version read consistency model and a sophisticated locking scheme that emphasizes concurrency and data integrity. This model allows readers and writers to access data simultaneously without interfering with each other, minimizing locking conflicts and improving performance.
Key Points:

1. Multi-Version Read Consistency: Both Oracle 12c and 19c use multi-version read consistency to provide a consistent view of the data at a point in time. This approach reduces the need for read locks and allows transactions to proceed without waiting for others to complete.
2. Row-Level Locking: Oracle continues to use row-level locking rather than page or table-level locking. This fine-grained locking mechanism enhances concurrency by allowing multiple transactions to modify different rows of the same table simultaneously.
3. Automatic Lock Management: Oracle automatically manages locks at the lowest level necessary and handles lock escalation internally. This behavior remains consistent between 12c and 19c.

Enhancements and New Features:
While the core locking mechanisms remain the same, Oracle Database 19c introduces several enhancements that can impact performance and concurrency:

• Optimized Concurrency Controls: Oracle 19c includes performance optimizations that may improve how locks are managed under high-concurrency situations, but these do not fundamentally change the locking scheme.
• Advanced Indexing Techniques: Features like Automatic Indexing in 19c can indirectly affect locking behavior by changing how data is accessed and modified.
• In-Memory Enhancements: Improvements to the In-Memory Column Store can influence data access patterns, potentially impacting locking in specific scenarios.

Conclusion: The fundamental locking scheme in Oracle Database has remained consistent between versions 12c and 19c. While there are enhancements and new features in 19c that may affect locking behavior in particular use cases, the core principles and mechanisms governing locks have not undergone significant changes.

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.