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.
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.
TYPES
- 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
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.
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.
Deadlocks
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:
LOCK TABLE EMP IN SHARE MODE NOWAIT;
Lock_mode
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
V$LOCK
V$LOCKED_OBJECT
DBA_BLOCKERS
DBA_WAITERS
DBA_LOCK
DBA_DDL_LOCKS
DBA_DML_LOCKS
DBA_LOCK_INTERNAL
DBA_KGLLOCK
V$LOCKED_OBJECT
DBA_BLOCKERS
DBA_WAITERS
DBA_LOCK
DBA_DDL_LOCKS
DBA_DML_LOCKS
DBA_LOCK_INTERNAL
DBA_KGLLOCK
Comments
Post a Comment