Is it possible to have two concurrent transactions in the same JDBC connection?

Question

Right now, I have two objects that share a connection to an Oracle database in auto-commit mode. But now both objects must start their own transaction in order to do their job.

What are the consequences?

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

And what's the best practice if I have 10,000 objects instead of 2? How many database connections do I need if each object can start a transaction. Do I need 10,000 database connections?

Answer 1

It is not possible to have two transactions on the same connection. In addition to any possible problems with threads in Connection, there is only one commit () method , and it performs all the actions since the last commit / rollback, regardless of which object they came from.

Use two connections if you need two transactions.

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