Is there a significant cost of Beginning & Committing a MySQL Transaction even if no changes made to tables?

user_4475852 0
Viewed 595

This application is running on AWS EC2, using RDS/MySQL (5.7).

We have an audit script that is visiting every user in a MySQL user's table. \

For each user, we

  • Unconditionally start a transaction,
  • Possibly make some changes the user's record and to other tables.
  • Unconditionally commit the transaction.

Now, it's likely (and common) in step 2 that no changes were made to any table. The question arose during the code inspection as to the performance impact of Starting/Committing a transaction for many records when no changes were made.

I've read elsewhere that MySQL is optimized for commits, not rollback. But I've yet to find a discussion on the cost of starting/committing transactions when no work was done.

transactions mysql performance
3 Answers

Keep in mind that as soon as you read an InnoDB table, whether you explicitly "started a transaction" or not, InnoDB does similar work.

The alternative to starting a transaction is relying on autocommit. But autocommit doesn't mean no transaction happens. Autocommit means the transaction starts implicitly when your query touches an InnoDB table, and the transaction commits automatically as soon as the query is done. You can't run more than one statement, and you can't rollback, but otherwise it's the same as an explicit transaction.

You don't really save anything by trying to avoid starting a transaction.

Bill_Karwin_208600

There's no significant cost in short running transactions.

The minimal cost of transactions starts to happen when changes are actually made.

Rollbacks are only expensive if there is data to be changed during the rollback, otherwise its a fairly empty operation.

Committing (and probably rollback) where there are no changes shouldn't incur a penalty.

user_101951530

There is most definitely an overhead, and it can be quite significant. I recently worked with a client who had exactly this issue (lots of empty commits due to a bug in their ORM) and their database was burning through about 30% of CPU on empty commits.

Capture a day of slow log with long_query_time=0 (important!), put it through mysqldumpslow or pt-query-digest, and see if you have a query that is just COMMIT; using up a large amount of CPU.

user_18426770
Related