Re: Locking question when using Select clause with For Update and Skip locked

On Mar 7, 4:10 pm, harvinde..._at_gmail.com wrote:
> 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

Tom Kyte has this whole subject covered in detail in several of his books.

Before you go any further I recommend that you purchase and read his latest one "Expert Oracle Database Architecture". Read chapters 1 thru 9. Rinse and repeat.

It's better to get the whole complete picture and understand it in detail than trying to pick and fix 1 piece of SQL at a time. Received on Wed Mar 07 2007 - 16:10:01 CST