Microsoft® SQL Server® 2012 Bible

1090

Part VIII: Performance Tuning and Optimization

Database Beginning State

Before the transaction begins, the database is in a consistent state. All indexes are com-

plete and point to the correct row. The data meets all the enforced rules for data integrity.

Some data pages are likely already cached in the buffer pool. Additional data pages or index

pages are copied into memory as needed. Following are the steps of a transaction.

So the fi rst step in a transaction is: The database is in a consistent state.

Data-Modification Command

The transaction is initiated by a submitted batch, as shown in Figure 47-10.

The second step in a transaction is: The code issues a begin transaction command.

Even when the DML command is a standalone command without a begin transaction

and commit transaction, it is still handled as a transaction.

FIGURE 47-10

The SQL DML commands are performed in memory as part of a transaction.

Data Pages

In

RAM

T Update

Data File

T-Log

6) Confirm

5) Write

Ahead

1) Begin in

Consistent State

4) Write to

Data Page

Update Confirmed

2) Begin Tran

3) Update

SQL Update

Delete

Insert

The third step in a transaction is: The code issues a single DML insert, update, or

delete command, or a series of them.

To give you an example of the transaction log in action, the following code initiates a

transaction and then submits two update commands:

USE AdventureWorks2012

BEGIN TRANSACTION

UPDATE Production.Product

SET Name = 'Transaction Log Test A',

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

http://www.it-ebooks.info