Oracle Locking Survival Guide
Modes of Locking
Description of each Lock Mode
Row Share Table Locks (RS)
When to Lock with ROW SHARE Mode:
Row Exclusive Table Locks (RX)
When to Lock with ROW EXCLUSIVE Mode:
Share Table Locks (S)
When to Lock with SHARE Mode
Exclusive Table Locks (X)
Be careful to use an EXCLUSIVE lock!
update emp set
update emp set
In the example, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.
Automatic Deadlock Detection
Oracle performs automatic deadlock detection for enqueue locking deadlocks. Deadlock detection is initiated whenever an enqueue wait times out, if the resource type required is regarded as deadlock sensitive, and if the lock state for the resource has not changed. If any session that is holding a lock on the required resource in an incompatible mode is waiting directly or indirectly for a resource that is held by the current session in an incompatible mode, then a deadlock exists.
If a deadlock is detected, the session that was unlucky enough to find it aborts its lock request and rolls back its current statement in order to break the deadlock. Note that this is a rollback of the current statement only, not necessarily the entire transaction. Oracle places an implicit savepoint at the beginning of each statement, called the default savepoint, and it is to this savepoint that the transaction is rolled back in the first case. This is enough to resolve the technical deadlock. However, the interacting sessions may well remain blocked.
ORA-60 error in ALERT.LOG
An ORA-60 error is returned to the session that found the deadlock, and if this exception is not handled, then depending on the rules of the application development tool, the entire transaction is normally rolled back, and a deadlock state dump written to the user dump destination directory. This, of course, resolves the deadlock entirely. The enqueue deadlocks statistic in V$SYSSTAT records the number of times that an enqueue deadlock has been detected.
select name, value
where name = 'enqueue deadlocks';
enqueue deadlocks 1
How to avoid Deadlocks
Application developers can eliminate all risk of enqueue deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order. However, in complex applications, this is easier said than done, particularly if an ad hoc query tool is used. To be safe, you should adopt a strict locking order, but you must also handle the ORA-60 exception appropriately. In some cases it may be sufficient to pause for three seconds, and then retry the statement. However, in general, it is safest to roll back the transaction entirely, before pausing and retrying.
With the introduction of automated referential integrity (RI) came a whole new suite of locking problems. What seems at first to be a DBA's blessing can turn out to be an absolute nightmare when the DBA doesn't fully understand the implications of this feature. Why is this so?
RI constraints are validated by the database via a simple SELECT from the dependent (parent) table in question-very simple, very straightforward. If a row is deleted or a primary key is modified within the parent table, all associated child tables need to be scanned to make sure no orphaned records will result. If a row is inserted or the foreign key is modified, the parent table is scanned to ensure that the new foreign key value(s) is valid. If a DELETE CASCADE clause is included, all associated child table records are deleted. Problems begin to arise when we look at how the referential integrity is enforced.
Oracle assumes the existence of an index over every foreign key within a table. This assumption is valid for a primary key constraint or even a unique key constraint but a little presumptuous for every foreign key.
Index or no Index on Foreign Key's ?
If an index exists on the foreign key column of the child table, no DML locks, other than a lock over the rows being modified, are required.
If the index is not created, a share lock is taken out on the child table for the duration of the transaction.
The referential integrity validation could take several minutes or even hours to resolve. The share lock over the child table will allow other users to simultaneously read from the table, while restricting certain types of modification. The share lock over the table can actually block other normal, everyday modification of other rows in that table.
You can use the script: show_missing_fk_index.sql to check unindexed foreign keys:
SQL> start show_missing_fk_index.sql
Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
eg.: SCOTT, S% OR %
eg.: EMP, E% OR %
Owner <%>: SCOTT
Unindexed Foreign Keys owned by Owner: SCOTT
Table Name 1. Column Constraint Name
------------------------ ------------------------ ---------------
EMP DEPTNO FK_EMP_DEPT
What is so dangerous about a Cascading Delete ?
Oracle allows to enhance a referential integrity definition to included cascading deletion. If a row is deleted from a parent table, all of the associated children will be automatically purged. This behavior obviously will affect an application's locking strategy, again circumnavigating normal object locking, removing control from the programmer.
What is so dangerous about a cascading delete? A deleted child table might, in turn, have its own child tables. Even worse, the child tables could have table-level triggers that begin to fire. What starts out as a simple, single-record delete from a harmless table could turn into an uncontrollable torrent of cascading deletes and stored database triggers.
DELETE CASCADE constraints can be found with the following script:
SQL> SELECT OWNER,
WHERE DELETE_RULE IS NOT NULL;
CONSTRAINT_NAME C TABLE_NAME DELETE_RU
------------------------------ - ----------------- ---------
FK_EMP_DEPT R EMP CASCADE
Oracle resolves true enqueue deadlocks so quickly that overall system activity is scarcely affected. However, blocking locks can bring application processing to a standstill. For example, if a long-running transaction takes a shared mode lock on a key application table, then all updates to that table must wait.
There are numerous ways of attempting to diagnose blocking lock situations, normally with the intention of killing the offending session.
Blocking locks are almost always TX (transaction) locks or TM (table) locks . When a session waits on a TX lock, it is waiting for that transaction to either commit or roll back. The reason for waiting is that the transaction has modified a data block, and the waiting session needs to modify the same part of that block. In such cases, the row wait columns of V$SESSION can be useful in identifying the database object, file, and block numbers concerned, and even the row number in the case of row locks. V$LOCKED_OBJECT can then be used to obtain session information for the sessions holding DML locks on the crucial database object. This is based on the fact that sessions with blocking TX enqueue locks always hold a DML lock as well, unless DML locks have been disabled.
It may not be adequate, however, to identify a single blocking session, because it may, in turn, be blocked by another session. To address this requirement, Oracle's UTLLOCKT.SQL script gives a tree-structured report showing the relationship between blocking and waiting sessions. Some DBAs are loath to use this script because it creates a temporary table, which will block if another space management transaction is caught behind the blocking lock. Although this is extremely unlikely, the same information can be obtained from the DBA_WAITERS view if necessary. The DBA_WAITERS view is created by Oracle's catblock.sql script.
Some application developers attempt to evade blocking locks by preceding all updates with a SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE SKIP LOCKED statement. However, if they allow user interaction between taking a sub-exclusive lock in this way and releasing it, then a more subtle blocking lock situation can still occur. If a user goes out to lunch while holding a sub-exclusive lock on a table, then any shared lock request on the whole table will block at the head of the request queue, and all other lock requests will queue behind it.
Diagnosing such situations and working out which session to kill is not easy, because the diagnosis depends on the order of the waiters. Most blocking lock detection utilities do not show the request order, and do not consider that a waiter can block other sessions even when it is not actually holding any locks.
Lock Detection Scripts
The following scripts can be used to track and identify blocking locks. The scripts shows the following lock situation.
Session 1 Session 2 select empno
from emp for update of empno;
update emp set ename = 'Müller'
where empno = 7369;
This script shows actual DML-Locks (incl. Table-Name), WAIT = YES means
that users are waiting for a lock.
WAI OSUSER PROCESS LOCKER T_OWNER OBJECT_NAME PROGRAM
--- ------- -------- ------- -------- ------------- --------------
NO zahn 8935 SCOTT - Record(s) sqlplus@akira
YES zahn 8944 SCOTT - Record(s) sqlplus@akira
NO zahn 8935 SCOTT SCOTT EMP sqlplus@akira
NO zahn 8944 SCOTT SCOTT EMP sqlplus@akira
This script show users waiting for a lock, the locker and the SQL-Command they are waiting for a lock, the osuser, schema and PIDs are shown as well.
OS_LOCKER LOCKER_SCHEMA LOCKER_PID OS_WAITER WAITER_SCHEMA WAITER_PID
---------- -------------- ---------- ----------- --------------- ----------
zahn SCOTT 8935 zahn SCOTT 8944
TX: update emp set ename = 'Müller' where empno = 7369
This is the original Oracle script to print out the lock wait-for graph in a tree structured fashion. This script prints the sessions in the system that are waiting for locks, and the locks that they are waiting for. The printout is tree structured. If a sessionid is printed immediately below and to the right of another session, then it is waiting for that session. The session ids printed at the left hand side of the page are the ones that everyone is waiting for (Session 96 is waiting for session 88 to complete):WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2 ----------------- ------------ -------------- ---------- --------- -------- 88 None 96 Transaction Exclusive Exclusive 262144 3206
The lock information to the right of the session id describes the lock that the session is waiting for (not the lock it is holding). Note that this is a script and not a set of view definitions because connect-by is used in the implementation and therefore a temporary table is created and dropped since you cannot do a join in a connect-by.
This script has two small disadvantages. One, a table is created when this script is run. To create a table a number of locks must be acquired. This might cause the session running the script to get caught in the lock problem it is trying to diagnose. Two, if a session waits on a lock held by more than one session (share lock) then the wait-for graph is no longer a tree and the conenct-by will show the session (and any sessions waiting on it) several times.
For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks, because not all of the lock information is available locally. To prevent distributed transaction deadlocks, Oracle times out any call in a distributed transaction if it has not received any response within the number of seconds specified by the _DISTRIBUTED_LOCK_TIMEOUT parameter. This timeout defaults to 60 seconds. If a distributed transaction times out, an ORA-2049 error is returned to the controlling session. Robust applications should handle this exception in the same way as local enqueue deadlocks.
where name = 'distributed_lock_timeout';
There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.
Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.
If the block does not have enough internal free space (24 bytes) to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably waiting for a free ITL entry.
The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a single block, even though those updates may not increase the total row length. The degree of concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount of internal free space. Do not, however, let this warning scare you into using unnecessarily large INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade table scan performance, and non-default INITRANS settings are seldom warranted.
One case in which a non-default INITRANS setting is warranted is for segments subject to parallel DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions may have concurrent interest in any one block.
Check ITL Waits
The following SQL-Statement shows the number of ITL-Waits per table (Interested Transaction List). INITRANS and/or PCTFREE for those tables is to small (could also be that MAXTRANS is too small). Note that STATISTICS_LEVEL must be set to TYPICAL or ALL, MAXTRANS has been desupported in Oracle 10g and now is always 255 (maximum).
where name = 'statistics_level';
TTITLE "ITL-Waits per table (INITRANS to small)"
set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
object_name||' '||subobject_name object_name,
where statistic_name = 'ITL waits'
and value > 0
order by 3,1,2;
col owner clear
col object_name clear
col value clear
Exclusive Locks lock a resource exclusively, Share Locks can be acquired by more than one Session as long as the other Session holding a Share Lock have no open Transaction. A Share Lock can be "switched" from one Session to another.
Application developers can eliminate the risk of deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order.
A DELETE CASCADE can start out as a simple, single-record delete from a harmless table could turn into an uncontrollable torrent of cascading deletes and stored database triggers.
Blocking locks are almost always TX (transaction) locks or TM (table) locks. Oracle always performs locking automatically to ensure data concurrency, data integrity, and statement-level read consistency. Usually, the default locking mechanisms should not be overriden.
For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks.
The most common cause of ITL entry shortages is a zero PCTFREE setting.