Thread: setQueryTimeOut(1) - not expected result...

Hallo,

assuming one has 2 users which try to lock the same record for edit at a time
the one coming later should be informed of the situation, i like to do the
following:

//user1:

stmt.setQueryTimeout(1);//wait just one second
ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
mytable");

//user2:

stmt.setQueryTimeout(1);
ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
mytable");
(if timeoutinform user...)

I would think, that the second user get's a query timeout on his query, but it
does not seem to work. Instead the think seems to wait forever.

Immediately after user1 does and "COMMIT;" the user2 get's his ResultSet.

What am i doing wrong?

Guido

Guido,

I had the same situation, I didn't use Transactions because I'd lock the
record when the first user start to edit and not when the user save the
record.

I did like this:

I create 2 tables on my postgres database :

the first table I use to register the user login, this file has a serial
field which give me a different sequence each login.

the second table I use to register that login sequence started to edit a
record.

always before start to edit a record I check the second file and if the
record is there I show a message saying there is another user editing the
record.

this solution has a few problems, like "turn off the computer without delete
the records from a second file", so I create a thread in Java which every 1
minute update the first file ("login file")

so, when I check the second table I check the first either and if the login
time was more than 2 minutes It mean the record isn't locked anymore because
the login that was locking this record didn't update his login (the user
turn off the computer).

wmiro.

----- Original Message -----
From: "Guido Fiala" <guido.fiala@dka-gmbh.de>
To: <pgsql-jdbc@postgresql.org>
Sent: Wednesday, January 28, 2004 11:52 AM
Subject: [JDBC] setQueryTimeOut(1) - not expected result...


Hallo,

assuming one has 2 users which try to lock the same record for edit at a
time
the one coming later should be informed of the situation, i like to do the
following:

//user1:

stmt.setQueryTimeout(1);//wait just one second
ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
mytable");

//user2:

stmt.setQueryTimeout(1);
ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
mytable");
(if timeoutinform user...)

I would think, that the second user get's a query timeout on his query, but
it
does not seem to work. Instead the think seems to wait forever.

Immediately after user1 does and "COMMIT;" the user2 get's his ResultSet.

What am i doing wrong?

Guido

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Guido Fiala wrote:

> //user1:
>
> stmt.setQueryTimeout(1);//wait just one second
> ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
> mytable");

[...]

> What am i doing wrong?

You are assuming that setQueryTimeout() is implemented :)  Calling it
does nothing in the current driver. This is on my list of things to fix,
but not near the top.

On another note, it's generally a bad idea to use the transaction
control primitives (BEGIN, COMMIT, ROLLBACK) directly -- use
Connection.setAutoCommit(false) and Connection.commit() /
Connection.rollback() instead.

-O

Am Mittwoch, 28. Januar 2004 22:21 schrieben Sie:
> Guido Fiala wrote:
> > //user1:
> >
> > stmt.setQueryTimeout(1);//wait just one second
> > ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
> > mytable");
>
> > What am i doing wrong?
>
> You are assuming that setQueryTimeout() is implemented :)  Calling it
> does nothing in the current driver. This is on my list of things to fix,

As i really need this - maybe i could implement that feature myself and
contribute it here? I assume, it's not much more to set some IO-timeout on
the socket-read() after sending the query...

> but not near the top.

Mmmm, is there any possibility to stop that query using another thread in the
same application?

> On another note, it's generally a bad idea to use the transaction
> control primitives (BEGIN, COMMIT, ROLLBACK) directly -- use
> Connection.setAutoCommit(false) and Connection.commit() /
> Connection.rollback() instead.

Yes, but wouldn't i then need a lot of connections, one for each data display
in my application?

So far i could'nt really see a need of connection pooling in my app, when does
one start to need them?

Guido

Guido Fiala wrote:
> Am Mittwoch, 28. Januar 2004 22:21 schrieben Sie:
>
>>Guido Fiala wrote:
>>
>>>//user1:
>>>
>>>stmt.setQueryTimeout(1);//wait just one second
>>>ResultSet rs=stmt.executeQuery("BEGIN;SELECT * FROM mytable FOR UPDATE OF
>>>mytable");
>>
>>>What am i doing wrong?
>>
>>You are assuming that setQueryTimeout() is implemented :)  Calling it
>>does nothing in the current driver. This is on my list of things to fix,
>
>
> As i really need this - maybe i could implement that feature myself and
> contribute it here? I assume, it's not much more to set some IO-timeout on
> the socket-read() after sending the query...

It's harder than that, unfortunately. If you just time-out the read, you
either have to drop the connection entirely or wait for the backend to
complete the original query before you can do further queries (the
backend doesn't know you've given up on that query so will still be
running it).

A better approach is probably to use the query cancellation protocol to
cancel the outstanding query at the backend once the query times out.
This should cause the backend to give up on the query and return an
error, and you should get a fairly prompt SQLException.

>>but not near the top.
>
>
> Mmmm, is there any possibility to stop that query using another thread in the
> same application?

Yes -- see Statement.cancel(), which uses the query cancellation
protocol I mentioned above. You can implement something like
setQueryTimeout() using that and a timer thread. It's going to be a bit
ugly though since the JDBC API makes it very hard (impossible?) to avoid
race conditions between starting the query and cancelling it.

>>On another note, it's generally a bad idea to use the transaction
>>control primitives (BEGIN, COMMIT, ROLLBACK) directly -- use
>>Connection.setAutoCommit(false) and Connection.commit() /
>>Connection.rollback() instead.
>
>
> Yes, but wouldn't i then need a lot of connections, one for each data display
> in my application?

I'm not sure why this would change how many connections you need.. you
can still only have one transaction per connection regardless of whether
you use explicit BEGIN/COMMIT or the JDBC interface methods. The
implementation of Connection.setAutoCommit() and friends just arranges
to have BEGIN etc. sent at the right places. It's safer to let the
driver handle this as there are cases where it needs to know what state
the connection is in (transaction vs. no transaction) and doing your own
transaction demarcation is likely to confuse it. It's a lot more
portable too..

-O