Is Java.Sql.Connection Thread Safe

Is java.sql.Connection thread safe?

If the JDBC driver is spec-compliant, then technically yes, the object is thread-safe, but you should avoid sharing connections between threads, since the activity on the connection will mean that only one thread will be able to do anything at a time.

You should use a connection pool (like Apache Commons DBCP) to ensure that each thread gets its own connection.

Java thread safe database connections

I don't think that making database connections thread-safe is a common practice. Usually what you want is either:

• Serialize the access to some part of your servlet, so that there is no more than one servlet executing code at a time (e.g. implementing the SingleThreadModel interface).
• Locking a particular table / table page / row so you can operate on some particular tuple (by changing the database isolation level).
• Using optimistic locking to detect modified rows in a table (using some reference attribute of the table to check if the current version is the same that the one in the table).

AFAIK, the typical use of ThreadLocal<Connection> is to store a unique database connection per thread, so that the same connection can be used in different methods in your business logic without the need of passing it as a parameter each time. Because the common servlet container implementation uses a thread to fulfill an HTTP request, then two different requests are guaranteed to use two different database connections.

can Multiple threads share the same database connection to execute multiple prepareStatement in simultaneously?

In theory, JDBC connections are required to be thread-safe, but in practice it is problematic for two reasons:

1. Not all drivers are actually thread-safe, and even drivers that claim to be thread-safe are likely not robustly tested for this thread-safety, as using a connection concurrently from multiple threads is not recommend and uncommon.

2. Using a single connection concurrently from multiple threads comes with all kinds of additional coordination problems: one thread committing or rolling back, or switching from auto-commit false to auto-commit true, will break things for all other threads on the same connection: work gets lost, or active result sets are suddenly closed (e.g. due to commit, or the JDBC requirement that execution of a statement in auto-commit will close any previous result sets on the same connection).

   Also, if a connection breaks (or is closed by one of the threads), this will impact all your other threads as well.

Additionally, if a driver is thread-safe, this thread-safety is usually achieved with a lot of synchronized blocks or other forms of mutual exclusion. This can seriously impair performance over using separate connections, because the other threads will have to wait on each other.

Using a connection per thread is a lot simpler, as it isolates the work from one thread from all others. In addition, it will usually perform better because of the absence of contended access to a connection. If your concern is about the setup cost of having multiple connection, then you should do two things:

1. Use a connection pool, which allows you to easily reuse connections.
2. Use a thread pool with a limited size to execute the actual work.

Doing this has a lot of benefits: you can submit individual units of work to the thread pool; the unit of work obtains a connection from the pool, and returns it when it is done (by closing the connection). Using a connection pool in this way also allows you to easily replace broken connections without having to handle this in your application code itself (other than maybe a retry for the task that broke a connection), and finally using both a connection pool and thread pool allows you to experiment with sizing both to see what is a good size for giving your application the best throughput.

JDBC transactions in multi-threaded environment

Two points:

1. You shouldn't share a jdbc.Connection between threads, at least for any 'seriously production' code, see here. For demo purposes, I think, sharing a Connection is OK;
2. If a thread reads from DB after relevant DB transaction is committed, it will see data written by another thread.

---

For your second question

will thread B timeout until the first transaction has commit() or rollback()

-- B will block till A tx is finished (either by commit or rollback) if:

1. B tries to update/delete same table row which is being updated by A, and ...
2. A updates that row under DB-level lock, using SELECT ... FOR UPDATE.

You can get this behavior using two consoles (for example, with PostgreSQL psql), each console stands for a thread:

in A console type following:

BEGIN;
SELECT some_col FROM some_tbl WHERE some_col = some_val FOR UPDATE;

now in B console type:

BEGIN;
UPDATE some_tbl SET some_col = new_val WHERE some_col = some_val;

You should see that UPDATE blocks until in A you do either COMMIT or ROLLBACK.

Above explanation uses separate DB connections, just like Java JDBC connection pool. When you share single connection between Java threads, I think, any interaction with DB will block if connection is used by some other thread.

JDBC and Multithreading

As rohivats and Asaph said, one connection must be used by one and only one thread, that said, consider using a database connection pool. Taking into account that c3p0, DBCP and similars are almost abandoned, I would use HikariCP which is really fast and reliable.

If you want something very simple you could implement a really simple connection pool using a thread safe collection (such as LinkedList), for example:

 public class CutrePool{
      String connString;    
      String user;
      String pwd;

      static final int INITIAL_CAPACITY = 50;
      LinkedList<Connection> pool = new LinkedList<Connection>();
      public String getConnString() {
          return connString;
      }
      public String getPwd() {
          return pwd;
      }

      public String getUser() {
          return user;
      }

      public CutrePool(String connString, String user, String pwd) throws SQLException {
          this.connString = connString;
        
          for (int i = 0; i < INITIAL_CAPACITY; i++) {
               pool.add(DriverManager.getConnection(connString, user, pwd));
          }
          this.user = user;
          this.pwd = pwd;
      }

      public synchronized Connection getConnection() throws SQLException {
          if (pool.isEmpty()) {
              pool.add(DriverManager.getConnection(connString, user, pwd));
          }
          return pool.pop();
      }
    
      public synchronized void returnConnection(Connection connection) {
          pool.push(connection);
      }  
  }

As you can see getConnection and returnConnection methods are synchronized to be thread safe. Get a connection (conn = pool.getConnection();) and don't forget to return/free a connection after being used (pool.returnConnection(conn);)

Creating thread safe MyBatis sessions from a java.sql.Connection

It does not appear to be documented but the Snowflake's JDBC Driver package does offer a basic DataSource class that can fetch entirely new connection objects when DataSource::getConnection() is called:

import net.snowflake.client.jdbc.SnowflakeBasicDataSource;

SnowflakeBasicDataSource ds = new SnowflakeBasicDataSource();

ds.setUrl("jdbc:snowflake://account.region.snowflakecomputing.com/");

ds.setUser("user");
ds.setPassword("password");
ds.setWarehouse("wh");
ds.setDatabaseName("db");
ds.setSchema("schema");
ds.setRole("role");

// Other arbitrary connection or session properties can be passed
// via URL params in the ds.setUrl(...) call above

// Use ds.setOauthToken(...)
// or  ds.setAuthenticator(...)
// or  ds.setPrivateKey(...)
// or  ds.setPrivateKeyFile(...)
// for alternative modes of authentication

Connection conn = ds.getConnection();

---

Related Topics