Understanding Oracle Locking
Locks:
Are used to protect data (blocks, segments, rows …)
Several users can share locks
Lock requests are queued up using mechanism called as
enqueue. Enqueues are lists/queues stored in Shared
Pool
A transaction ID of a transaction that locks a table or
a row is stored in the header of that table or row.
Latches:
Used to protect memory structure (SGA)
Latches cannot be shared by processes
Latch request are not queued up. (immediate or willing
to wait (spins))
Locks provide data concurrency and consistency
Data concurrency means that many users can access data
at the same time.
Data consistency means that each user sees a consistent
view of the data, including visible changes made by the
user's own transactions and transactions of other
users.
How does Oracle
provide data
consistency (UNDO
segments)
Isolation levels
Isolation levels are
how Oracle executed
SQL statements in
regards to read
consistency.
Isolation levels are
directly related to
the type of locks that are acquired or ignored.
Read committed - read records that are committed by
other users, uncommitted records by other user are
ignored – this is the default isolation level for
Oracle)
Serilizable – database is frozen (do not read
transactions committed by other users after I have
started my serializable transaction. User behaves as if
he/she is the only user in the database. In PL/SQL
“select .. for update” is a serializable transaction)
Any changes that I make to data in my serializable
transaction can be seen by me.
How to set the isolation level
Alter session set isolation_level=serializable;
Once a lock is obtained, it is released only when user
commits or rolls back the transaction. This means a
lock is obtained for the duration of a transaction and
not for the duration of the query.
Types of Locks
DML Lock / Data Locks
DDL Lock / Dictionary Lock
Lock Modes
o Exclusive (X) (other users cannot modify the
resource. They can read)
o Share (S) (other users can obtain share locks)
Data lock Level
o Table (T)
RS – Row Share
RX – Row Exclusive
S – Share
SRX - Share Row Exclusive
X - Exclusive
o Row (R)
Locked or not locked – always exclusive
Lock Types
Explicit
Implicit
DML Locks (Table locks)
Insert, Row Other users can still perform Implicit
update, Exclusive DML on any other row in the
delete (RX) table.
Select … Row Share Other users can still perform Implicit
for update (RS) DML on the rows in the table
that were not returned by the
SELECT
statement.
UPDATE and Share Row Users can still perform DML Implicit
DELETE Exclusive on any other row in either
on parent (SRX) – like the parent or child table as
tables an Exclusive long as an index
with lock which exists on the child table’s
Foreign allows RS Foreign Key column.* (Lock is
Key lock for not acquired in 9i Release 2
relationsh other users and above)
ips to
child
tables
LOCK TABLE Exclusive(X) Other users can only query Explicit
... IN the table until the locking
EXCLUSIVE transaction
MODE is either committed or rolled
back.
DDL Locks (Table Locks)
Create, Exclusive(X) Prevents other users from Implicit
drop, issuing DML or
alter SELECT statements against the
referenced
table until after the CREATE,
DROP, or ALTER
operation is complete
Create Shared(S) During the compilation of a Implicit
procedure, procedure/function, user
Audit acquires (S) locks on tables
that are referenced in the
code. It, therefore, prevents
other users from altering or
dropping this table until the
procedure is compiled. This
lock allows other user to
compile their
procsdures/functions
How to Monitor Locking/blocking activity
V$locks
SELECT s.username,
DECODE(l.type,’TM’,’TABLE LOCK’, ‘TX’,’ROW LOCK’,
NULL) “LOCK LEVEL”,
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL;
V$locked_object (also gives blocking info)
SELECT LPAD(‘ ‘,DECODE(l.xidusn,0,3,0))
||l.oracle_username “User Name”,
o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc;
V$DBA_WAITERS
DBA_BLOCKERS
SELECT s.username
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid;
Use /rdbms/admin/utllockt.sql instead of above queries
Enqueue Statistics
Enqueues are shared memory structures that serialize access
to database resources(tables, indexes etc.).
select *
from v$sgastat
where name like ‘%enqueue%’
Enqueue wait
Enqueue requests
Enqueue conversions (e.g. upgrading locks)
Enqueue timeouts
Enqueue deadlocks
Initialization Parameters
DML_LOCKS = Maximum number of DML locks for the
entire instance (A DML lock is a lock obtained on
a table that is undergoing a DML operation
(insert, update, delete).) = 4 *
TRANSACRIONS(derived from TRANSACTIONS which is
derived from SESSIONS)
ENQUEUE_RESOURCES sets the number of resources
that can be concurrently locked by the lock
manager. It is derived from SESSIONS, which is
derived from PROCESSES
TRANSACTIONS = 1.1 * SESSIONS (to allow recursive
transactions)
SESSIONS = 1.1 * PROCESSES + 5
PROCESSES=100 We should set parameter based on
concurrent number of users.
Set these parameters when you get an error message that
enqueues are exhausted
How to tune locking
Change the application code so less-restrictive locking
is used
Do not use lock tables in exclusive more in your code
Avoid coding long transactions
Contact the blocking user and ask them to commit or
roll back their changes – else kill their sessions.
When the session is killed, the transaction of that
session is rolled back.
Remember – deadlocks are automatically resolved by
Oracle by rolling back the one of the transactions.
How to kill a blocking session.
SELECT s.username, s.sid, s.serial#
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid;
Alter system kill session ‘SID,Serial#’
ORA-00028 “Your session has been killed”
