lock timeout patch

Hello all,

I've created a lock timeout patch and it's attached.

When a transaction is blocked by another transaction because of
waiting a lock, we need a lock timeout in some cases.

Using this patch, the lock timeout is enabled with
'lock_timeout = xxxx' directive in postgresql.conf,
and if a timeout is occured, an error code (40P02)
will be returned and a client application can detect it using

JDBC: SQLException.getSQLState()
C: PQresultErrorField()

I know my code need to be cleaned up,
but any comments about this patch?

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
> When a transaction is blocked by another transaction because of
> waiting a lock, we need a lock timeout in some cases.

Isn't there an existing solution for this problem?

regards, tom lane

Tom,

I guess the transaction cancellation from the client
using PQrequestCancel() is available, but the cancellation
logic must be implemented in the client-application using
signal or thread.

I think detecting such situation on server-side is not
available now, and SQL Server or DB2 have same function.

Tom Lane wrote:
> Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
>
>>When a transaction is blocked by another transaction because of
>>waiting a lock, we need a lock timeout in some cases.
>
>
> Isn't there an existing solution for this problem?
>
> regards, tom lane
>

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
> I guess the transaction cancellation from the client
> using PQrequestCancel() is available, but the cancellation
> logic must be implemented in the client-application using
> signal or thread.

Actually I think the recommended solution involves using statement_timeout.

regards, tom lane

statement_timeout terminates large sort or scan
even if it is running, doesn't it?

statement_timeout doesn't care that
the process is waiting a lock or running.
I don't want to terminate a running query.

So a lock waiting backend shold be killed.

Tom Lane wrote:
> Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
>
>>I guess the transaction cancellation from the client
>>using PQrequestCancel() is available, but the cancellation
>>logic must be implemented in the client-application using
>>signal or thread.
>
>
> Actually I think the recommended solution involves using statement_timeout.
>
> regards, tom lane
>

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
> statement_timeout terminates large sort or scan
> even if it is running, doesn't it?

> statement_timeout doesn't care that
> the process is waiting a lock or running.
> I don't want to terminate a running query.

> So a lock waiting backend shold be killed.

This argument holds no water. On what will you base your estimate of
a good value for lock_timeout? It is nothing more than your estimate
of the statement runtime for some other backend that is currently
holding the lock you want ... an estimate which surely has less, not
more, reliability than the estimate you could make of the maximum
runtime of your own statement, because you have less information about
just what that other backend is doing. (And both you and the other
backend are in turn dependent on waiting for locks held by third
parties, etc etc.)

I'd accept a mechanism to enforce a timeout at the lock level if you
could show me a convincing use-case for lock timeouts instead of
statement timeouts, but I don't believe there is one. I think this
proposal is a solution in search of a problem.

regards, tom lane

Tom Lane wrote:
> I'd accept a mechanism to enforce a timeout at the lock level if you
> could show me a convincing use-case for lock timeouts instead of
> statement timeouts, but I don't believe there is one. I think this
> proposal is a solution in search of a problem.

I think statement_timeout and lock_timeout are different.

If I set statement_timeout to 1000 to detect a lock timeout,
I can't run a query which takes over 1 sec.

If a lock wait is occured, I want to detect it immediately,
but I still want to run a long-running query.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

On Mon, 28 Jun 2004, Satoshi Nagayasu wrote:

> If I set statement_timeout to 1000 to detect a lock timeout,
> I can't run a query which takes over 1 sec.
>
> If a lock wait is occured, I want to detect it immediately,
> but I still want to run a long-running query.

Why is it important what it is that makes your query not return as fast as
you expect? Maybe it's locking, maybe the computer is swapping, maybe it's
just lack of IO to that disk that holds the table, maybe it does a big
sort and have too little sort_mem to do it fast, ...

What makes locking special?

--
/Dennis Björklund

On Mon, 2004-06-28 at 02:16, Satoshi Nagayasu wrote:
> Tom Lane wrote:
> > I'd accept a mechanism to enforce a timeout at the lock level if you
> > could show me a convincing use-case for lock timeouts instead of
> > statement timeouts, but I don't believe there is one. I think this
> > proposal is a solution in search of a problem.
>
> I think statement_timeout and lock_timeout are different.
>
> If I set statement_timeout to 1000 to detect a lock timeout,
> I can't run a query which takes over 1 sec.
>
> If a lock wait is occured, I want to detect it immediately,
> but I still want to run a long-running query.
>

How is your problem not solved by NOWAIT?
http://developer.postgresql.org/docs/postgres/sql-lock.html

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
>>I think statement_timeout and lock_timeout are different.
>>
>>If I set statement_timeout to 1000 to detect a lock timeout,
>>I can't run a query which takes over 1 sec.
>>
>>If a lock wait is occured, I want to detect it immediately,
>>but I still want to run a long-running query.
>
> How is your problem not solved by NOWAIT?
> http://developer.postgresql.org/docs/postgres/sql-lock.html

I agree that it's one of the solutions when we use LOCK explicitly.
But LOCK does only lock a whole table, doesn't it?

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

Dennis Bjorklund wrote:
>>If I set statement_timeout to 1000 to detect a lock timeout,
>>I can't run a query which takes over 1 sec.
>>
>>If a lock wait is occured, I want to detect it immediately,
>>but I still want to run a long-running query.
>
>
> Why is it important what it is that makes your query not return as fast as
> you expect? Maybe it's locking, maybe the computer is swapping, maybe it's
> just lack of IO to that disk that holds the table, maybe it does a big
> sort and have too little sort_mem to do it fast, ...
>
> What makes locking special?

Processing slow-down is just a hardware/software sizing issue.
Of course we need to fix it when a problem is occured,
but I think it's a different kind of problem.

In large databases, such as DSS(decision support system),
some queries takes one or more minutes. I think it's okey.

But I don't want to wait one or more minutes just for a lock.
I need to return a message to the user "retry later." or
something like that. It depends on various applications.

So I think detecting a lock waiting is important.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

On Tue, Jun 29, 2004 at 09:25:27 +0900,
Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> wrote:
>
> But I don't want to wait one or more minutes just for a lock.
> I need to return a message to the user "retry later." or
> something like that. It depends on various applications.

Why not set statement timeout low when you are about to run a query
that you think should return quickly?