Microsoft® SQL Server® 2012 Bible

1091

Chapter 47: Managing Transactions, Locking, and Blocking

47

SellEndDate = '12/31/2003'

WHERE ProductID = '1001'

UPDATE Production.Product

SET Name = 'Transaction Log Test B',

SellEndDate = '4/1/2003'

WHERE ProductID = '1002'

Notice that the transaction has not yet been committed.

This brings you to the fourth step in a transaction: The query execution plan is either gen-

erated or pulled from the plan cache. Any required locks are applied, and the data modi-

fi cations, including index updates, page splits, and any other required system operation,

are performed in memory. At this point the data pages in memory are different than those

stored in the data fi le.

The following section continues the chronological walk through the process.

Transaction Log Recorded

The most important aspect of the transaction log is that all data modifi cations are written

to it and confi rmed prior to being written to the data fi le, as shown in Figure 47-10.

The fi fth step in a transaction is: The data modifi cations are written to the transaction log.

The sixth step in a transaction is: The transaction-log DML entries are confi rmed. This

ensures that the log entries are written to the transaction log.

Transaction Commit

When the sequence of tasks is complete, the commit transaction closes the transaction.

Even this task is written to the transaction log, as shown in Figure 47-11.

FIGURE 47-11

The commit transaction command launches another insert into the transaction log.

Data Pages

In

RAM

T Commit

Data File

T-Log

Delete

Insert

Commit

9) Confirm

8) Write

Ahead

1) Begin in

Consistent State

4) Write to

Data Page

2) Begin Tran

3) Update

7)Commit Tran

SQL Update

c47.indd 1091c47.indd 1091 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM

http://www.it-ebooks.info