From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Pieter Zieschang <pieter(dot)zieschang(at)modis(dot)de> |
Cc: | List <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: insert function runs forever if connection is lost, keeping the table locked |
Date: | 2018-06-29 14:58:06 |
Message-ID: | CADK3HHKdv=28JMVcj0e4c033s62eSeULHG2gujQZYiBKDf8maA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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(at)postgresintl(dot)com
www.postgresintl.com
On 29 June 2018 at 10:54, Pieter Zieschang <pieter(dot)zieschang(at)modis(dot)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
> <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>, List <
> pgsql-jdbc(at)postgresql(dot)org>
> Date: 29.06.2018 16:50
> Subject: Re: insert function runs forever if connection is lost,
> keeping the table locked
> Sent by: davecramer(at)gmail(dot)com
> ------------------------------
>
>
>
> So to be clear the application is completely dead ? There are no java
> threads still alive ?
>
> Dave Cramer
>
> *davec(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
> On 29 June 2018 at 10:48, Dave Cramer <*pg(at)fastcrypt(dot)com*
> <pg(at)fastcrypt(dot)com>> wrote:
> I didn't intend not to reply to the list.
>
> Dave Cramer
>
> *davec(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
> On 29 June 2018 at 10:46, Pieter Zieschang <*pieter(dot)zieschang(at)modis(dot)de*
> <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* <https://www.modis.de/>
>
>
>
>
>
> 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 16:37
> 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 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.
>
>
>
> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pieter Zieschang | 2018-06-29 15:57:29 | Re: insert function runs forever if connection is lost, keeping the table locked |
Previous Message | Pieter Zieschang | 2018-06-29 14:54:52 | Re: insert function runs forever if connection is lost, keeping the table locked |