Oracle Database Row Lock Contention
Oracle Row Lock Contention
Database row lock contention
LOCKS:
- Locks are used by Oracle databases to provide data concurrency and integrity between transactions.
- Locking within an Oracle database is an entirely automatic mechanism that prevent destructive interaction
between transactions accessing the same resource
- There are several levels to locking, but the Oracle Database automatically uses the lowest applicable level of
restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity
Consistency and concurrency
- Data concurrency means that many users can access data at the same time.
- 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.
As DBA's, we need to have a good understand of what happens when transactions are issued against the database.
ACID Model:
- A transaction is a logical, atomic unit of work that contains one or more SQL statements.
- A transaction groups SQL statements so that they are either all committed, which means they are applied to
the database, or all rolled back, which means they are undone from the database.
- Oracle Database assigns every transaction a unique identifier called a transaction ID.
- A transaction can either be a DDL or a DML
A Transaction ends when on of the following occurs:
- A user runs a DDL command (Implicit commit)
- A user issues a COMMIT or ROLLBACK
- A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed (graceful shutdown)
- A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment (abort)
To ensure consistency and concurrency, the database uses the ACID model
ACID is an acronym for the following 4 key properties that define a transaction:
A - Atomicity: The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.
C - Consistency: The transaction takes the resources from one consistent state to another.
I - Isolation: A transaction's effect is not visible to other transactions until the transaction is committed.
The database must serialize concurrent access to data (Enqueue mechanism)
D - Durability: Changes made by the committed transaction are permanent and must survive system failure.
These 4 properties represent a set of database design principles that emphasize aspects of reliability
that are important for business data and mission-critical applications.
A TX lock therefore is acquired when a transaction initiates its first change and is held until the transaction
does a COMMIT or ROLLBACK.
It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete.
Oracle uses what we call an enqueue mechanism:
----------------------------------------------
which is is a sophisticated locking mechanism that permits several concurrent processes to share known
resources to varying degrees.
Enqueues coordinate parallel access to Oracle resources such as objects or data records.
So When multiple transactions need to lock the same resource, the first transaction to request the lock obtains it,
This enqueue mechanism is automatic and requires no administrator interaction.
It is embedded withing the internals of the oracle architecture
There are different levels of locking which can either be automatic or manual but we will not talk about those in this demo
DEMO
#oracle #locks #enqueue #database #locking
Related Videos:
---------------
Introduction to Structured Query Language SQL Oracle Database
• Introduction to Struct...
Follow me:
----------
LinkedIn: / atsohmofor
Twitter: / bindag
OR @Bindag
email: bobsukki@gmail.com
Telegram: NI TSOH
RU-vid: ru-vid.com/show-UCx06... OR NI TSOH
18 май 2022
