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?
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.