OpenJPA
  1. OpenJPA
  2. OPENJPA-1294

Nested JDBC calls fail for certain database

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-M3
    • Fix Version/s: 2.0.0-beta
    • Component/s: jdbc, kernel, query, sql
    • Labels:
      None
    • Environment:
      MySQL

      Description

      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.

        Activity

        Donald Woods made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Donald Woods made changes -
        Fix Version/s 2.0.0 [ 12314019 ]
        Affects Version/s 2.0.0 [ 12314019 ]
        Affects Version/s 2.0.0-beta [ 12314149 ]
        Pinaki Poddar made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Donald Woods made changes -
        Fix Version/s 2.0.0-M3 [ 12314148 ]
        Milosz Tylenda made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Hide
        Milosz Tylenda added a comment -

        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.

        Show
        Milosz Tylenda added a comment - 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.
        Hide
        Milosz Tylenda added a comment -

        Hi Pinaki. I guess the databases affected are those with DBDictionary.supportsMultipleNontransactionalResultSets = false. The name suggests that the problem should occur only while using auto-commit.

        Show
        Milosz Tylenda added a comment - Hi Pinaki. I guess the databases affected are those with DBDictionary.supportsMultipleNontransactionalResultSets = false. The name suggests that the problem should occur only while using auto-commit.
        Pinaki Poddar made changes -
        Field Original Value New Value
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Pinaki Poddar created issue -

          People

          • Assignee:
            Pinaki Poddar
            Reporter:
            Pinaki Poddar
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development