pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLock timeout detection in postgres 7.3.1
7+ messages / 5 participants
[nested] [flat]
* Lock timeout detection in postgres 7.3.1
@ 2003-02-05 08:51 Muhammad Shariq Muzaffar <shariq77@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Muhammad Shariq Muzaffar @ 2003-02-05 08:51 UTC (permalink / raw)
To: pgsql-sql
Hi
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?
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-06 09:51 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 7+ messages in thread
From: Christoph Haller @ 2003-02-06 09:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: shariq77@yahoo.com
>
> 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't want to run it
every time we wait for a lock. We (optimistically?) assume that
deadlocks are not common in production applications, and just 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 default is
1000 (i.e., one second), which is probably about the smallest
value you would want in practice. On a heavily loaded server you
might want to raise it. Ideally the setting should exceed your
typical transaction time, so as to improve the odds that the 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-06 11:14 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 7+ messages in thread
From: Ludwig Lim @ 2003-02-06 11:14 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
--- Christoph Haller <ch@rodos.fzk.de> wrote:
>
> 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
> 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.
>
I don't think there is a deadlock in the example
given above. If I'm not mistaken a deadlock occurs if
both transactions are waiting for each other to
release the lock (i.e T1 waits for T2 to release
locks/resources while T2 is also waiting for T1 to
release locks/resources. In the above example, T1
doesn't wait for T2 to do something before finishes
the transaction (Only T2 is waiting for T1 to finish),
hence the condition for deadlock is not met.
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-06 12:12 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 7+ messages in thread
From: Christoph Haller @ 2003-02-06 12:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: shariq77@yahoo.com; lud_nowhere_man@yahoo.com
> >
> > T1 (within psql):
> > BEGIN; DELETE FROM <some_table> ;
> > DELETE n
> >
> > T2 (within psql):
> > BEGIN; DELETE FROM <some_table> ;
> > <waiting forever>
> >
...
>
> I don't think there is a deadlock in the example
> given above. If I'm not mistaken a deadlock occurs if
> both transactions are waiting for each other to
> release the lock (i.e T1 waits for T2 to release
> locks/resources while T2 is also waiting for T1 to
> release locks/resources. In the above example, T1
> doesn't wait for T2 to do something before finishes
> the transaction (Only T2 is waiting for T1 to finish),
> hence the condition for deadlock is not met.
>
Yupp, I agree.
But from former DBMS I was dealing with,
I know this SET TIMEOUT called feature, which if properly set
terminated processes like that hanging on T2.
Is there something comparable within Postgres?
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-07 10:29 Tomasz Myrta <jasiek@klaster.net>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tomasz Myrta @ 2003-02-07 10:29 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; shariq77@yahoo.com; lud_nowhere_man@yahoo.com
Christoph Haller wrote:
<cut>
> Yupp, I agree.
> But from former DBMS I was dealing with,
> I know this SET TIMEOUT called feature, which if properly set
> terminated processes like that hanging on T2.
> Is there something comparable within Postgres?
PostgreSQL 7.3 Documentation
3.4. Run-time Configuration
STATEMENT_TIMEOUT (integer)
Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer.
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
In this case I suppose 2 things:
- table has a lot of records and you should just wait to finish operation.
- another query locked the table and it is realy a deadlock
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-07 11:39 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 7+ messages in thread
From: Christoph Haller @ 2003-02-07 11:39 UTC (permalink / raw)
To: jasiek@klaster.net; +Cc: pgsql-sql
>
> PostgreSQL 7.3 Documentation
> 3.4. Run-time Configuration
> STATEMENT_TIMEOUT (integer)
> Aborts any statement that takes over the specified number of
milliseconds. A value of zero turns off the timer.
> 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
>
>
> In this case I suppose 2 things:
> - table has a lot of records and you should just wait to finish
operation.
> - another query locked the table and it is realy a deadlock
>
Thanks Tomasz for pointing this out.
STATEMENT_TIMEOUT is exactly what I was looking for.
I should have had a look into the 7.3 Doc on my own.
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Lock timeout detection in postgres 7.3.1
@ 2003-02-12 03:00 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2003-02-12 03:00 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; shariq77@yahoo.com; lud_nowhere_man@yahoo.com
Tomasz Myrta wrote:
> Christoph Haller wrote:
> <cut>
> > Yupp, I agree.
> > But from former DBMS I was dealing with,
> > I know this SET TIMEOUT called feature, which if properly set
> > terminated processes like that hanging on T2.
> > Is there something comparable within Postgres?
>
> PostgreSQL 7.3 Documentation
> 3.4. Run-time Configuration
> STATEMENT_TIMEOUT (integer)
> Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer.
> 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
>
>
> In this case I suppose 2 things:
> - table has a lot of records and you should just wait to finish operation.
> - another query locked the table and it is realy a deadlock
One of the uses of STATEMENT_TIMEOUT is to allow a LOCK or query to fail
if it doesn't complete in a short time. We don't have a special timer
to say if we are waiting on a lock for a specified time --- just a
query-level timer.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-02-12 03:00 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-05 08:51 Lock timeout detection in postgres 7.3.1 Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-02-06 09:51 Re: Lock timeout detection in postgres 7.3.1 Christoph Haller <ch@rodos.fzk.de>
2003-02-06 11:14 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-02-06 12:12 Re: Lock timeout detection in postgres 7.3.1 Christoph Haller <ch@rodos.fzk.de>
2003-02-07 10:29 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-12 03:00 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-02-07 11:39 Re: Lock timeout detection in postgres 7.3.1 Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox