Oracle locking strategy: how does it work

From: Ronald van Woensel <rvw_at_incore.xs4all.nl>
Date: 1998/01/18
Message-ID: <34C23994.172F4EB9@incore.xs4all.nl>#1/1

I'm currently running into some deadlock problems, and although oracle seems to
be able to recover from deadlocks, this is not *always* the case. (I will discuss this specific problem later) The point is that deadlocks seem to occur on moments that I do not expect oracle to lock something. Example:

delete from t1 where t1.id in (select id from t2 where t2.something='value');

At the moment this query gave a deadlock on me, table t2 WAS EMPTY!
(and t1.id has a primary key on it)

So the question is:
- Is the information in the server log about these deadlocks reliable? - Is oracle locking in a different way than I was assuming it was? My assumption is that if a delete statement does not delete rows, it will not lock any.

Some documentation says that you can always get deadlocks from oracle, even for no reason. But the oracle dump just says 'due to incorrect application design' (which pisses me off) Is this true? or should I alter my code until all deadlocks are away?
(I'm using oracle 7.3.2.3)

About oracle's deadlock-recovery, I use the following piece of code, which is used
inside a background process that executes plsql statements on the background:

• Start processing LOOP fDeadLockRetry:=FALSE; BEGIN SAVEPOINT StartBgTransaction; curs:=DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curs,'BEGIN '||vcStatement||' END;', DBMS_SQL.V7); i:=DBMS_SQL.EXECUTE(curs); DBMS_SQL.CLOSE_CURSOR(curs); EXCEPTION WHEN deadlock_detected THEN DBMS_SQL.CLOSE_CURSOR(curs); ROLLBACK TO StartBgTransaction; DeadLockCount := DeadLockCount + 1; DBMS_LOCK.SLEEP(DeadLockCount); fDeadLockRetry:=TRUE; WHEN OTHERS THEN inc.logc('Bg['||vcHandlerID||'] Err: '||vcStatement,140); inc.logc('Err='||sqlcode||'('||sqlerrm(sqlcode)||')',140); DBMS_SQL.CLOSE_CURSOR(curs); END; EXIT WHEN NOT fDeadLockRetry; END LOOP;

When deadlocks occur, it will retry the statement, which works well for 95% of the deadlocks, but it happed once that the session ran into the following error for ALL plsql executed AFTER the deadlock:

ORA .. no statement parsed

(this happend about 50 times after the deadlock, and no statement was
executed without this error)
Closing the session and restarting it solved the problem.

any suggestions?

--

Received on Sun Jan 18 1998 - 00:00:00 CST