|
Edson
Carlos Ericksson Richter wrote:
> 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? Short
answer: nothing.
A
little bit longer:
SAP
DB deletes rows at the time they are asked to be
deleted,
not
during commit or later.
--> Other tasks searching around will not SEE
that there are changes (deletes)
at work, because the row has
gone.
--> delete tab1 from
task1; will result (with default isolation level or lower)
in count(*) from tab1 done by another task to result :
0.
In case other isolation levels are used (higher ones) the whole
table
will be locked for a while and things are
different
On
the other hand: updates/inserts have the row in the table, the other
task
searching around will find the row, check for lock
and see:
oops,
may be a fine row, but it is locked, let us wait until commit
of
the
task doing the change.
--> with SAP DB as it is, readers are influenced
(have to wait) by writers.
Perhaps, at some time, this may change, but it is and
will be in the
medium future (at
least).
So,
for SAP DB 4) and 5) of your list do not describe the behaviour of task B
correctly.
Elke
SAP
Labs Berlin
|
- Transactions Management Edson Carlos Ericksson Richter
- Transactions Management Edson Carlos Ericksson Richter
- Zabach, Elke
