Test1&Test2.
There two connections at two threads (thread per connection) or at two JVMs
(JVM per conection).
Transaction isolation level is serializable.
(connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);)
Tested JDBC drivers:
classes12.zip 1552313
classes12.zip 1568843
classes111.zip 1440531
Table metadata:
create table test( id number(10) not null, pos number(10) not null)
create unique index testid on test(id)
or
create table test( id number(10) not null primary key, pos number(10) not
null)
Table data:
insert into test (id, pos) values (1,1)
insert into test (id, pos) values (2,2)
Test 1 sequence:
~~~~~~~~~~~~
Connection1:
Connection2:
Connection2: SELECT id, pos FROM test WHERE id=2
Connection1: UPDATE test SET pos=10 where id=1
Connection1: commit
Connection2: UPDATE test SET pos=20 where id=2 ----> Exception:
java.sql.SQLException: ORA-08177: can't serialize access for this
transaction
Test 2 sequence:
~~~~~~~~~~~~
Connection2: SELECT id, pos FROM test WHERE id=2
Connection1: UPDATE test SET pos=10 where id=1
Connection2: UPDATE test SET pos=20 where id=2
Connection1: commit
Connection2: commit
The transactions completed successfully!!!
Comments: Exception: java.sql.SQLException: ORA-08177: can't serialize
access for this transaction
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1311)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:738)
at
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1313
)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1232)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java:1
353)
at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1760)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:1805)
at
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:531)
at transaction.TransactionTestStmt.update2(TransactionTestStmt.java:439)
at transaction.TransactionTestStmt.access$2(TransactionTestStmt.java:19)
at
transaction.TransactionTestStmt$Transaction2.run(TransactionTestStmt.java:50
8)
at java.lang.Thread.run(Thread.java:479)
So the transactions are not intersected: no common to select or to update
and,
for example, Interbase and MSSQL pass both tests successfully.
More than, I cannot repeat test with using two SQLPlus windows :)
Questions: where bug and what this bug is it?