1. Overview
When we work on multi-threading projects, we know that if multiple threads share objects that are not implemented with thread safety in mind, the threads may behave unexpectedly.
Many of us may have suffered from thread-safe problems. So, the question, “Is this class thread-safe?” often comes to mind.
It’s pretty common for a Java application to access relational databases via JDBC and also make use of multi-threading. In this quick tutorial, we’re going to discuss if java.sql.Connection is thread-safe.
2. The java.sql.Connection Interface
When we access databases via JDBC from our applications, we’ll directly or indirectly use java.sql.Connection objects. We rely on these connection objects to execute database operations. Therefore, java.sql.Connection is a pretty important type in JDBC.
It’s also a common scenario that multiple threads will need to talk to a database concurrently. As a result, we often hear the question, “Is java.sql.Connection thread-safe?”
In the next few sections, we’ll take a closer look at this question. Further, we’ll discuss a proper approach to use java.sql.Connection objects among multiple threads so that multiple threads can access the database simultaneously.
3. Thread Safty and java.sql.Connection
First of all, let’s talk about thread safety quickly. Thread safety is a programming method. That is, it’s an implementation-related concept. Therefore, we can use different techniques to make an implementation thread-safe — for instance, stateless implementations, immutable implementations, and more.
Now, let’s take a look at java.sql.Connection. First of all, it’s an interface — it doesn’t contain any implementation. Therefore, it doesn’t make much sense if we ask in general: “Is java.sql.Connection thread-safe?” We have to check the classes that implement this interface to decide if an implementation is thread-safe or not.
Well, a couple of questions come to mind right away: Which classes implement this interface? Are they thread-safe?
Usually, we don’t implement the java.sql.Connection interface in our application code. JDBC drivers will implement this interface so that we can get the connection to a specific database, such as SQL Server or Oracle.
Therefore, the thread-safety of the Connection implementation is fully dependent on the JDBC drivers.
Next, we’ll explore a couple of database JDBC drivers as examples.
4. java.sql.Connection Implementation Examples
Microsoft SQL Server and Oracle Database are two widely used relational database products.
In this section, we’ll look at the JDBC drivers of these two databases and discuss if their implementations of the java.sql.Connection interface is thread-safe.
4.1. Microsoft SQLServer
The Microsoft SQL Server driver class, SQLServerConnection, implements the java.sql.Connection interface and is not thread-safe, according to its Javadoc:
SQLServerConnection is not thread-safe, however multiple statements created from a single connection can be processing simultaneously in concurrent threads.
So, this means we shouldn’t share an SQLServerConnection object among threads, but we can share the statements created from the same SQLServerConnection object.
Next, let’s take a look at another well-known database product, Oracle Database.
4.2. Oracle Database
The official Oracle JDBC driver implements the java.sql.Connection interface in a thread-safe way.
Oracle states the thread safety of its Connection implementation in its official document:
The Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading …
However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously …
Well, based on the description above, we can say Oracle’s connection implementation is thread-safe. However, sharing a connection object among multiple threads is “strongly discouraged”.
So, from the SQL Server and Oracle examples, we know that we cannot assume that a java.sql.Connection implementation is thread-safe. Then, we may ask, what is the proper approach if we want multiple threads to access a database concurrently? Let’s figure it out in the next section.
5. Using a Connection Pool
When we access a database from our application, we need first to establish the connection to the database. This is considered an expensive operation. To improve the performance, usually, we’ll use a connection pool.
Let’s quickly understand how a connection pool works in a multi-threading scenario.
A connection pool holds multiple connection objects. We can configure the size of a pool.
When multiple threads need to access a database concurrently, they request connection objects from the connection pool.
If there are still free connections in the pool, a thread will get a connection object and start its database operations. After the thread finishes its work, it’ll return the connection to the pool.
In case there is no free connection in the pool, the thread will wait for a connection object to be returned to the pool by another thread.
Therefore, a connection pool allows multiple threads to access the database concurrently using different connection objects instead of sharing the same one.
Further, in this way, we don’t have to care about whether the implementation of the Connection interface is thread-safe.
6. Conclusion
In this article, we’ve discussed the frequently asked question: Is java.sql.Connection thread-safe?
As java.sql.Connection is an interface, it’s not easy to predict if the implementations are thread-safe.
Moreover, we’ve addressed that a connection pool is a proper way to handle connections if multiple threads need to access the database concurrently.
