Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-5169

Avoid generating useless SQL rollback and commit queries

XMLWordPrintable

    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      JIRA generates vast amounts of useless SQL for most operations. For every valid line of SQL, there are:

      • three SET autocommit statements
      • a commit
      • a rollback immediately after

      Typical output looks like:

      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM issuelink WHERE SOURCE=10020
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE FROM nodeassociation WHERE SOURCE_NODE_ID=10000 AND SOURCE_NODE_ENTITY='Project' AND SINK_NODE_ENTITY='VersionControl' AND ASSOCIATION_TYPE='ProjectVersionControl'
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT ID, issueid, AUTHOR, actiontype, actionlevel, actionbody, CREATED, actionnum FROM jiraaction WHERE issueid=10020 AND actiontype='comment'
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      041109 10:01:28 1757 Query SET autocommit=1
      1757 Query SET autocommit=0

      Creating an issue generates 657 SQL queries, of which 540 are useless (commits, rollbacks, sets). Of the remaining 117, 44 are duplicates of queries made earlier (a separate issue).

      It is hard to tell what effect on performance all this has. One hopes that databases run these statements very quickly. MySQL takes 0.3ms per set of (commit,rollback,3 x set), amounting to 32ms for the insert. The increase in network overhead is probably a lot more significant.

            [JRASERVER-5169] Avoid generating useless SQL rollback and commit queries

            Brian Donnelly added a comment -

            Greetings,

            I should be noted that this affects all DBs, not jsut MySQL.

            I am running a 98% transaction rollback rate!

            Cheers,
            Brian D.

            Brian Donnelly added a comment - Greetings, I should be noted that this affects all DBs, not jsut MySQL. I am running a 98% transaction rollback rate! Cheers, Brian D.

            Deleted Account (Inactive) added a comment -

            In the SELECT case, don't you give uncommitted transactions when the autocommit status of the connection is false? This happens for instance when you use, for the SELECT, a connection that previously was used for a "multi SQL stmt update". The Java API states that "the (auto)commit occurs statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed". But again, this is when the connection has autocommit set to true.

            Deleted Account (Inactive) added a comment - In the SELECT case, don't you give uncommitted transactions when the autocommit status of the connection is false? This happens for instance when you use, for the SELECT, a connection that previously was used for a "multi SQL stmt update". The Java API states that "the (auto)commit occurs statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed". But again, this is when the connection has autocommit set to true.

            ɹǝʞɐq pɐɹq added a comment -

            This has been fixed.

            In a nutshell the new behaviour is as follows :

            If we are only doing a SELECT, then the autoCommit status of the connection is never changed and no commit() it performed when the connection is placed back in the pool. This will also help ensure DBCP does not need to change anything on passivate

            If OFBIZ needs to do a "single SQL statement update" then the connection is check to see if its in autoCommit=true and if not it is set to that. No commit() is then performed before placing the connection back into the pool.

            If OFBIZ needs to do a "multi SQL statement update" then the connection is checked to see if its in autoCommit=false and if not it is set to that. A commit() is then performed before placing the connection back into the pool.

            ɹǝʞɐq pɐɹq added a comment - This has been fixed. In a nutshell the new behaviour is as follows : If we are only doing a SELECT, then the autoCommit status of the connection is never changed and no commit() it performed when the connection is placed back in the pool. This will also help ensure DBCP does not need to change anything on passivate If OFBIZ needs to do a "single SQL statement update" then the connection is check to see if its in autoCommit=true and if not it is set to that. No commit() is then performed before placing the connection back into the pool. If OFBIZ needs to do a "multi SQL statement update" then the connection is checked to see if its in autoCommit=false and if not it is set to that. A commit() is then performed before placing the connection back into the pool.

            AntonA added a comment -

            The username has a space appended to it, so the lockup against the userbase table failed every time.

            AntonA added a comment - The username has a space appended to it, so the lockup against the userbase table failed every time.

            Paul M added a comment -

            I have tried to find that JIRA by modifying the URL, but I am denied access.

            please can you post a hint here as to how to fix it as I am seeing this very problem.

            thanks
            Paul

            Paul M added a comment - I have tried to find that JIRA by modifying the URL, but I am denied access. please can you post a hint here as to how to fix it as I am seeing this very problem. thanks Paul

            AntonA added a comment -

            The JQL upgrade turned out to be a problem with corrupted data where the user was not being cached and therefore was looked up every time.

            See JRA-18910.

            AntonA added a comment - The JQL upgrade turned out to be a problem with corrupted data where the user was not being cached and therefore was looked up every time. See JRA-18910.

            PdZ (Inactive) added a comment -

            This affects the JQL upgrade process of 4.0, vastly increasing the amount of time spent spinning as JIRA waits for the database to get back to it.

            PdZ (Inactive) added a comment - This affects the JQL upgrade process of 4.0, vastly increasing the amount of time spent spinning as JIRA waits for the database to get back to it.

            Furore Jira Admin added a comment -

            Yep... would be an improvement.
            Just giving it a bit more attention.
            It makes some actions quite slow..which is a pitty.
            Using MSSQL btw.

            Furore Jira Admin added a comment - Yep... would be an improvement. Just giving it a bit more attention. It makes some actions quite slow..which is a pitty. Using MSSQL btw.

              Unassigned Unassigned
              7ee5c68a815f Jeff Turner
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

                Created:
                Updated:
                Resolved: