JDBC Transaction vs Connection Clarification

Question

I am using JDBC to talk to my Postgres database. If my entire app runs off a single connection, ie there is only ever one call to;

DriverManager.getConnection("jdbc:postgresql://host:5432/database", user, pass);

But this Connection object is shared across multiple threads in the Java, am I right in assuming that any attempt to use SQL transactions (BEGIN and COMMIT style) is only going to be very confusing and broken, given the potential for the Java threads to interleave? Does the Connection object 'know' which Java thread is using it to make queries?

Should I have one Connection object per Java thread and use the SQL transactions that way? Or should I perform all my transactional isolation in the Java using synchronized?

Answer 1

Just to elaborate on the existing answers:

PgJDBC's Connection object is thread-safe, but only on a statement level. It won't crash or produce wrong result when used by multiple threads in autocommit mode but it won't isolate different threads' transactions for you. As per the documentation you need to use a connection pool for that.

There are actually lots of ways to use connections among multiple threads:

• Use an internal connection pool where you fetch connections from, perform work with them, and return them to the pool. This is the strongly preferable option for most applications. Many JDBC connection pool implementations exist for Java, so don't roll your own. dbcp and c3p0 are two popular implementations, but if you're using a servlet environment or app server you should generally use the server's connection pool rather than bringing your own.

• Use an external connection pool like pgbouncer or pgpool-II and open/close connections to it freely. This is slightly slower and is mostly an option used where the application cannot or for various reasons should not pool connections internally. You probably don't need to do this unless you need to limit total connection counts to the DB and share them between multiple applications or app instances.

• Use no pool and open/close connections freely. This is terribly inefficient. Don't do it.

• Keep a connection per thread using thread local storage. This'll work, but it's grossly inefficient because each open connection ties up database server resources while it sits idle. Don't do this unless you use an external connection pool like PgBouncer in transaction pooling mode, in which case it's OK.

• Use only a single connection and wrap transactions in synchronized blocks, synchronizing on the Connection instance. This'll work and will use the database connection efficiently but will limit your threads' performance. It's generally not a good design for anything except toy/convenience apps.

• Use only a single connection with its own dedicated thread. Have other connections pass data structures describing work to be done to that thread via a FIFO queue, producer/consumer style. This works if the threads spend most of their time doing CPU-heavy or other non-database work and need only limited database access. Pretty much the only reason to use it instead of using a connection pool is if you're constrained to using a single connection for some external reason, but if you are then it can be a decent option.

In general, though, you should just use a connection pool and be done with it.