Re: Commit release lock in cursor?

In article <8j0d5n$ffq$1_at_nnrp1.deja.com>,   alan_psb_at_my-deja.com wrote:
>
>
> The algorithm:
>
> define cursor with select for update on table testing
> open cursor
> do fetch
> commit
> close cursor
>
> I want to ask, is the commit will release the lock of the table
> testing? And is the close cursor release the lock on table testing?
>
> Thanks,
> Alan
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

A 'select for update' does not lock the table, just the rows specified by the where condition. Every row returned is locked; the locks, all of them, are released when a 'commit' is executed. Therefore a 'commit' cannot be included in a loop where 'select for update' rows are being fetched. The next attempted fetch after a 'commit' will fail with an Oracle error -- "ORA-01002: fetch out of sequence". Of course, if only one row is fetched from the 'select for update' cursor once the data has been modified a 'commit' can be issued and the row lock will be released. This doesn't mean that you cannot modify each row in turn, just that you cannot issue a 'commit' until the record list is exhausted and the loop exits.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.