MySQL – Restoring a Database Using –single-transaction

this way is faster because

• Server does not need to perform fsync() multiple times during data import. fsync is the operation of submitting data into disk
• Extended insert enables sequential write to disk which means disk does not need to seek for a locations for every row.
• If the table has Auto_Increment values, Server can spare adequate values for the batch insertion at once rather than once for every insertion.
• The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead (Added By @Rick James in comments)

I know two side effects for this method.

1. undo log will get too large while performing this operation.
2. If an error happen during the operation, the entire import will be reverted (which might be an advantage in your case).

Non-committed transactions will be automatically rolled-back if the session is terminated.

You can easily test this by:

USE tempdb;

CREATE TABLE dbo.TestTran
(
    ID INT NOT NULL
        CONSTRAINT PK_TestTran
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

BEGIN TRAN
INSERT INTO dbo.TestTran DEFAULT VALUES;
SELECT * FROM dbo.TestTran;

Results will show a row inserted into dbo.TestTran.

Now, disconnect and reconnect the session.

--disconnect and reconnect

USE tempdb;

SELECT * FROM dbo.TestTran;

The results from the above SELECT statement will return no results since the non-committed transaction was automatically rolled back when the session was disconnected.

This is a primary tenet of all ACID-compliant database management systems.

Atomicity

A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic — all the work is done or none of it is.

Once you disconnect the session (or it completes without a COMMIT TRAN), the transaction will rollback to ensure none of the work has been done since the BEGIN TRAN statement.