Postgres Pro
Downloads
Home   >   mailing lists

Thread: Statement Timeout and Locking

Statement Timeout and Locking

From
Markus Schaber
Date:
Hello,

In one of our maintainance apps that shuffles some data across
independent databases, we need to enshure data consistency, and decided
to do this by acquiring a lock in the target database. Now we do not
want the application to wait indefinitely for this lock, but fail and
complain loudly whenever locking fails.

When using the SQL statement
  SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE
  MODE; SET statement_timeout TO DEFAULT;
in psql, everything works fine. If someone else has the lock, it aborts with
  ERROR:  canceling query due to user request
after waiting 5 seconds.

However, the attached java file shows a different behaviour, it seems to
wait forever (at least it waited three minutes, and I'll keep it running
over lunch).

I use PostgreSQL 8.0 JDBC3 with SSL (build 311) and a debian PostgreSQL
7.4.7-5 server.

Does someone have an explanation for this, or even better, a fix or
workaround?

Markus


import java.sql.Connection;
import java.sql.DriverManager;

public class LockTest {

    public static void main(String[] args) throws Exception {
        System.out.println("using: "+org.postgresql.Driver.getVersion());
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/locktest", "test", "blubb");
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        conn.setReadOnly(false);
        conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE; SET
statement_timeoutTO DEFAULT;"); 
        System.out.println("Lock acquired");
        Thread.sleep(10000000);
    }
}

Re: Statement Timeout and Locking

From
Tom Lane
Date:
Markus Schaber <schabi@logix-tt.com> writes:
>         conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE; SET
statement_timeoutTO DEFAULT;"); 

At least in more recent JDBC drivers, I'd expect the above to fail
entirely because you can only put one SQL command per V3 Parse message.
Try splitting it into three statements.

            regards, tom lane

Re: Statement Timeout and Locking

From
Kris Jurka
Date:

On Thu, 28 Apr 2005, Tom Lane wrote:

> Markus Schaber <schabi@logix-tt.com> writes:
> >         conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE;
SETstatement_timeout TO DEFAULT;"); 
>
> At least in more recent JDBC drivers, I'd expect the above to fail
> entirely because you can only put one SQL command per V3 Parse message.
> Try splitting it into three statements.
>

The JDBC driver splits this query itself and issues it in three queries
internally.

Kris Jurka

Re: Statement Timeout and Locking

From
Kris Jurka
Date:

On Thu, 28 Apr 2005, Kris Jurka wrote:
> On Thu, 28 Apr 2005, Tom Lane wrote:
> > Markus Schaber <schabi@logix-tt.com> writes:
> > >         conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE;
SETstatement_timeout TO DEFAULT;"); 
> >
> > At least in more recent JDBC drivers, I'd expect the above to fail
> > entirely because you can only put one SQL command per V3 Parse message.
> > Try splitting it into three statements.
>
> The JDBC driver splits this query itself and issues it in three queries
> internally.
>

This actually is the problem.  It works as three separate statements, but
fails as one.   The server doesn't seem to recognize the SET when other
commands come in before Sync.

Driver splitting queries:

 <=BE ReadyForQuery(I)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1aaa14a,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=S_1,query="BEGIN",oids={})
 FE=> Bind(stmt=S_1,portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query="SET statement_timeout TO 5000",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query=" LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query=" SET statement_timeout TO DEFAULT",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync

This hangs while a version split by the caller works:

 <=BE ReadyForQuery(I)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@7a84e4,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=S_1,query="BEGIN",oids={})
 FE=> Bind(stmt=S_1,portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query="SET statement_timeout TO 5000",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync
 <=BE ParseComplete [S_1]
 <=BE BindComplete [null]
 <=BE CommandStatus(BEGIN)
 <=BE ParseComplete [null]
 <=BE BindComplete [null]
 <=BE NoData
 <=BE CommandStatus(SET)
 <=BE ReadyForQuery(T)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@9ed927,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=null,query="LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync
 <=BE ParseComplete [null]
 <=BE BindComplete [null]
 <=BE NoData
 <=BE ErrorMessage(ERROR: canceling query due to user request

Kris Jurka

Re: Statement Timeout and Locking

From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes:
> This actually is the problem.  It works as three separate statements, but
> fails as one.   The server doesn't seem to recognize the SET when other
> commands come in before Sync.

[ reads some code... ]  The problem is that postgres.c only inspects
StatementTimeout when start_xact_command starts a transaction command,
and the placement of finish_xact_command calls is such that that's
not going to happen until after Sync.  So the upshot is that the
"SET statement_timeout" isn't going to have effect until after Sync
(or after a transaction-control command, but there are none in your
example).

This suggests that the statement_timeout stuff is being done at the wrong
place.  I'm not sure exactly what the more-right places would be for
V3 protocol though.  What exactly would you expect statement_timeout to
cover in a Parse/Bind/Execute world --- especially if those aren't
issued in a purely sequential fashion?

A very simple definition would be that each Parse, Bind, or Execute
action is independently constrained by statement_timeout, but that would
act significantly differently from the simple-query case if planning
takes long enough to be a factor.  (Bear in mind that planning can
include constant-folding of user-defined functions, so at least in some
cases you can imagine people would want statement_timeout to constrain
planning.)  Also that would imply three times as many timer
enable/disable kernel calls, which might be an annoying amount of
overhead.

Anyway the short-term answer for Markus is "don't do it that way".
We ought to think about making the backend's behavior more consistent,
though.

            regards, tom lane

Re: Statement Timeout and Locking

From
Markus Schaber
Date:
Hi, Tom,

Tom Lane schrieb:

> Anyway the short-term answer for Markus is "don't do it that way".
> We ought to think about making the backend's behavior more consistent,
> though.

I'll split the query into three. Having it in one query just was a
convenience here.

Thanks,
Markus