Database Reference
In-Depth Information
As you can see, all log records are still in the Log Buffer. Now let's assume that transaction T2 wants to commit.
This action generates another log record and forces SQL Server to flush the content of the Log Buffer to the disk, as
shown in Figure
29-4
. SQL Server hardens all of the log records from the Log Buffer into the transaction log, regardless
of the transactions that generated them.
Figure 29-4.
Data modifications: Commit
Client applications would receive confirmation that the transaction is committed only after all log records are
hardened. Even though the data page (1:26912) is still dirty and has not been saved into the data file, hardened
log records on the disk have enough information to re-apply (redo) all of the changes done by the committed
T2 transaction. Thus it guarantees no data loss in case of a SQL Server crash.
■
Updating multiple rows from within a transaction allows SQL Server to buffer transaction log i/o operations,
saving multiple records at once. it is more efficient compared to multiple transactions, each updating a single row, and
forcing SQL Server to flush the log buffer on every commit operation.
Tip
nevertheless, remember locking behavior and avoid situations where the system holds a large number of exclusive
(X) locks for an extended period of time.
At this point, the system has log records hardened in transaction log even though the data pages in the data files
have yet to be updated. The next CHECKPOINT process saves dirty data pages and marks them as clean in the
Buffer Pool. CHECKPOINT also generates its own log record, as shown in Figure
29-5
.
