2

I have an application where I run an explicit COMMIT query on a MySQL standalone server

COMMIT;

The query works as expected in the vast majority of cases, but sometimes the query seems successful but at the same time seems not to commit the transaction to the DB.

So I was digging into the possibility that a COMMIT query could fail. I found this similar question: https://stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how

but on the official MySQL doc never mentions that a COMMIT can fail.

I want to understand in which conditions a commit query can fail and how to reproduce it, possibly supported by official doc pages.

6
  • Did you issue a START TRANSACTION? Is there anything in the logs? Do you have the proper PRIVs? Can we see the query? Feb 2, 2021 at 16:41
  • Yes, the transaction is wrapped with the START TANSACTION query. I can't access mysql's logs. The event described is not systematic, it happens only sometimes
    – nulll
    Feb 2, 2021 at 16:47
  • If COMMIT fails, it will just like any other statements, emit an error. If your application is throwing away the error, it will not show what the problem is/was. MySQL logs are OK, but your application's log is probably more important here. Also, maybe your transaction succeeds, not changing anything? Feb 2, 2021 at 17:11
  • Is this a standalone server? Or part of some kind of cluster?
    – Rick James
    Feb 2, 2021 at 19:24
  • Soon after it happens, run SHOW ENGINE=InnoDB STATUS; and post the results here.
    – Rick James
    Feb 2, 2021 at 19:26

2 Answers 2

1

This depends entirely on how you setup the transaction.

If you have START TRANSACTION and COMMIT; surrounding a series of INSERT, UPDATE, and DELETE queries, there are some commands that trigger an implicit commit:

  • ALTER TABLE
  • CREATE INDEX
  • DROP DATABASE
  • DROP INDEX
  • DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE
  • LOCK TABLES
  • UNLOCK TABLES
  • SET AUTOCOMMIT = 1
  • BEGIN (can break another BEGIN or START TRANSACTION)
  • START TRANSACTION (can break another START TRANSACTION or BEGIN)

I have mentioned this before in the DBA StackExchange

You can see the latest list of commands that trigger implicit commits in the MySQL 5.7 Docs. Why consider implicit commits ? Data may commit out of context with other data too soon or too late.

Aside from these commands, terminating a DB Connection will implicitly rollback a transaction. You should monitor the status variables Aborted_connects and Aborted_clients to see if DB Connections are failing on entry or in session.

1
0

The problem was not on the COMMIT query, the problem was that during that transaction an implicit rollback happens.

As the documentation says, this can happen if a deadlock occures during the transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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