Thread: 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
Attachment
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