> Just trying to work out the event flow here...
>=20
> Session 1 deletes where x =3D3D 1 (this sets the lock on this row), then =
=3D
> the
> trigger fires and locks the remaining rows
>=20
> Session 2 tries to delete where x =3D3D 2, but Sess 1's trigger would hav=
e
> acquired a lock on this row, thus session 2 waits.=3D20
>=20
> To me the trigger for session 2 would not have fired yet since it's
> being blocked on the delete process. The trigger is an AFTER delete
> trigger but the delete never gets a chance to complete due to the lock
> acquired by the first trigger.
>=20
> Sess 2's select ... for update nowait statement never gets a chance to
> execute...By the way is there a way to check if a trigger has fired?
>=20
> Feel free to correct me if I'm way off. =3D20
I think, you are right.
Trace file from second (waiting) sessions displays that waiting (tx,6
enqueue) statement is 'delete' not 'select for update nowait'.
And if we change trigger type to 'before delete' then we get desired ora-00=
054.
--=20
Egor
http://www.oracledba.ru/orasrp/
Free Oracle Session Resource Profiler
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 09 2005 - 02:55:54 CDT
