select for update LOCK 3 10gR2

Bonjour,

Je viens de migrer une base oracle de la version 9i à la version 10gR2,

Lors d'un select for update sous oracle 9 j'avais un lock 2
mais depuis la version 10gr2 j'ai des lock 3

par consequent je me retrouve avec plein de deablock...
Je suis retourner en 9i en attendant de trouver une solution .... en esperant que vous pourriez m'aider

exemple :


SOUS ORACLE 9i
select * from "une table" where "une ligne" is null for update;
resultat: blablabla
select lmode from v$lock where type = 'TM';
resultat:LMODE is 2 (SS mode)


SOUS ORACLE 10gr2

select * from "une table" where "une ligne" is null for update;
resultat: blablabla
select lmode from v$lock where type = 'TM';
resultat : LMODE is 3 (SX mode)


Merci pour votre aide

FYI .

CDLT

Citation:

---

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)

---

...

Citation:

---

Description

The fix for bug 3646162 changed the behaviour of
SELECT FOR UPDATE operations such that with that fix
they correctly take a sub-exclusive mode TM lock on
the affected tables. This change in behaviour introduced
by that fix led to problems for some client code so this
fix introduces a backout method to disable the fix and
revert to the old (incorrect) TM lock level.

To enable this fix in >= 10.2.0.2 set "_fix_control"='4969880:ON'

To enable this fix in 9.2 / 10.1 / 10.2.0.1 set event 38084 to any level.


eg:
create table test( f1 varchar2(10));
select * from test where f1 is null for update;
select lmode from v$lock where type = 'TM';
^
LMODE is 3 (SX mode)

With this fix enabled:
alter session set "_fix_control"='4969880:ON'; -- 11g
alter session set events '38084 trace name context forever, level 1'; -- 9.2/10g
select * from test where f1 is null for update;
select lmode from v$lock where type = 'TM';
^
LMODE is 2 (SS mode)

The full bug text (if published) can be seen at Bug 4969880 (This link will not work for UNPUBLISHED bugs)
You can search for any interim patches for this bug here Patch 4969880 (This link will Error if no interim patches exist)

---