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

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

Dave Cramer

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

On 29 June 2018 at 10:48, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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(C
>> onnection.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.
>>
>
>