Connection zombies on database!

[JDBC-0]

Driver version

e.g. 12.4.1 or 12.8.1

SQL Server version

Microsoft SQL Server 2019 (RTM-CU27-GDR) (KB5040948) - 15.0.4382.1 (X64) Jul 1 2024 20:03:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10 Pro 22H2

JAVA/JVM version

Occurs e.g. in both 17.0.8 or 21.0.4

Problem description

SQL Server sp_who shows connections for processes that don't exist anymore.

Expected behavior

If the Java App closes the database connection, those connections should be not visible anymore on the SQL server.

Actual behavior

exec sp_who2 shows misc (many; increasing) "open" connections that are closed by the application.

Any other details that can be helpful

We inspected the thread dump of a Java VM and searched for threads that can be related to the connections. (We name a databse connection with the Java Thread ID !) But non of the hanging connections belong to still existing Java threads.

Those zombie connections only vanish if we exit the Java application. As we can't identify still running threads for this connections we think that the driver (or the driver's DLL ...) somehow does not relase those connections. Those DB connections only vanish if we stop/exit the Java application.

With our thread and connection naming, we are sure, that the originators of those connections are our Java application threads and that those threads don't exist anymore (not in thread dump) and those threads Java code have standard closing of connections included.

This does not occur for all connections. Some driver versions earlier, we didn't see this problem.

[JDBC-0]

Wait ... we were able to reproduce 1 thread that left the connection open. We debugged the whole chain of destructors and found a link class that didn't overwrite the destruct() method. We were able to fix this and this connection vanished. => our fault.

I guess there are no finalizers for connections if no one is referenzing them. And finalize is deprecated now (Java 21). Is there a way to get infos from the Driver what connections are open, who created them (stack trace?) and what their current state is?

[Jeffery-Wasty]

Hi @JDBC-0,

We'll look into this.

[JDBC-0]

Wow, you never stop learning: one reason why a connection was still open was: not all statements were explicitely closed! Until last week I was sure that closing a connection will also close all of its open statements (perhaps in jTDS this was the case) ... but that does not seem to be the case with this driver? @Jeffery-Wasty

[Jeffery-Wasty]

No, you need to explicitly close all open statements (this is actually a common mistake).

We'll be closing this issue now, but let us know if you have anything further you'd like to discuss.

[JDBC-0]

@Jeffery-Wasty : OK, we introduced some central list to remember open connections but this is not 100% perfect as we might forget some places where we open/close connections. The question is: is the driver able to offer such lists/infos about open connections/statements? That would be a 100% complete single point of truth about open connections/statements.

Are open ResultSets also a problem concerning open connections?

[Jeffery-Wasty]

No, the driver does not offer this. The user is given full responsibility in making sure any resources that need to be closed, are closed. I would need to check if this is offered in our logging somewhere, but that would not be a solution to your problem as keeping logging on continuously would be quite performance intensive.

I'm curious how this Java application is being worked on? Most IDEs and editors should give a warning if there are unclosed resources.

ResultSets should also be closed, but we haven't noticed as many issues related to unclosed ResultSets, as we have for statements and connections.