Hi! I'm can't get transactions isolating sessions:
Environment: User A and User B. User A will delete from times-to-times a
large amount of data, and re-insert them using standalone Java app through
JDBC. User B is connecting by JDBC from a Tomcat WEB Server (pool of 10
connections). SapDB 7.4.3.10 using latest sapdbc.jar from ftp.sap.com. I've
tried User B as WebSQL with same results...
Steps (or Wish List):
1) User B issue "select count(*) from MyTable", resulting in, let's say,
70000 records.
2) User A issue "begin transaction" (cnSap.setAutoCommit(false))
3) User A issue "delete from MyTable", deleting all records
4) User B issue "select count(*) from MyTable", resulting in 70000 records
again, because user A don't had commited commands
5) User A issue 90000 inserts. During this time, User B always see 70000
records, as in step 1, because user A don't commit.
6) User A commit (cnSap.commit())
7) User B see now 90000 records, because user A already commited.
Maybe I'm really wrong in my tests (and teory), but I've tested MS SQL
Server, with two "Query Analizers", and sessions are isolated when using
transactions...
I've tested a little program doing deletes, inserts and commits, but User B
is being sensitive about changes in User A session...
Now, I'll drop some code I already tested to isolate user sessions (just
don't work):
Test 1 (cnSap is already open, SQL mode Internal):
try {
System.out.println("Start importing");
boolean autoCommit = cnSap.getAutoCommit();
cnSap.setAutoCommit( false );
// in real, I already tested all transaction types!
cnSap.setTransactionIsolation(
java.sql.Connection.TRANSACTION_READ_COMMITTED );
System.out.println("Deleting existing data");
stSap = cnSap.prepareStatement( "DELETE FROM MyTable" );
stSap.executeUpdate();
stSap = cnSap.prepareStatement( "INSERT INTO MyTable ( C1, C2 )
VALUES ( ?, ? )" );
int nRegistros = 0;
while( nRegistros < 90000 ) {
nRegistros++;
if( nRegistros % 1000.0 == 0 )
System.out.println( "Records: " + nRegistros );
stSap.setInt( 1, nRegistros );
stSap.setString( 2, "Something else" );
stSap.addBatch( );
}
System.out.println( "Records: " + nRegistros );
System.out.println("Executing batch!");
stSap.executeBatch();
System.out.println("Commit!");
cnSap.commit(); // Changes should be visible by User B only
now!!!
cnSap.setAutoCommit( autoCommit );
System.out.println("End.");
} catch( Exception e ) {
try {
cnSap.rollback();
} catch( Exception e1 ) {}
e.printStackTrace( );
return;
} finally {
try {
stSap.close();
} catch( Exception e1 ) {}
// Connection still open for anything else...
}
Can someone help me? What I'm doing wrong?
Thanks!
Edson Richter
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.461 / Virus Database: 260 - Release Date: 10/3/2003
smime.p7s
Description: S/MIME cryptographic signature
