Conditions that can make a MySQL COMMIT query fail?

Question

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.

Answer 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

• Mar 06, 2018 : Can you rollback a query in state 'committing alter table to storage engine'
• Aug 21, 2015 : Transactional DDL workflow for MySQL
• Feb 12, 2014 : row locking within ACID transaction innodb
• Mar 15, 2013 : MySQL backup InnoDB

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.

Answer 2

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.