Oracle lock timeout

Volker Dunz

unread,

Oct 5, 2000, 3:00:00 AM10/5/00

to

Hi,
our CTD 1.5 app is running against an oracle server 7.3.4. Now we are
experiencing ever more problems with locks which are resulting in 'hanging'
applications. In fact the app is not hanging, it is waiting for response
from the server. The wait time can take several minutes to an hour, more and
more clients are blocking each other. I think cause of this is the
lock-wait-timeout period of oracle, which is set to infinite by default.
Who knows, where this parameter can be edited?
Thanks in advance, Volker Dunz.

Petra Zamburek

unread,

Oct 6, 2000, 3:00:00 AM10/6/00

to

Volker,

do you mean real locks for e.g. a table? Which commands (select, insert,
update, connect, ...) hang?

Petra

"Volker Dunz" <vd...@odysys.de> schrieb im Newsbeitrag
news:mmzoIUqLAHA.1412@wdsn3...

Kalidas Palanisamy

unread,

Oct 10, 2000, 3:00:00 AM10/10/00

to

Hi Volker Dunz,
I think you can specify timeout in sql.ini as given below::

[dbwservr]
servername=server1
locktimeout=2

----------------------------------

Isa

unread,

Oct 19, 2000, 3:00:00 AM10/19/00

to

Hello,

do you mean real locks for e.g. a table? Which commands (select, insert,

update, connect, ...) hang ???

IF YES / this question

Try SqlSetLockTimeout( hSql, 0 ) NO WAIT
SqlSetLockTimeout( hSql, -1) INFINITE

I'am not sure, but i think that is not possible to put seconds Lock Timeout.
Only NO WAIT or INFINITE

The error message is 00054 Resource Busy with NO WAIT

Isabelle LAMIRAND

Volker Dunz a écrit dans le message ...

Thierry Cazenave

unread,

Oct 19, 2000, 3:00:00 AM10/19/00

to

Hello,

Even if some particular usefull setting are available to reduce time during users are locked, I would have a look at the source code of the application.

Here are the top issue to do :

- Work when ever possible with RL isolation level

- Reduce the number of cursor used ( simplifies transaction processing )

- Each user interaction & unitary transaction has to finish with a commit

- Verify that error management rollback transaction before diplaying and error message

A very simple test to do is work in the application and whenever the user has control on the user interface ( no work in process ) there should be no lock on the database. If some are still persistant, you should try to understand why and decide if it is really necessary.

An example of what could not be necessary is to LOCK FOR UPDATE a row before the user can update a form window, because this particular row is locked during an undetermined duration between the data selection and the user interaction asking to update the database. If during this time any other transaction try to select or even update this particular row, he will be blocked, then because this transaction might have also locked other rows, some other transaction might be locked, ... etc .... then certainly a majority of working user might wait for an event that is not under control of the system.

These comments are very general and apply to a majority of database systems.

Thierry Cazenave

Centura

"Volker Dunz" <vd...@odysys.de> a écrit dans le message news: mmzoIUqLAHA.1412@wdsn3...