openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Milosz Tylenda (JIRA)" <j...@apache.org>
Subject [jira] Reopened: (OPENJPA-1294) Nested JDBC calls fail for certain database
Date Tue, 15 Sep 2009 15:05:57 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-1294?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Milosz Tylenda reopened OPENJPA-1294:
-------------------------------------


I am reopening since I am afraid that by unconditionally opening a new connection (thus a
new transaction) we are unnecessarily increasing chances for deadlock and lowering transaction
isolation level in a write intensive environments. See examples below, I have made them using
command line SQL clients but JDBC connections usually work the same way:

1. MySQL with REPEATABLE READ isolation (the default for MySQL) - lowering isolation.

Without opening a new connection in SelectImpl.getCount:

OPENJPA TX 1: select * from address where country='USA';
OK, returns 1 row.
OTHER TX 2: insert into address(id, country) values(60, 'USA');
OK.
OTHER TX 2: commit;
OK. (does not block in MySQL)
OPENJPA TX 1: select count(*) from address where country='USA';
OK, returns 1 (thus consistent with result in TX 1)

When opening a new connection in SelectImpl.getCount:

OPENJPA TX 1: select * from address where country='USA';
OK, returns 1 row.
OTHER TX 2: insert into address(id, country) values(60, 'USA');
OK.
OTHER TX 2: commit;
OK. (does not block in MySQL)
OPENJPA TX 3: select count(*) from address where country='USA';
OK, returns 2 (inconsistent with result in TX 1)


2. DB2 with SERIALIZABLE isolation (maybe other databases as well) - possible deadlock.

Without opening a new connection in SelectImpl.getCount:

OPENJPA TX 1: select * from address where country='USA';
OK, returns 1 row.
OTHER TX 2: insert into address(id, country) values(60, 'USA');
Blocks until TX 1 commits.
OPENJPA TX 1: select count(*) from address where country='USA';
OK, returns 1
OPENJPA TX 1: commit
OK
OTHER TX 2: commit
OK

When opening a new connection in SelectImpl.getCount:

OPENJPA TX 1: select * from address where country='USA';
OK, returns 1 row.
OTHER TX 2: insert into address(id, country) values(60, 'USA');
Blocks until TX 1 commits.
OPENJPA TX 3: select count(*) from address where country='USA';
Blocks until TX 2 commits. Means deadlock because TX 1 and 3 are from the same OpenJPA thread.


As you can see the problem boils down to the case when an external data modification occurs
in between two OpenJPA transactions.

What do yo think? Am I missing something?

If I am correct, I suggest we try:
1. While in transaction, always use the same connection for SELECT COUNT. I expect databases
handle that (needs some tests).

2. While in autocommit, open a new connection but only for databases with DBDictionary.supportsMultipleNontransactionalResultSets
= false.



> Nested JDBC calls fail for certain database
> -------------------------------------------
>
>                 Key: OPENJPA-1294
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1294
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, kernel, query, sql
>    Affects Versions: 2.0.0-M3, 2.0.0-M4, 2.0.0
>         Environment: MySQL
>            Reporter: Pinaki Poddar
>            Assignee: Pinaki Poddar
>             Fix For: 2.0.0-M3, 2.0.0-M4, 2.0.0
>
>
> Some use cases require a new database connection to perform operation because the current
connection is busy/open.
> Typical use case is asking for size of the result set (obtained by a SELECT COUNT(*)
query) while the original result set is still being open.
> Some databases (e.g. MySQL as per observation) do not allow the new COUNT query on the
same connection because the original result set is still open.
> A JDBCStore.getNewConnection() is added -- but use it with caution, remember to close
it and use it only when required.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message