Re: Row and Table Locking

i agree that for the most part, there is no need to explicitly include statements to acquire locks on tables and rows within a PL/SQL block.

for some applications, however, there may be an issue with lock contention, for example, session A acquires and holds a lock on a row, and session B issues an update on the same row. session B will wait... and wait... and wait...

the "SELECT ... FOR UPDATE NOWAIT" statement will attempt to acquire a lock, and if not immediately successful, it will raise an exception.

for a high-availabilty application, there can be a real benefit to explicitly trying to acquire the lock without waiting for it, and allowing control to pass back to the application when a lock is not acquired.

i would surmise that the PL/SQL packages were developed by a team that had first hand experience with application database sessions that were "hung" waiting to acquire a lock on a resource... with no way out other than to wait for the other session to release the lock... (it probably turned out that session A was a connection from a client pee-cee that was powered off and disconnected from the network without closing the oracle session... are we going to measure "hang time" be in minutes ? hours ? days ?

i continue to use "select ... for update nowait" in PL/SQL blocks... as far as i know, oracle 8.1 does not yet provide the "nowait" behavior for UPDATE or DELETE statements.

to conclude... the "select ... for update nowait" statements are likely there for a reason... they aren't costing you much... you have very little to gain by removing them... and you may learn more than you want to about lock contention if you do decide to remove them...

HTH "Nadeem Kafi" <kafis_at_super.net.pk> wrote in message news:f1698545.0108232016.51f79c0c_at_posting.google.com...
> Hello All,
>
> I want to confirm that there is no need to explicitely lock
> tables and rows during updates and deletes after the row
> level lock provided by Oracle after Version 6. i.e.
>
> Before each update/delete to a row in a table there is no need to
> lock the row. Oracle will do it automatically.
>
> One of my inherited application contains PL/SQL code which lock
> tables in share update mode (or using select...for update nowait)
> before every update and delete. Now using Oracle 8.1.6, I feel
> there is no need. BTW, there are no explicity application specific
> locking requirements.
>
> Best Regards,
> Nadeem Kafi.
Received on Fri Aug 24 2001 - 23:53:25 CDT