Java – JDBC transaction and connection clarification

I'm using JDBC to talk to my Postgres database If my entire application runs a single connection, that is, there is only one call;

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

However, this connection object is shared among multiple threads in Java. I assume that any attempt to use SQL transactions (begin and commit style) will only become very chaotic and destructive, because Java threads may be staggered? The connection object "knows" which java thread is using it for queries?

Should I have a connection object per java thread and use SQL transactions in this way? Or should I use synchronized to perform all transaction isolation in Java?

Solution

Just specify the existing answers:

Pgjdbc's connection object is thread safe, but only at one statement level Using multiple threads in auto commit mode will not crash or produce wrong results, but it will not isolate transactions from different threads for you According to the documentation, you need to use connection pooling

In fact, there are many ways to use connections between multiple threads:

>Use the internal connection pool from which to get connections, perform mating with them, and return them to the pool For most applications, this is a very good choice There are many JDBC connection pool implementations in Java, so don't scroll by yourself DBCP and c3p0 are two popular implementations, but if you are using a servlet environment or an application server, you should usually use the server's connection pool instead of its own. > Use an external connection pool, such as pgbouncer or pgpool II, and open / close connections freely This is a little slower, mostly because the application cannot or cannot be connected internally for various reasons You may not need to do this unless you need to limit the total number of connections to the database and share them among multiple applications or application instances. > Do not use the swimming pool and open / close the connection freely This is very inefficient Do not do this > use thread local storage to keep each thread connected This will work, but it is very inefficient because each open connection binds database server resources when idle Do not do this unless you use an external connection pool such as pgbouncer in transaction pool mode, in which case you can. > Use only a single connection, wrap the transaction in the synchronization block, and synchronize on the connection instance This will work and will effectively use database connections, but will limit the performance of your threads Not usually a good design except for toy / convenience applications. > Use only a separate connection with your own dedicated thread There are other connections through the FIFO queue, and the producer / consumer style passes the data structure describing the work to be completed This works if threads spend most of their time performing CPU heavy or other non database work and require only limited database access The only reason many use it instead of using connection pool is if you are limited to using a single connection for some external reasons, but if you are a considerable choice

Generally speaking, however, you should use a connection pool and complete it