Обсуждение: Lock timeout detection in postgres 7.3.1
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
> > 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
--- 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
> > > > 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
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
> > 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
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