Oracle provides many areas of locking:
- 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
Oracle locks have been around a long time since the inception of
the first major database release with the Oracle database
environment. What is the purpose of a lock within the Oracle
database? Locks function as the primary mechanism to provide for
data concurrency and data consistency within the database.
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 locks Script - locked rows
for a user
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 is best 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 . .
.
Here is a script by
Laurent Baylac to show locks in Oracle
10g:
SET
LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') ||
NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY
HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
--
Search for locked objects
-- To
be executed under the SYSTEM account
--
Compatible with Oracle10.1.x and higher
select
distinct to_name
object_locked
from
v$object_dependency
where
to_address in
(
select
/*+ ordered */
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod
!= 1)
and ((h.kgllkreq = 0) or (h.kgllkreq
= 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod=
1))
and ((w.kgllkreq != 0) and (w.kgllkreq
!= 1))))
and
w.kgllktype = h.kgllktype
and
w.kgllkhdl = h.kgllkhdl
and
w.kgllkuse = w1.saddr
and
h.kgllkuse = h1.saddr
)
/
The majority of locking issues within Oracle are
the result of application design within database applications. One
root cause of such lock contention problems lies in a basic
misunderstanding of the Oracle locking model. Developers often
assume incorrectly that database locking is the same across
different platforms.
For instance, a new
Oracle developer who is used to writing
database applications in Microsoft SQL
Server may use the same design approach with
Oracle database applications that he/she
used with SQL Server. This causes most of
the locking issues. The solution is simple:
educate the development staff on how Oracle
database locking works.
If a lock related hang scenario is encountered,
the following SQL statements are useful to help isolate the waiters
and blockers involved with locking problems.
Show all sessions waiting for any lock:
select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';
Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;
Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
Of course, one can also view lock activity from
the Oracle Enterprise Manager (OEM) database or Grid control
application as mentioned earlier. Next to be covered are some issues
regarding lock contention as related to use of interested
transaction lists at the Oracle database block level.
For a complete list of over 600 Oracle
scripts, I would recommend the Oracle
script location at
www.dba-oracle.com/oracle_scripts.htm .
Related Oracle locks articles:
Oracle row locks and row level locking
Oracle Metric user
lock
Oracle Locking Strategies
Locks and
ORA-00054 error
Oracle Locking scripts
Oracle 10g release 2 locking changes
Oracle passwords - changing, expiring and
locking users
Minimizing Table Locks to Optimize
Performance
Oracle Row-Level Locks
SGA System Global Area Components and
Locking
|