Thread: Lock timeout detection in postgres 7.3.1

Lock timeout detection in postgres 7.3.1

From
Muhammad Shariq Muzaffar
Date:
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


Re: Lock timeout detection in postgres 7.3.1

From
Christoph Haller
Date:
>
> 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





Re: Lock timeout detection in postgres 7.3.1

From
Ludwig Lim
Date:
--- 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


Re: Lock timeout detection in postgres 7.3.1

From
Christoph Haller
Date:
> >
> > 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




Re: Lock timeout detection in postgres 7.3.1

From
Tomasz Myrta
Date:
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




Re: Lock timeout detection in postgres 7.3.1

From
Christoph Haller
Date:
>
> 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




Re: Lock timeout detection in postgres 7.3.1

From
Bruce Momjian
Date:
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,
Pennsylvania19073