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

I didn't intend not to reply to the list.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 29 June 2018 at 10:46, Pieter Zieschang <pieter(dot)zieschang(at)modis(dot)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
> <https://maps.google.com/?q=Kohlgartenstrasse+11,+04315-Leipzig&entry=gmail&source=g>
>
> Amtsgericht Düsseldorf: HRB 78227
> Geschäftsführer: Martin Wimmer, Andreas Buchelt
>
> www.modis.de
>
>
>
>
>
> From: Dave Cramer <pg(at)fastcrypt(dot)com>
> To: Pieter Zieschang <pieter(dot)zieschang(at)modis(dot)de>
> Date: 29.06.2018 16:37
> Subject: Re: insert function runs forever if connection is lost,
> keeping the table locked
> Sent by: davecramer(at)gmail(dot)com
> ------------------------------
>
>
>
>
> On 29 June 2018 at 10:27, Pieter Zieschang <*pieter(dot)zieschang(at)modis(dot)de*
> <pieter(dot)zieschang(at)modis(dot)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(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
>
> From: Dave Cramer <*pg(at)fastcrypt(dot)com* <pg(at)fastcrypt(dot)com>>
> To: Pieter Zieschang <*pieter(dot)zieschang(at)modis(dot)de*
> <pieter(dot)zieschang(at)modis(dot)de>>
> Date: 29.06.2018 14:58
> Subject: Re: insert function runs forever if connection is lost,
> keeping the table locked
> Sent by: *davecramer(at)gmail(dot)com* <davecramer(at)gmail(dot)com>
> ------------------------------
>
>
>
>
>
>
>
> On 28 June 2018 at 09:27, Pieter Zieschang <*pieter(dot)zieschang(at)modis(dot)de*
> <pieter(dot)zieschang(at)modis(dot)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(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://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.
>