Thread: insert function runs forever if connection is lost, keeping the tablelocked

insert function runs forever if connection is lost, keeping the tablelocked

From
"Pieter Zieschang"
Date:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 


and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 

all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


Thanks in advance,


Pieter Zieschang


_________________________________________


Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.
I didn't intend not to reply to the list.


On 29 June 2018 at 10:46, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via


                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);




_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Date:        29.06.2018 16:37
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer

davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 




and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 



all set to 2.



Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.



Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?


Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.

So to be clear the application is completely dead ? There are no java threads still alive ?


On 29 June 2018 at 10:48, Dave Cramer <pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.


On 29 June 2018 at 10:46, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via


                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);




_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Date:        29.06.2018 16:37
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer

davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 




and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 



all set to 2.



Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.



Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?


Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.


Re: insert function runs forever if connection is lost, keeping the tablelocked

From
"Pieter Zieschang"
Date:
Hi Dave,


yes, the application died completely due to unhandled NPE.

Client is connecting from windows 10.

I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.


Thanks



_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 16:50
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via



                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 





and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 




all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?


Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.
There is a server side config:

 idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled option which would help in this case.

 Linux could wait some time before eventually killing the connection


On 29 June 2018 at 10:54, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi Dave,


yes, the application died completely due to unhandled NPE.

Client is connecting from windows 10.

I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.


Thanks



_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 16:50
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via



                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 





and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 




all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?


Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.

Re: insert function runs forever if connection is lost, keeping the tablelocked

From
"Pieter Zieschang"
Date:
Hi,


i tried again as suggested with additionally


idle_in_transaction_session_timeout        0
in place,
The transaction still gets not released when the client dies.



But with setting it to 6000, the transaction is sucessfully removed after 6 seconds.



But this will probably now kill every open transaction that takes longer than 6 seconds, right?
Or will it try to contact the client with keep alive first?



Thanks




_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 16:58
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com




There is a server side config:

 idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled option which would help in this case.

 Linux could wait some time before eventually killing the connection

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:54, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi Dave,


yes, the application died completely due to unhandled NPE.


Client is connecting from windows 10.


I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.



Thanks




_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:50
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <
pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via



                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 






and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 





all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?



Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




On 29 June 2018 at 11:57, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,


i tried again as suggested with additionally


idle_in_transaction_session_timeout        0
in place,
The transaction still gets not released when the client dies.



But with setting it to 6000, the transaction is sucessfully removed after 6 seconds.



But this will probably now kill every open transaction that takes longer than 6 seconds, right?
Or will it try to contact the client with keep alive first?

It will kill any "idle in transaction" session longer than 6 seconds.

Why not just fix your app ?


Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 16:58
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com




There is a server side config:

 idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled option which would help in this case.

 Linux could wait some time before eventually killing the connection

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:54, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi Dave,


yes, the application died completely due to unhandled NPE.


Client is connecting from windows 10.


I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.



Thanks




_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:50
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <
pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via



                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.




I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 






and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 





all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?



Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.

Re: insert function runs forever if connection is lost, keeping the tablelocked

From
"Pieter Zieschang"
Date:
Hi,

ok, it will kill anything which takes too long.



Actually the app is fixed by now to not crash.

But the condition of a connection loss of multiple clients of this type (used by different people) is still easily possible.

So i'm worried, that one connection loss causes the database to wait on a lock which never gets released.

Effectively freezing the database for unknown amounts of time for everyone else.


Is there a solution to make the database recognize connection loss and rollback in a reasonable amount of time, say 2 minutes?

Maybe the CentOS doesn't work correctly here?



Thanks for all the help so far.


_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 18:02
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com








On 29 June 2018 at 11:57, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,


i tried again as suggested with additionally



idle_in_transaction_session_timeout        0
in place,
The transaction still gets not released when the client dies.




But with setting it to 6000, the transaction is sucessfully removed after 6 seconds.




But this will probably now kill every open transaction that takes longer than 6 seconds, right?
Or will it try to contact the client with keep alive first?


It will kill any "idle in transaction" session longer than 6 seconds.

Why not just fix your app ?

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        
List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




There is a server side config:

 idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled option which would help in this case.

 Linux could wait some time before eventually killing the connection

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:54, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi Dave,



yes, the application died completely due to unhandled NPE.

Client is connecting from windows 10.

I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.



Thanks




_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:50
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <
pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via


                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.



I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 







and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 






all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?



Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



On 29 June 2018 at 12:10, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,

ok, it will kill anything which takes too long.

No it will only kill "idle in transaction" connections
 




Actually the app is fixed by now to not crash.

But the condition of a connection loss of multiple clients of this type (used by different people) is still easily possible.

So i'm worried, that one connection loss causes the database to wait on a lock which never gets released.

Effectively freezing the database for unknown amounts of time for everyone else.


Is there a solution to make the database recognize connection loss and rollback in a reasonable amount of time, say 2 minutes?


yes, set the idle in transaction setting to 2 minutes..


 
Maybe the CentOS doesn't work correctly here?

No it is correct behaviour. It does not know if the app has died or is going to send it data eventually.
 



Thanks for all the help so far.


_________________________________________

Pieter Zieschang

Modis IT Outsourcing GmbH
Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt

www.modis.de





From:        Dave Cramer <pg@fastcrypt.com>
To:        Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        List <pgsql-jdbc@postgresql.org>
Date:        29.06.2018 18:02
Subject:        Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        davecramer@gmail.com








On 29 June 2018 at 11:57, Pieter Zieschang <pieter.zieschang@modis.de> wrote:
Hi,


i tried again as suggested with additionally



idle_in_transaction_session_timeout        0
in place,
The transaction still gets not released when the client dies.




But with setting it to 6000, the transaction is sucessfully removed after 6 seconds.




But this will probably now kill every open transaction that takes longer than 6 seconds, right?
Or will it try to contact the client with keep alive first?


It will kill any "idle in transaction" session longer than 6 seconds.

Why not just fix your app ?

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Cc:        
List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




There is a server side config:

 idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled option which would help in this case.

 Linux could wait some time before eventually killing the connection

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:54, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi Dave,



yes, the application died completely due to unhandled NPE.

Client is connecting from windows 10.

I would expect the socket closes and the server should notice that, or at least use the keep alive settings and detect it after 6 seconds.



Thanks




_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>, List <pgsql-jdbc@postgresql.org>
Date:        
29.06.2018 16:50
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com




So to be clear the application is completely dead ? There are no java threads still alive ?

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:48, Dave Cramer <
pg@fastcrypt.com> wrote:
I didn't intend not to reply to the list.

Dave Cramer


davec@postgresintl.com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

no, this is a plain JRE application which does connect via


                        Properties props = new Properties();
                props.setProperty("user", user);
                props.setProperty("password", pass);
                        conn = DriverManager.getConnection ("jdbc:postgresql://" + dbname, props);
                        conn.setAutoCommit(false);
                        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);





_________________________________________


Pieter Zieschang


Modis IT Outsourcing GmbH

Kohlgartenstrasse 11, 04315-Leipzig

Amtsgericht Düsseldorf: HRB 78227
Geschäftsführer: Martin Wimmer, Andreas Buchelt


www.modis.de





From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 16:37
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com





On 29 June 2018 at 10:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

Thanks for your answer, unfortunately it didn't solve the problem.



I tried setting CallableStatement.setQueryTimeout(6).
This works, but causes the 2nd software run to die, because the deceased first run did never release the lock.
Die dead application however keeps the lock waiting.



Is there any chance that you have a connection pool in the middle ?




Dave Cramer


davec@postgresintl.com
www.postgresintl.com


From:        
Dave Cramer <pg@fastcrypt.com>
To:        
Pieter Zieschang <pieter.zieschang@modis.de>
Date:        
29.06.2018 14:58
Subject:        
Re: insert function runs forever if connection is lost, keeping the table locked
Sent by:        
davecramer@gmail.com








On 28 June 2018 at 09:27, Pieter Zieschang <
pieter.zieschang@modis.de> wrote:
Hi,

running pg-jdbc 42.2.2.jre7 against
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
 







and using CallableStatement interface.
When the application crashes after cstmt.execute(), not closing or rolling back  anything the query stays active and keeps the table locked (the function, uses in/out parameters and inserts to a table).
This would probably be the same if the client suddenly disconnects with a open CallableStatement which was executed but not closed, committed or rolled back.

The application in question is required to use CallableStatement.

I tried with settings
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
 






all set to 2.


Doesn't help, the query is still listed as active and the table lock is preventing other connections from doing anything for hours.


Is there any solution to this kind of deadlock caused by connection being lost?
What i would expect to happen: The DB recognises the connection is gone within 6 seconds and rolls back the transaction, thereby releasing the lock.


That is exactly what I would expect to happen as well. 

JDBC has a statement timeout setting. Have you tried that ?

What logs do you have from the server ?



Dave Cramer


davec@postgresintl.com
www.postgresintl.com


Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.




Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.



Diese E-Mail und alle Anhänge sind vertraulich und für den Adressaten bestimmt und können auch privilegiert oder von der Offenlegung nach geltendem Recht ausgenommen sein. Wenn Sie nicht der Adressat sind oder diese E-Mail irrtümlicherweise erhalten haben, benachrichtigen Sie den Absender unverzüglich, löschen Sie die E-Mail aus Ihrem System und kopieren Sie keine Teile dieser E-Mail oder deren Anhänge und geben Sie sie nicht weiter.

This email and any attachments are confidential and intended for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this email in error, please notify the sender immediately, delete it from your system and do not copy, or disclose or otherwise act upon any part of this email or its attachments.