- Locks and Oracle
- Lock management and escalation with Oracle
- Lock management in an Oracle RAC environment
- Enhancements to locks with Oracle
- Tips for resolving lock issues with Oracle
- Avoiding deadlock conditions with Oracle
It allows for multiple users to access the data simultaneously while providing a consistent view of data including any changes made by each user's transaction and that of other user transactions made to and against the data within Oracle. Furthermore, locks prevent errors in read and write consistency as part of the relational database ACID model. The database ACID model refers to Atomic, Consistency, Isolation, and Durability. To further explain what ACID means in terms of Oracle and other relational database models, the following explanation illustrates.
Atomicity:
For each transaction within the Oracle database, all of the units of work for a transaction must either be all or nothing. In other words, the transaction must be completed or else it must be undone or rolled back. Undo and rollback provide these functions with transactions in concert with locking and latching mechanisms.
Consistency:
Every transaction is required to preserve the integrity constraints which function as part of the declared consistency rules within the Oracle database. Database constraints are the business rules that provide for consistency.
Isolation:
This means that multiple transactions cannot interfere with one another at the same time. Results that are performed in flight, i.e. uncommitted transactions, are not visible to other transactions until a commit phase is executed and completed. Locks provide the mechanism for the isolation phase within the ACID model for Oracle database transactions.
For example, if Sally user locks table A with an exclusive lock, then user Bill will not be able to update the rows in that table until Sally has completed her transaction on that table. If locks did not exist within Oracle, there would be many problems with phantom reads and writes. This concurrency control ensures that all transactions within Oracle are executed safely and according to these rules so that no committed transactions are lost while in the event of a rollback undo operation to abort transactions.
Durability:
Durability is provided for by the Oracle database engine so that completed transactions are maintained and not lost in the future. Oracle protects against lost transactions by use of committed transactions stored within the undo/rollback segments and undo tablespaces within the Oracle database engine.
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
**********Query for checking object level locks*************
select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine from gv$locked_object a ,gv$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
#########################################################################
***********Query to Identify locks and Transaction ID's *******************
select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1, to_number('ffff', 'xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = username;
**************Query to Identify who is blocking whom ********************
select (select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
**************Query to Identify Locks*******************************
select * from dba_ddl_locks;
select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine from gv$locked_object a ,gv$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
#########################################################################
***********Query to Identify locks and Transaction ID's *******************
select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1, to_number('ffff', 'xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = username;
**************Query to Identify who is blocking whom ********************
select (select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
**************Query to Identify Locks*******************************
select * from dba_ddl_locks;
