Обсуждение: Socket timeouts (probably)

Hi,

I have a really strange problem here. I've got two Fedora 2 machines -
one for the database and one for a Java application to be run. When a
transaction from the application is run against the database and the
network cable is unplugged (or the machine is shut down) the database
continues to hold the lock on the tables, which are used in the
transaction. The lock is released in about 10-12 minutes, which in my
case is not acceptable, because if another client starts a transaction
against these locked tables his UI ties his hands until the lock on
the tables is released and the transaction is complete.
I started a 'netstat -atp' on the database machine and it turned out
that the connection with the unplugged machine is still active (5
connections actually, because of the connection pooling). So I assumed
that the sockets are just hanging there until they timeout and then
the locks are released.
My questions are is my assumption right and if that's true is there
any way to set the postmaster's sockets timeout to for example 2
minutes.

I appreciate any help on solving this.

Best regards
Sergei Georgiev

Hi, Sergej,

Sergei Georgiev schrieb:
> I have a really strange problem here. I've got two Fedora 2 machines -
> one for the database and one for a Java application to be run. When a
> transaction from the application is run against the database and the
> network cable is unplugged (or the machine is shut down) the database
> continues to hold the lock on the tables, which are used in the
> transaction. The lock is released in about 10-12 minutes, which in my
> case is not acceptable, because if another client starts a transaction
> against these locked tables his UI ties his hands until the lock on
> the tables is released and the transaction is complete.
> I started a 'netstat -atp' on the database machine and it turned out
> that the connection with the unplugged machine is still active (5
> connections actually, because of the connection pooling). So I assumed
> that the sockets are just hanging there until they timeout and then
> the locks are released.

Maybe you can set the TCP connection keepalive probing on the server to
about 30 seconds or so via /proc/sys/net. But this is only advisable if
your server does not serve any other purposes.

Markus


--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Sergei Georgiev wrote:

> I have a really strange problem here. I've got two Fedora 2 machines -
> one for the database and one for a Java application to be run. When a
> transaction from the application is run against the database and the
> network cable is unplugged (or the machine is shut down) the database
> continues to hold the lock on the tables, which are used in the
> transaction. The lock is released in about 10-12 minutes, which in my
> case is not acceptable, because if another client starts a transaction
> against these locked tables his UI ties his hands until the lock on
> the tables is released and the transaction is complete.

I raised this on -hackers recently. Ideally I'd like to see a
transaction timeout implemented on the server side for exactly this
case. See
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00397.php (the
archive indexes seem to be broken at the moment, but the direct link works).

You may want to try tweaking /proc/sys/net/ipv4/tcp_keepalive_* on the
server to make TCP keepalives notice the dead connection faster (these
are global settings, though). There are corresponding setsockopt() calls
you could make on a per-socket basis if you are prepared to tweak the
server code.

-O

> You may want to try tweaking /proc/sys/net/ipv4/tcp_keepalive_* on the
> server to make TCP keepalives notice the dead connection faster (these
> are global settings, though). There are corresponding setsockopt() calls
> you could make on a per-socket basis if you are prepared to tweak the
> server code.

I'd rather not, because as Markus said there may be other services
started on the DB machine in the future (it's just a prototype testing
for a new project). I found that there is a property called
"statement_timeout" in the postgresql.conf. This limits the time, in
which a statement must be executed. It looks like a solution to me,
but I need to test it.
10x anyway :)

Sergei Georgiev wrote:

> I found that there is a property called
> "statement_timeout" in the postgresql.conf.  This limits the time, in
> which a statement must be executed. It looks like a solution to me,
> but I need to test it.
> 10x anyway :)

statement_timeout will only catch cases where the network fails while
the client is partway through sending a query or receiving (lots of)
result data.

-O