Lloyd Banks Lloyd Banks - 4 months ago 16
MySQL Question

Performance Difference Between Explicitly Committing vs Auto-Committing Transactions in MySQL and SQL Server

In MySQL, I can nest a series of transactions atomically with the following:

START TRANSACTIONS;

UPDATE balance
SET current_balance = 40
WHERE id = 1;
UPDATE balance
SET current_balance = 50
WHERE id = 2;

COMMIT;


I can do the same thing in SQL Server with

BEGIN TRANSACTION

UPDATE balance
SET current_balance = 40
WHERE id = 1;
UPDATE balance
SET current_balance = 50
WHERE id = 2;

COMMIT TRANSACTION


Is there a difference in how fast queries are executed in the above vs not nesting (no START / BEGIN and COMMIT commands) the statements in their respective database systems?

Answer

Using explicit transactions should be faster especially if you do a lot of inserts or updates.

  • For implicit transaction (autocommit = 1 in MySQL), database engine have to write transaction log to disk (or disk cache for that matter) after each statement.
  • For explicit transaction (autocommit = 0 in MySQL), database engine can potentially have only one input/output operation for writing transaction log for all statements used in a transaction.

IMHO it won't be noticeable in just two update statements though.

But transactions mainly are not about speed but rather consistency and atomicity of the whole operation.


BTW in MySQL you can simultaneously update two records without explicitly starting a transaction in a following way

UPDATE balance b1 JOIN balance b2
    ON b1.id = 1 
   AND b2.id = 2
   SET b1.current_balance = 40,
       b2.current_balance = 50

or both in MySQL and SQL Server

UPDATE balance 
   SET CASE id WHEN 1 THEN 40 
               WHEN 2 THEN 50
       END
 WHERE id IN(40, 50)

Further reading: