Scripts and Transactions in Transact-SQL - Transaction Processing Architecture
(Page 4 of 5 )
An explanation of how transactions are implemented in Microsoft SQL Server will give you some insight into many processes.
Every change to the database is recorded in a transaction log before it is written to the appropriate tables. Transaction logs are implemented in separate files (or sets of files) with the extension .ldf. All modifications are written to this file chronologically. The records in this transaction log can later be used to roll back the transaction (thus providing atomicity) or to commit the changes to the database (thus providing durability). Two types of records can be stored in transaction logs:
- Logical operations performed (for instance, Insert, Update, Delete, and start of transaction)
- Before and after images of the changed data (that is, copies of data before and after the change is made)
NOTE
The transaction log does not record queries that are executed against the database (since they do not modify its content).
The transaction log mechanism helps to resolve data integrity issues such as:
If a client application loses its connection before a transaction is finished, SQL Server will detect a problem and roll back changes to ensure consistency.
If the machine loses power during processing, SQL Server will recover the database when services are restored. All transactions that were recorded in the transaction log in an undivided manner (that is, as part of a complete transaction set) are rolled forward (written to data tables) as if nothing unusual has happened. All transactions that were not completed before the problem occurred are rolled back (deleted) from the database.
NOTE
The transaction log also plays an important role in the implementation of backups in SQL Server. When a user starts a full backup, SQL Server records a complete snapshot of the data tables in backup files. At that point, SQL Server marks the current position in the transaction log and continues to record all changes to the database in the transaction log. Transactions logged during the process are also recorded as part of the full backup. When the backup is complete, SQL Server makes another mark in the transaction log. At the time of the next backup, a transaction log backup will suffice. To restore the database, an administrator first uses the full backup and then one or more transaction log backups that have been run since the full backup. SQL Server runs through the transaction log and applies changes to the data tables.
Next: Nested Transactions >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Buy this book now.
|
|
/ 4 
