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?
