Oracle’s Transactions use of Oracle Locking feature
Locks are used to prevent destructive interactions between transactions by allowing orderly access to resources. These objects can be database objects such as tables or shared database structures in memory.
Oracle locks can be broadly classified into following categories – based upon the type of object that is locked :-
The transaction that contains a DML statement acquires exclusive row locks on the rows modified by a statement within the transaction. Until this transaction commits or rollback, other transactions can’t update or delete rows.
A query in a transaction can see only committed changes made by earlier statements in the same transaction, but won’t be able to see data committed by other transactions after it started.
In addition to the exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the rows. If it’s already holding a more restrictive table-level DML lock, it retains the more restrictive lock.
Oracle uses row level locking for updates, inserts and deletes and it also automatically imposes a row exclusive table lock during these operations.
DML Locks are placed by oracle to protect data in tables and indexes. Row-level DML Locks guarantee that readers of data do not wait for Writers of Data & vice versa.
DDL Locks are placed on database objects during any DML Operation such as alter ot drop table/index.
Note :- A DML locks automatically places a DDL lock on the database object. So a DML transaction will simultaneously hold row-level DML lock as well as object- level DDL Lock. But for purely DDL operations no accompanying DML locks are held.
Latches are internal mechanisms that protect shared data structures in the SGA. Latches control the processes ‘ access to the memory structures.
Internal Locks are used by Oracle to protect access to structures such as datafiles, tablespaces & rollback segments.
Distributed Locks are specialized locking mechanism that is used in a distributed database system or in the Oracle Real application clusters environment.
Row Locks (TX) :-Row-level locks are primarily used to prevent two transactions from modifying the same row.
Table Locks (TM) :- Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation.
Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).
Monitoring and Detecting Lock Contention :-
Locking Mechanism :-
– Oracle automatically manages locks
– high data concurrency: users can access data at same time
– row level locks for dml, no locks for queries
– modify default locking with ‘row_locking’, default ‘always’ is lock row not table for dml
– ‘intent’ => locks at table level (except select for update that still locks at row level)
– data consistency: multi-version consistency, user sees static version of data even if its being changed
– Locks help until commit or rollback & abnormal termination where PMON cleans up locks
Lock Modes :-
– Exclusive (X), stops resource being shared with other transactions until X lock released
– set at row level for DML
– Share (S) several transactions can acquire share locks on same resouce
– set at table level for DML
– DML locks: table lock ‘TM’ is set for any DML transaction that modifies a table
– DML transactions get at least 2 locks: shared table lock & exclusive row lock
– insert, update, delete, select for update, lock table
– table lock prevents DDL operations that would conflict with the transaction
– row level lock ‘TX’ automatically acquired by insert, update, delete, select for update
– stops other users modifying same row at same time, row level locks can only by X
– each row gets a lock byte turned on in row header pointing to the interested transaction list (ITL) slot
used by the transaction
Enqueue Mechanism :-
– All locks maintained as enqueue, to keep track of users waiting for locks, requested lock modes, order that lock requested.
– Increase overall number of locks available to instance: dml_locks, enqueue_resources
Table lock modes: automatically assigned for DML :-
– Row Exclusive (RX), insert, update, delete, allows other transactions to dml other rows in table
– allows query, stops other transactions manually X table lock for X read or write access
– Row Share (RS), select …. for updade, allows other transactions to dml other rows in table
– allows query, stops other transactions manually X table lock for X write access
– manually lock table: lock table xx.yyy in mode;
– Share (S) : no DML allowed, implicitly used for RI: select … for update
– Share Row Exclusive (SRX): no DML or (S) mode allowed, implicitly for RI,
– No index required on foreign key column in child table
– Exclusive (x) : no DML or DDL allowed, no manual locks, queries are allowed
DML locks in block :-
– BLOCK = Block Header (TX slot 1,2..), row X (contains lock byte)
– locking information is not cleared on a commit, but when next query reads block: delayed block cleanout
– query doing cleanout, checks status of transaction and SCN in RBS transaction table
– Oracle keeps identifier for each active transaction in datablock header
– at row level, lock bytes stores identifier for slot containing transaction
– DDL locks: protects schema object while its acted upon or referred to by ongoing DDL operations
– DDL locks used to stop other DDL operations that might modify or reference same schema object
– shouldn’t see contention for DDL locks as only held briefly and requested NOWAIT mode
There are 3 types of DDL lock :-
– Exclusive:- required for drop & alter table, lock released when DDL completes
users can’t get exclusive lock on table, if other users hold a lock of any level
– alter table fails if users have uncommited transactions on that table
– Sharable :- create procedure, audit, grant, create package
– lock released when DDL parse completes, doesn’t stop similar DDL or any DML
but stops other users altering or dropping referenced object
– Break Parse :- used for invalidating statements in shared SQL area
– sql or pl/sql in lib cache holds one of these locks for every object it references until
statement ages out of shared pool
– never causes waits or contention, just means objects may need reparsing if object change
which may cause contention for shared pool
Lock Contention :-
– locks are inexpensive & efficient, may be issues if
– developers have codded unnecessary high locking levels, long transactions
– users aren’t commiting when they should
– App uses Oracle with other apps that impose higher locking levels
Diagnosing Locking activity :-
– dba_locks, dba_waiters, dba_blockers: shows who holding or waiting for locks (run catblock.sql)
– utllockt.sql: shows lock wait hierachy, sessions waiting and holding locks
– v$lock, type (tx,tm), id1 (RBS & slot#, object ID)
– find table name that corresponds to resouce ID1
select object_name from dba_objects, v$lock where object_id=id1 and type=’TM’
– XIDUSN (RBS#), OBJECT_ID, SESSION_ID, ORACLE_USERNAME, LOCKED_MODE
– if XIDUSN is 0, then session with corresponding session ID is requesting and waiting for lock
being held by session for which XIDUSN is different from 0
Resolving Locks :-
– contact user holding lock to rollback/commit OR KILL user
– check which row is causing contention from v$session
– row_wait_block#, row_wait_row#, row_wait_file#, row_wait_obj#
– 2 or more user wait for data locked by each other
– oracle detects and resolves deadlocks automatically by rolling back statement that detected deadlock
– statement that caused ddeadlock is deadlock is rolledback NOT transaction, so we should next
rollback remainder of transaction
– deadlocks usually occur when transactions explictly override default locking
– distributed deadlocks are handled in same way as normal deadlocks
– check trace file from deadlock, contains rowids of locking rows
– distributed transactions, local deadlocks are detected by analysing ‘waits for’ graph and
global deadlocks are detected by a time-out
Famous Oracle Error :-
ORA-00054: resource busy and acquire with NOWAIT specified
When you get an ORA-00054, it’s usually impossible to do the DDL unless You spend hours inconveniencing end-users by nuking their sessions.
ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary.
You have choices for avoiding the ORA-00054 error:
Re-run the change late at night when the database is idle.
Do all DDL during a maintenance window with all end-users locked-out.
Kill the sessions that are preventing the exclusive lock.
Oracle has several views for showing lock status, some of which show the username:
DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS – Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock.
DBA_LOCKS – Shows all locks or latches held or being requested
DBA_WAITERS – Shows all sessions waiting on, but not holding waited for locks
The DBA_LOCK_INTERNAL view used to show locks for a specific user, and you can specify the query in the form:
SELECT NVL(b.username,’SYS’) username, session_id,lock_type,mode_held, mode_requested,lock_id1,lock_id2
FROM sys.dba_lock_internal a, sys.v_$session b where . . .
You can also query v$access and v$locked_object to see specific locks:
select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select SESSION_ID from v$locked_object);
You can simply kill the session if you’re sure:
SQL> ALTER SYSTEM KILL SESSION (‘sid,serial#’);