Avoid generating useless SQL rollback and commit queries

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.