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 |
+-----------------------------------------------------------+
test.zip
