Thanks a lot for the reply, Locking is always a big concern when
trying to support same type of code on different RDBMS. I am preety
sure there must be more locking issues in the application besides this
and trying to isolate and fix them. This was bit tricky with the way
how the "rownum" and "top" are implemented and for developers it is
very difficult to see the difference unless they test the concurrency
of the code while developing. This particular issue is not that
serious since this block takes few ms to run and commit and there is
not much blocking overhead since rest of the processing is more time
consuming then getting the row to work from this table. Since Advance
Queuing is introduced long time back and we are also doing same type
of processing there must be a way in Oracle to implement FIFO between
different sessions (I have not yet looked into the AQ code so just
assuming that oracle must be be able to get the next message and
passed to waiting session so there must be some kind of lock single
row and get the rownum with order by clause implemented).
I will do research also but It will be great if you can send some
linke about how oracle process the rownum and why in this scenario it
is showing as blocking on Transaction lock and if blocking session is
locking all the rows howcome the waiting session was able to get the
DML lock.
Thanks
--Harvinder
Received on Wed Mar 07 2007 - 15:10:21 CST
