Re: Row Locking
Date: 1995/09/23
Message-ID: <44212d$241_at_inet-nntp-gw-1.us.oracle.com>#1/1
pberger_at_nic.wat.hookup.net (Paul Berger) writes:
>I have a question regarding deadlock detection within Oracle7,
>version 7.1.4, (in a Netware 3.x environment).
>
>We have an experienced application developer who is concerned
>about allowing his application to accommodate "update"
>deadlock conditions. As the resident DBA I am trying to help.
>
>We have done some testing within SQL*Plus to force a lock
>state on a single row within a table accessed by two concurrent
>users. The blocked user becomes "locked" indefinitely until
>the "blocker" either commits or roles back their transaction.
>Oracle on behalf of the blocked user does nothing.
As has been explained before, this is not deadlock, this is "livelock". This behavior (if transaction A locks a row, and transaction B attempts to modify the same row, then transaction B blocks until transaction A releases the row by either rolling back or commiting) is fully documented in the Server manuals.
>Is there
>some way, at the session or system level, that we can bias
>Oracle's behaviour in this regard? Can we establish a time-out
>so that the blocked user can proceed in an informed fashion,
>rather than waiting on the "blocker" to do something?
There is nothing that you can do to change the behavior of the database. What you *can* do, however, is change the behavior of the application. Here's a sample PL/SQL block that detects livelock:
declare
dummy char;
row_locked exception;
pragma exception_init(row_locked, -54);
begin
select 'x' into dummy from dept where deptno = 10
for update nowait;
update dept set dname = 'SUPPORT' where deptno = 10;
exception
when row_locked then
dbms_output.put_line('can''t lock row');
end;
/
This will "time-out" immediatly: any re-trying or delays are up to the application designer.
>Oracle doesn't seem to do any kind of deadlock detection in
>this regard, other than wait for the "blocker". On hot
>spots within a busy table this can conceivably become
>problamatic.
Do you even know that you have a hot spot? People with backgrounds in databases that don't do row-level locking tend to fear problems that don't exist when using Oracle databases.
If you've done measuring and testing to determine that you really *do* have a hot spot, then I'd suggest that you need to do some rethinking of your application. Sequences can be good for this sometimes. Sometimes you need to re-consider the Business Process Model underlying your application. Sometimes you need to take a look at your table normalization.
In general, if you've got a lot of people trying to access the same row at the same time, you've got a conceptual problem, not a implementation problem.
Paul Zola Senior Technical Analyst World-Wide Technical Support Tools Escalations ==============================================================================Computers possess the truly profound stupidity of the inanimate. - B. Sterling Received on Sat Sep 23 1995 - 00:00:00 CEST