>
> I have recently migrated my database from MS Sql
> Server to postgresql 7.3.1. In MS SQL SERVER, it is
> very easy to set the lock time equals to zero on ROW
> LEVEL. So that if some other user try to access the
> same data, he/she will get the error immediately. I
> have tried to run the same code through VB 6.0
> (windows) using pgsql as database on RED HAT LINUX
> 8.0, the only problem i am facing is when ever a user
> try to access a pre LOCKED ROW, the program goes into
> halt until the first user executes ROLLBACK or COMMIT.
>
> Is there any way to set the LOCK TIME equals to ZERO
> in postgresql 7.3.1?
>
I'm working on
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
and found a similar behaviour.
T1 (within psql):
BEGIN; DELETE FROM <some_table> ;
DELETE n
T2 (within psql):
BEGIN; DELETE FROM <some_table> ;
<waiting forever>
The documentation says (within Server Runtime Environment)
DEADLOCK_TIMEOUT (integer)
This is the amount of time, in milliseconds, to wait on a lock
before checking to see if there is a deadlock condition or not. The check for deadlock is relatively slow, so we
don'twant to run it
every time we wait for a lock. We (optimistically?) assume that deadlocks are not common in production applications,
andjust wait
on the lock for awhile before starting to ask questions about whether it can ever get unlocked. Increasing this
value
reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. The
defaultis
1000 (i.e., one second), which is probably about the smallest value you would want in practice. On a heavily loaded
serveryou
might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds
thatthe lock
will be released before the waiter decides to check for deadlock. This option can only be set at server start.
If I get this right, the T2 psql process should terminate within one
second, shouldn't it?
The postgresql.conf file is as it was right after the installation
#deadlock_timeout = 1000
So, I doubt this a bug, but still, there must be a misunderstanding or
something else
I don't know about. Could someone please enlighten us.
Regards, Christoph
