1

I am trying to understand why there are so many rollback queries being issued by Jira and is equal to the number of commits. MySQL log reveals the following:

2021-04-29T14:55:58.364468Z     42059 Query     SET autocommit=0
2021-04-29T14:55:58.364857Z     42059 Query     select `AO_319474_QUEUE`.`CLAIMANT`, `AO_319474_QUEUE`.`CLAIMANT_TIME`, `AO_319474_QUEUE`.`CREATED_TIME`, `AO_319474_QUEUE`.`ID`, `AO_319474_QUEUE`.`MESSAGE_COUNT`, `AO_319474_QUEUE`.`MODIFIED_TIME`, `AO_319474_QUEUE`.`NAME`, `AO_319474_QUEUE`.`PURPOSE`, `AO_319474_QUEUE`.`TOPIC` from `AO_319474_QUEUE` `AO_319474_QUEUE` where `AO_319474_QUEUE`.`NAME` = 'servicedesk.base.internal.processing.master' limit 1
2021-04-29T14:55:58.365363Z     42059 Query     select count(*) from `AO_319474_MESSAGE` `AO_319474_MESSAGE` where `AO_319474_MESSAGE`.`QUEUE_ID` = 2012637 and (`AO_319474_MESSAGE`.`CLAIMANT` is null or `AO_319474_MESSAGE`.`CLAIMANT` is not null and (`AO_319474_MESSAGE`.`CLAIMANT_TIME` is null or `AO_319474_MESSAGE`.`CLAIMANT_TIME` < 1619707858365))
2021-04-29T14:55:58.365760Z     42059 Query     select `AO_319474_QUEUE_PROPERTY`.`ID`, `AO_319474_QUEUE_PROPERTY`.`LONG_VALUE`, `AO_319474_QUEUE_PROPERTY`.`NAME`, `AO_319474_QUEUE_PROPERTY`.`PROPERTY_TYPE`, `AO_319474_QUEUE_PROPERTY`.`QUEUE_ID`, `AO_319474_QUEUE_PROPERTY`.`STRING_VALUE` from `AO_319474_QUEUE_PROPERTY` `AO_319474_QUEUE_PROPERTY` where `AO_319474_QUEUE_PROPERTY`.`QUEUE_ID` = 2012637
2021-04-29T14:55:58.366033Z     42059 Query     commit
2021-04-29T14:55:58.366186Z     42059 Query     rollback
2021-04-29T14:55:58.366297Z     42059 Query     SET autocommit=1
2021-04-29T14:55:58.366568Z     42059 Query     SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
2021-04-29T14:55:58.366704Z     42059 Query     SET autocommit=0
2021-04-29T14:55:58.366947Z     42059 Query     update `AO_319474_QUEUE`
set `CLAIMANT_TIME` = 1619708158366 where `AO_319474_QUEUE`.`ID` = 2012637 and (`AO_319474_QUEUE`.`CLAIMANT` = 'claimant-20210376093440308EDT-c54a3a84-1cc5-4a62-ba11-ddcebde00928' and `AO_319474_QUEUE`.`CLAIMANT_TIME` >= 1619707858366)
2021-04-29T14:55:58.367378Z     42059 Query     select `AO_319474_MESSAGE`.`CLAIMANT`, `AO_319474_MESSAGE`.`CLAIMANT_TIME`, `AO_319474_MESSAGE`.`CLAIM_COUNT`, `AO_319474_MESSAGE`.`CONTENT_TYPE`, `AO_319474_MESSAGE`.`CREATED_TIME`, `AO_319474_MESSAGE`.`EXPIRY_TIME`, `AO_319474_MESSAGE`.`ID`, `AO_319474_MESSAGE`.`MSG_DATA`, `AO_319474_MESSAGE`.`MSG_ID`, `AO_319474_MESSAGE`.`MSG_LENGTH`, `AO_319474_MESSAGE`.`PRIORITY`, `AO_319474_MESSAGE`.`QUEUE_ID`, `AO_319474_MESSAGE`.`VERSION` from `AO_319474_MESSAGE` `AO_319474_MESSAGE` where `AO_319474_MESSAGE`.`QUEUE_ID` = 2012637 and (`AO_319474_MESSAGE`.`CLAIMANT` is null or `AO_319474_MESSAGE`.`CLAIMANT` is not null and (`AO_319474_MESSAGE`.`CLAIMANT_TIME` is null or `AO_319474_MESSAGE`.`CLAIMANT_TIME` < 1619707858367)) order by `AO_319474_MESSAGE`.`PRIORITY` desc, `AO_319474_MESSAGE`.`CREATED_TIME` asc, `AO_319474_MESSAGE`.`ID` asc limit 1
2021-04-29T14:55:58.367743Z     42059 Query     commit
2021-04-29T14:55:58.367882Z     42059 Query     rollback

So, I see it run explicit rollback for every transaction after a commit.

Can anyone please explain what could be the reasoning behind the above implementation for transactions? Apparently, it follows the following pattern:

set autocommit to true
set session transaction isoloation level to read-commited
set autocommit to false
run required queries
commit
rollback

As per my understanding, commited transaction can not be rolled back with a rollback command.

Because these transaction are happening from a single session, is issuing explicit rollback a safe way to end transaction and commit is to save the required changes transactions? (Analalgous to saving a file while editing after making important changes and also, the habit of closing a file with explicit command to not save unsaved changes)

Assuming the following is the behavior when JDBC MySQL connector is used:

  • If a commit is issued and made to database, no problem. (Rollback not required)
  • If a commit is issued, but not received at server due to a communication issue, then my understanding is that a lock is held on the records at server end until the session is closed or the communication reestablishes (as this is a read-commited isolation level and also as it is a TCP session and so the server can assume that it will received every command eventually until the session lives). If JDBC connector reconnects after a timeout of communication breakdown, the commit should reach eventually or does the JDBC connector assumes everything fails and try to redo by starting with a rollback.

So, is the transactions actually running ROLLBACK as first command and ending with a commit?

0
2

Answered in a comment by Bill Karwin:


Java apps are notorious for doing superfluous commands, because the JDBC driver can't be sure you haven't changed something since the last call. So it habitually re-defines the autocommit and the tx isolation level, etc. I'd guess the rollback in this case is a no-op that is being done "just in case you didn't commit" even though you did commit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.