Transactions Management

[Edson Carlos Ericksson Richter]

Hi!   I'm need the following "workflow" correct running with SapDB:   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.459 / Virus Database: 258 - Release Date: 25/2/2003