Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is possible to have two concurrent transactions in one JDBC connection?

Right now I have two objects sharing a database connection to an Oracle database in auto commit mode. But now both objects need to start their own transaction to get their work done.

What is the consequence?

Is it necessary to give each object its own connection in order to have concurrent transactions or is possible to keep the code as it is and use the same connection for two concurrent transactions?

And what is the best practice, if I have 10000 objects instead of 2? How many database connections do I need, if it may be possible for every object to start a transaction. Do I need 10000 database connections?

like image 867
ceving Avatar asked Sep 18 '13 16:09

ceving


People also ask

Can DB connection have multiple transactions at once?

Absolutely no. every "transaction" must have begin, complete or rollback.

Can we connect multiple database in JDBC?

Connection mysqlCon = DriverManager. getConnection(mysqlUrl, "root", "password"); To connect to multiple databases in a single JDBC program you need to connect to the two (or more) databases simultaneously using the above steps.

How many JDBC connection isolation levels are there?

JDBC transaction isolation levels There are five levels of transaction isolation in the IBM® Developer Kit for Java™ JDBC API.

How to set transaction isolation level in java?

The setTransactionIsolation() method of the Connection interface is used to change the transaction isolation level for the current connection.


1 Answers

It is not possible to have two transactions on one connection. Apart from any potential threading issues in the Connection, there is only one commit() method and it commits all activity since the last commit/rollback, regardless of which object they came from.

Use two connections if you need two transactions.

If you have 1000's of objects, then use a connection pool to rationalize the number of active connections to the database.

like image 120
rolfl Avatar answered Sep 21 '22 17:09

rolfl