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.

Hedayatullah Sarwary
  • 163
  • 1
  • 1
  • 8
nulll
  • 141
  • 5
  • Did you issue a START TRANSACTION? Is there anything in the logs? Do you have the proper PRIVs? Can we see the query? – Dave Stokes Feb 02 '21 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 02 '21 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? – geertjanvdk Feb 02 '21 at 17:11
  • Is this a standalone server? Or part of some kind of cluster? – Rick James Feb 02 '21 at 19:24
  • Soon after it happens, run `SHOW ENGINE=InnoDB STATUS;` and post the results here. – Rick James Feb 02 '21 at 19:26
  • Also check the return code from the statement. – Dave Stokes Feb 04 '21 at 15:27

2 Answers2

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.

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • Hi, I am reading that [deadlocks can cause implicit rollbacks](https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handling.html) – nulll Jul 22 '21 at 09:48
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.

nulll
  • 141
  • 5