Oracle concurrency control Overview

Oracle uses block technology to ensure the serializability of concurrent operations. Oracle locks fall into two categories: Data locks (also known as DML locks) and dictionary locks. Dictionary locks are used internally by Oracle DBMS to block dictionary tables. Dictionary locks include syntax analysis locks and DDL locks. When necessary, DBMS automatically locks and releases locks. You do not have permission to control them.

Oracle provides five data locks: Shared locks (S locks), exclusive locks (X), row-level shared locks (RS locks), and row-level exclusive locks (RX locks) and shared row-level exclusive locks (SRX locks ). The blocking granularity includes row-level and table-level. The Compatibility Matrix of Data locks is as follows:

T1T2 S X RS RX SRX - S Y N Y N N Y X N N N N N Y RS Y N Y Y Y Y RX N N Y Y N Y SRX N N Y N N Y - Y Y Y Y Y Y

Y = Yes, indicating compatible requests; N = No, indicating incompatible requests

Generally, data blocking is controlled by the system and transparent to users. However, Oracle allows you to use the lock table statement to explicitly LOCK the blocked object.

A notable feature of Oracle Data locks is that by default, read data is not locked. That is to say, when a user updates data, another user can read the corresponding data at the same time, and vice versa. Oracle ensures that users do not read "dirty" data and can re-read through the memory structure of the Rollback Segment. This improves the data concurrency.

Oracle provides an effective Deadlock Detection Mechanism to periodically diagnose deadlocks in the system. If deadlocks exist, the transactions with the least number of update operations are canceled.