Does commit write data to disk immediately

COMMIT does not mean that SQL Server immediately writes modified data pages to the MDF file. COMMIT guarantees that all log records associated with the transaction, including the COMMIT record, are synchronously flushed to the transaction log file in accordance with Write-Ahead Logging. The modified data pages typically remain in the Buffer Pool as dirty pages and may be written later by Checkpoint or Lazy Writer. This design allows SQL Server to achieve both high performance and full ACID durability. If a crash occurs after COMMIT but before the data pages are written to disk, SQL Server uses the transaction log during the Redo phase of recovery to reconstruct the committed changes. This separation of log persistence from data page persistence is one of the fundamental architectural principles of SQL Server’s storage engine.

When a COMMIT occurs, SQL Server guarantees that the transaction log records, including the COMMIT record, are flushed to the transaction log file (.ldf) on disk. This is required by the Write-Ahead Logging (WAL) protocol. The modified data pages may still reside in memory (Buffer Pool) and can be written to the data file (.mdf/.ndf) later by Checkpoint, Lazy Writer, or other background processes. Durability is guaranteed because SQL Server can always recover committed changes from the transaction log even if the data pages were never written to disk before a crash.

When we run below code

BEGIN TRANSACTION;  UPDATE Account SET Balance = Balance - 10000 WHERE AccountNumber = 'SB100001'; COMMIT;

Internally it perform the below steps

Ø  Update Data Page in Memory

Ø  Generate Log Records

Ø  Write Log Records to LDF

Ø  Write COMMIT Record to LDF

Ø  Acknowledge COMMIT Success

Ø  Data Page May Still Be In Memory

COMMIT = Log Flush

NOT COMMIT = Data Page Flush

Internal Working Step-by-Step

Step 1: Transaction Begins

BEGIN TRANSACTION;

Transaction Manager creates Transaction ID.

Step 2: Data Modified

UPDATE Account

SET Balance = Balance - 10000

WHERE AccountNumber = 'SB100001';

Reads page into memory Buffer Pool. Keep before value Salary = 50000 and after value Salary =  60000. Now Page becomes Dirty Page.

It means value of Memory != Disk.

Step 3: Log Records Generated

Transaction Log receives LOP_BEGIN_XACT, LOP_MODIFY_ROW.

Step 4: COMMIT Executed

COMMIT;

SQL Server writes LOP_COMMIT_XACT to transaction log.

Before SQL Server returns command(s) completed successfully. It must ensure transaction Log is physically written to disk. This is WAL.