Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Modes Of Locking

The LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.
Database locks are used to provide concurrency control in order to ensure data consistency and integrity.
A locked table remains locked until you either commit your transaction or roll it back and savepoint.
Ensure that only one user can modify a record at a time.
Ensure that a table cannot be dropped while another user is querying it.
Ensure that one user cannot delete a record while another is updating it.
  • DML locks (data locks)
  • DDL locks (dictionary locks)
  • Internal Locks/Latches
  • Distributed Locks
  • Parallel Cache Management Locks
  • Deadlocks
DML locks
      Row Level Locks [TX]

All DML locks Oracle acquires automatically are row-level locks.
Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.
Row-level locks serve a primary function to prevent multiple transactions from modifying the same row.

Table Level Lock [TM]
A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCKTABLE

A table lock can be held in several modes:
Row share (RS)
Row exclusive (RX)
Share (S)
Share row exclusive (SRX)
Exclusive (X)

DDL locks
            A DDL lock protects the definition of a schema object while the object is referenced in a DDL operation.
·         Exclusive DDL Locks
·         Shared DDL Locks
·         Breakable Parse Locks

            Oracle automatically acquires a DDL lock to prevent other DDL operations from referencing or altering the same object.
Internal locks
Data Dictionary Locks
Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.

File and Log Management Locks
Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.

Tablespace and Rollback Segment Locks
Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

            Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

Distributed Locks        
            A distributed lock manager (DLM) is a software component provided by your platform vendor.
            Distributed locks are held by a database instance, not by individual transactions.
            The Oracle Parallel Server uses a distributed lock manager to coordinate concurrent access to resources, such as data blocks and rollback segments, across multiple instances.

Parallel Cache Management Locks
            The instance locks which manage the locking of blocks in datafiles.         
            PCM locks ensure cache coherency by forcing instances to acquire a lock before modifying or reading any database block.

            A deadlock is the situation where you have two, or more, Oracle "sessions" (well, transactional "states") competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.

Manual Table Lock Modes
            LOCK TABLE table_name IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];
            For example:

ROW SHARE(RS) - Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
ROW EXCLUSIVE(RX) - Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.
SHARE(S) - Allows concurrent queries but users are prevented from updating the locked table.
SHARE ROW EXCLUSIVE(SRX) - Users can view records in table, but are prevented from updating the table or from locking the table in SHARE mode.
EXCLUSIVE(E) - Allows queries on the locked table, but no other activities.

Views to identify Locking Issues


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction