From: Steve.Coladonato@aventis.com Message-ID: <15B45C8C4933D511819600508B6FEB7C9CB9A4@kopnt39.kop.centeon.com> Date: Fri, 26 Oct 2001 07:44:49 -0400 Subject: RE: [suse-oracle] Detecting oracle deadlocks in 8i and 9i
"Select" statements do not require the "for update nowait" clause if you are
only intending to view the results. If you are inserting, you do not need
"select for update nowait". However, if you are updating or deleting, then
another user may also be trying to modify the same row. So before issueing
the update or delete statement, the row should be locked by the the "select
for update nowait" clause and then the update or delete should occur. The
"select for update nowait" will return you an error indicating that the row
is locked by someone else.
Select statements can still access tables that have rows locked including
the locked rows. A view does not store any data, it is only a SQL
statement. SQL*Loader will either insert rows, no lock required, or delete
the table first and then insert. If it "replaces", then until the
SQL*Loader session issues it's first commit, which could be at the end of
the load, locks will be in place. The Oracle Metalink site has information
concerning deadlocks.
We run into problems here, mainly in development, when a user will be
running SQL*Plus and be modifying rows without committing at the same time
the application code is being tested. Without the "select for update
nowait" clause, the application just sits there waiting for the lock to be
released and the developer thinks his PC is hung.
BTW, we have found that it is best to put all insert, update, and delete
statements in a stored procedure/package and have the application frontend
isssue the appropriate call and pass the procedure/package the data it has
captured from the screen. Also, all business logic processing should be in
stored PL/SQL procedures and not in the application code.
Steve
-----Original Message-----
From: Matt [mailto:zagnimp@aom.bt.co.uk]
Sent: Friday, October 26, 2001 5:10 AM
To: martin@berg.dk; suse-oracle@suse.com
Subject: Re: [suse-oracle] Detecting oracle deadlocks in 8i and 9i
Hi Martin and All,
What do all the select statements have to have a select for update
*nowait* on them
if I am inserting data or deleting from one table to another ?
Or should it be only when creating views ?
Or when altering (dropping/creating) partitions ?
I think most of the problem is a select is happening for a creation of a
view
whilst a sqlloader session is in operation.
Are there any fantastic documentations on the area of deadlocking ?
Also is deadlocking handeled better in 8.1.7 and 9i then on 8.1.6 ?
Regards
Matt
Steve.Coladonato@aventis.com wrote:
>All applications should "select for update" prior to updating a record
or
>deleting a record. Should the record be locked by another transaction,
an
>appropriate error code can be returned to the calling application.
>
>Steve
>
Make that a "select for update *nowait*" otherwise you'll still end up
with a deadlock situation.
Martin Berg
---------------------------------------------------------------------
To unsubscribe, e-mail: suse-oracle-unsubscribe@suse.com
For additional commands, e-mail: suse-oracle-help@suse.com
---------------------------------------------------------------------
To unsubscribe, e-mail: suse-oracle-unsubscribe@suse.com
For additional commands, e-mail: suse-oracle-help@suse.com
This archive was generated by hypermail 2.1.0 : Fri Oct 26 2001 - 04:45:29 PDT