CODEWITHSUNDEEP
mysqlsql-server
Lloyd Banks
Lloyd Banks

Reputation: 36669

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?

Upvotes: 0

Views: 3185

Answers (2)

peterm
peterm

Reputation: 92805

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:

Upvotes: 2

M.Ali
M.Ali

Reputation: 69554

By Default SQL Server Guarantees Atomicity for Sql Statements but to enforce Atomicity on Sql Batches(More then one sql statement) Transactions are used.

Explicit Transaction are not used to gain any performance but to Control Atomicity among different Operations/Sql Statements wrapped inside a Transaction, So we have the control of multiple operations done on the target data to COMMIT them all or ROLLBACK all, and data is not left in half done state.

Yes Explicit Transaction can have impact on the performance, as resources being used inside an Explicit transaction are locked by the transaction and not available for other users. Also the behaviour of transaction is different under different Transaction Isolation Levels.

Upvotes: 1

Related Questions