If that is true it really does look like a serious flaw. I'd say that in
general it is unacceptable if statements in an application are executed
against different connections, and therefore, in different
transaction/isolation contexts. Otherwise, all kinds of messes can happen,
as you experience. At least it must be under the control of the application
when and where this happens.
----- Original Message -----
From: "Douma, Ate" <[EMAIL PROTECTED]>
To: "Orion-Interest" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 5:39 PM
Subject: Serious problem with Orion transaction processing: multiple
connections used within a single transaction
> I actually wanted to enter the following problem into Orion Bugzilla but I
> haven't received a password for my account which I created almost a week
> ago. So, if someone with access to the Orion Bugzilla could accommodate me
> and enter it in Bugzilla for me I would be very grateful.
>
> The problem is the following:
>
> When using an XADataSource for CMP transactions, non-primary key CMP
finder
> calls are executed by Orion using a separate connection from the
connection
> in which the transaction is processed.
>
> This problem I have been able to reproduce using Orion 1.3.8, 1.4.5 and
> 1.4.7 against both SqlServer 2000 and Oracle 8.0.5.
> For Oracle I used the latest thin jdbc 8.1.7 driver; for SqlServer I
tested
> with several level 3 and level 4 type drivers, all of which supplied an
> XADataSource.
>
> Using SQL Server, this problem has very serious consequences when using
> transaction isolation level "committed" (which we regard as a minimum
level
> for business applications).
> When within a transaction multiple entities are created, and a business
rule
> requires a check on already existing entities the transaction will
> guaranteed leed to a deadlock situation (which by the way Orion doesn't
> notice).
>
> Example:
> For an entity containing two date fields defining a date period a business
> rule states that no date period may overlap between the entities.
> To be able to enforce this business rule, before the entity is created a
> finder is called which returns the existing entities which have an
> overlapping period definition.
> If for the first entity to be created this finder does not return
anything,
> the entity is created.
> As soon as the ejbStore() of this entity is called SQL Server will lock
the
> entity table against usage from other sessions (bit of a pain this
feature,
> but hey, we have to support SQL Server).
> Then, for the next entity to be created the above described finder is
called
> again.
> As Orion uses a separate connection for these finders a deadlock situation
> within SQL Server occurs.
>
> Consequence:
> We have to define transaction isolation level "uncommitted" for such
> entities to be able to deploy our application against SQL Server leading
to
> the possibility of dirty reads.
>
> Using Oracle (and probably all other major RDBMS's), the problem is even
> more tricky.
> Oracle requires as minimum transaction isolation level "committed".
> Furthermore, Oracle (luckily) does not set a full table lock as a result
of
> modifications. Thus, rdbms deadlocking does not occur as result of the
Orion
> problem.
> But, more seriously, the above described finder won't work reliable
anymore
> because of the (correct) isolation of the current transaction
modifications
> from other connections.
> If in the above example the second entity would have defined a period
> overlapping on the period of the first entity this violation of the
business
> rule will NOT be found as the finder cannot see (correctly) the changes
made
> within the current transaction.
>
> To be able to test the above described problem I've made a (very) simple
> test application with an entity containing two Integer fields. One of the
> fields is the primary key and the other a secondary key.
> In a stateless session EJB I create three entities in one transaction:
> (1,1), (2,2), (3,2).
> Before each creation a finder is used to check the existence of an entity
> with a specified secondary key.
> If everything would go as expected, the creation of the third entity
should
> fail.
> Using SQL Server, the finder check before the creation of the second
entity
> leads to the above described database deadlock.
> Using Oracle, all three entities can be created.
>
> I've attached the test application to this message.
>
> The zip file contains a build ear file, all sources, an ant build.xml
> script, Orion configuration files, and log files.
>
> For testing against SQL Server I've included the latest trial JSQLConnect
> JDBC from NetDirect (www.j-netdirect.com). This driver, which fully
> implements an XADataSource interface, will work until the first of March
> 2000.
> Included with the Orion configuration files is a datasource.xml containing
> two datasource definitions: one using the Oracle thin jdbc 8.1.7 driver
and
> one using the JSQLConnect JDBC driver.
>
> The application defines a web application (on url: /test) with as welcome
> file a jsp presenting a simple submit button. Pressing the button will
> execute the session bean method creating the three entities as described
> above.
> Note: On Oracle the test can only be performed once as all entities are
> incorrectly created. Before testing it again the table needs to be emptied
> first.
>
> For both test's I've included the logfiles from Orion.
> For the JSQLConnect test I've included its JDBC trace log clearly
indicating
> the usage of a separate connection for the finder query.
> For the Oracle test I've put the database in trace mode and included the
> Oracle trace files for the sessions used by the test application.
>
> Hopefully the Orion team can indicate how and when this problem can be
fixed
> or circumvented because right now our primary deployment database is SQL
> Server, and using the transaction isolation level "uncommitted" is not
> acceptable for our customers.
>
> Ate Douma
>
> +-----------------------------------------------------------+
> | Ate Douma iWise B.V. |
> | Hoofdstraat 2a-4a |
> | mailto:[EMAIL PROTECTED] 4941 DC Raamsdonksveer |
> | Phone ++31 (0)162 517167 The Netherlands |
> | Fax ++31 (0)162 516872 http://www.iwise.nl |
> +-----------------------------------------------------------+
>
>
>
