I think I found the reason of the lock... But I don't have a solution
(yet)...
Since the timer is using isolation level 1 (default), an implicit exclusive
lock is placed on each new record inserted in table T... These locks will
remain until the timer transaction is committed...
The other thread (transaction) does a "select * from T" tripping the
exclusive locks set by the timer thread and therefore will wait until the
timer thread releases the locks.
Can someone indicate me a way to support "long" transaction that insert,
update or delete records without locking the other thread that are just
interested in the non-committed records... I wish the isolation level 1
would just skip the uncommitted inserts from other concurrent transactions
without locking...
Thanks for any help on the subject.
Benoit
----- Original Message -----
From: "Benoit Menendez" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 14, 2002 11:19 PM
Subject: Question about transaction isolation level
> I am using SapDB with JBoss and I ran into the following problem, maybe
> someone can help... I have already posted to the JBoss forum, but they
> suspect it is a database locking issue.
>
> I am using JTA (UserTransaction) with JBoss using the XADataSourceLoader
and
> the SapDB database
>
> I have a Timer firing relatively long transactions as follow (pseudo
code):
>
> --------------------------------------------------------------------------
--
> UserTransaction transaction = getUserTransaction();
> transaction.begin();
> Connection connection = getConnection(); // using XA connection pool
> datasource obtained with JNDI
> Statement statement = connection.prepareStatement("select ... from T where
> ...");
> ... // some short work
> statement.close();
> connection.close();
> loop(many times) {
> // slow network access (wait)
> Connection connection = getConnection();
> Statement statement = prepareStatement("insert into T ...");
> ... // set fields
> statement.execute();
> statement.close();
> connection.close();
> }
>
> transaction.commit();
> --------------------------------------------------------------------------
--
>
> This works fine, but the transaction can last a couple minutes, if I try
to
> access the same table from another thread (and another UserTransaction),
the
> database will hang (lock) until the Timer transaction is commited.
>
> I don't set any isolation level (so I assume I am using read commited by
> default).
>
> What am I doing wrong?
>
> Thanks for you help,
>
> Benoit
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
