Bug 3646162 False deadlock (ORA-60) in a RAC environment / TM lock mode change
This note gives a brief overview of bug 3646162.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 10.2
Versions confirmed as being affected
* (None Specified)
Platforms affected Generic (all / most platforms affected)
The fix for this bug introduces a notable change in behaviour thus:
Notable change of behaviour introduced in 9.2.0.6
Notable change of behaviour introduced in 10.1.0.4
Fixed:
This issue is fixed in
* 9.2.0.6 (Server Patch Set)
* 10.1.0.4 (Server Patch Set)
* 10.2.0.1 (Base Release)
Symptoms:
Related To:
* Deadlock
* RAC (Real Application Clusters) / OPS
Description
A false deadlock (ORA-60) may be reported in a RAC environment
if an UPDATE statement occurs after a corresponding SELECT FOR UPDATE.
Note:
This fix introduces a notable change in behaviour which affects
both RAC and non RAC environments.
Prior to this fix "SELECT FOR UPDATE" operations initially take
the relevant TM lock in "Row-Share" mode (SS or mode=2 in V$LOCK).
With this fix "SELECT FOR UPDATE" operations take the relevant
TM lock in "Row Exclusive" mode (SX or mode=3 in V$LOCK).
This has an impact on the behaviour of SQL when there are
unindexed foreign key constraints. In particular UPDATEs or
DELETEs to a PARENT table row will now be BLOCKED by any
active "SELECT FOR UPDATE" on the child table, even if the
child row is for a different parent key to that being deleted
or updated.
eg: Assume DEPT.DEPTNO has a PRIMARY KEY.
Assume there is a dummy row in DEPT with DEPTNO=99 with no children.
Assume EMP.DEPTNO has a FOREIGN KEY referencing DEPT.DEPTNO
Session 1>> SELECT * FROM EMP WHERE EMPNO=7369 FOR UPDATE ;
# Now places an SX lock on the TM lock for EMP
Session 2>> DELETE FROM EMP WHERE DEPTNO=99;
# This will now wait for session 1 to commit / rollback
when previously it did not.
The solution to such problems is to create an index on the
foreign key columns (eg: EMP.DEPTNO here)
See bug 4969880 for details of disabling this fix.
The full bug text (if published) can be seen at Bug 3646162 (This link will not work for UNPUBLISHED bugs)
You can search for any interim patches for this bug here Patch 3646162 (This link will Error if no interim patches exist)
Partager