insert function runs forever if connection is lost, keeping the tablelocked
| От | Pieter Zieschang |
|---|---|
| Тема | insert function runs forever if connection is lost, keeping the tablelocked |
| Дата | |
| Msg-id | OFB5D0986A.1485D837-ONC12582BA.00298984-C12582BA.0049EC12@LocalDomain обсуждение исходный текст |
| Список | pgsql-jdbc |
Hi,
running pg-jdbc 42.2.2.jre7 against
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
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
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.
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.
В списке pgsql-jdbc по дате отправления: