Multiple connections to same dbms within same transaction
mc1976Nov 18 2005 — edited Nov 18 2005Hi guys,
I need your help to understand an issue with connections and transaction. In an EJB method, with transaction_required isolation level,
I create two different connection to the same dbms. The connections have autocommit set to true and the same isolation level, set to TRANSACTION_READ_UNCOMMITTED.
In the first connection, I delete all rows in a table, closing the statement and the connection.
In the second connection, i'm trying to execute several insert statements.
The problem is that the insert statements give me a DuplicateKeyException, like if the delete statement doesn't execute properly.
If I debug the code, I can see - via a command line processor - that after the delete statement the table is empty. Then, the insert statements try to execute, fail, and the transaction is rolled-back correctely.
I'd expect that effects of a connection would be 'visible' to any other connection in the same transaction, but evidently it's not true. In fact, if I execute the delete-and-insert sequence, all works fine.
Can anybody explain me how multiple connections work in a single transaction enviroment ?
Thanks in advance !