Mysql – What could be the reason for running rollback after a commit

jdbcMySQLrollbacktransaction

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?

Best Answer

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.

Related Question