MySQL - Does using an explicit transaction affect query performance?

Question

In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and the relation between the two is also held in another table, C; so I created my update statement with two joins, like so :

UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'

Now I tested this statement on a dump of my target database, on a equivalent machine. Because I needed this dump for other purposes, I tested the thing using a transaction in order to rollback afterwards :

START TRANSACTION;
UPDATE [...]
ROLLBACK;

Which gave me pretty good performance (I think) for my update, about 17 seconds and 8 for the rollback.

My question is this: If I were to run this statement as is, not in a transaction, on my target database, would I obtain the same performance ? In other words, does using explicit transactions affect performance, and if yes, how ?

I tried to look the documentation on this but it is rather sparse on this topic.

Answer 1

1: Using autocommit to automatically commit each transaction:

SET autocommit = ON;
UPDATE ...

2: Explicitly turning a single statement into a transaction. This is 'identical' to #1.

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;

3: Multiple statements in a transaction:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

4: Don't use this; some day you will forget to COMMIT:

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;

5: Not advised. It can lead to slowdowns due to overflowing the "log":

BEGIN;
a million statements
COMMIT;

Notes:

• Each SQL has some small overhead (network transmission; parsing; etc)
• Each transaction as a noticeable overhead. In particular there is disk I/O to make it crash-safe. Hence #3 is faster than two copies of #1 or #2.
• See innodb_flush_log_at_trx_commit for a tradeoff between integrity and speed.

Advice:

• First priority: Do what the application needs -- this is, put the appropriate number of statements in each transaction.
• Lower priority: Throw together multiple statements when suitable, thereby amortizing the transaction overhead.
• In the case of INSERT, it is even better to have a single INSERT statement with multiple rows. Davit's Comment show a 9x speedup by putting multiple INSERTs in a single transaction. That is similar to the effect of the single batch insert.
• Use ROLLBACK only when appropriate. It is costly. This is because InnoDB optimizes for COMMIT and has to do extra effort to undo.
• For performance improvements, this whole discussion is pretty far down the checklist. More important things involve indexing and query formulation.
• Multi-thousand-row DELETE and UPDATE have a lot of overhead and stress on the log. It is better to break them up.

Answer 2

An incomplete answer but nonetheless, here it is.

Following suggestions from the comments, I made a few tests and obtained the following performance. The question's query modified 350 000 rows out of 4 million.

Using the explicit transaction mentionned above took about 24 seconds, whereas the query alone took a little under 20 seconds.

It turns out using an explicit transaction looks more expensive than a single query. I cannot understand why though, so I'll have to search on that.

Some details about my system variables:
innodb_buffer_pool_size and innodb_log_file_size were increased from the default by a factor 3. For this particular test's purpose I set query_cache_type to 0 (from 1). Performance of our application dropped quite dramatically afterwards, but since it's a test machine, it's fine.
Also, the MySQL server is managed through systemd, where we modified the LimitNOFILE param to 8192. This file's other parameters are I think irrelevant.

About the query, EXPLAIN EXTENDED showed that only req and eq_ref join types were used.
In the end, I will go for the single query on my target database as it is faster.