[
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.
