Understanding Transactions

When updating or inserting a record into a database, the record is first allocated in buffer memory, and the buffer manager guarantees that the transaction log is written before the changes to the database file are written. It does this by keeping track of a log position using a log sequence number (LSN).

                   At certain intervals SQL Server will issue a checkpoint in the transaction log that will issue a write from the transaction log to the data file. This process of working with the transaction log and recording actions in the transaction log before applying them to the actual data files allows SQL Server to recover from failures in case of an unexpected shutdown; this is known as autorecovery.

                   The autorecovery process will check the database to see what the last-issued checkpoint and written LSN was and will then write all committed records from the transaction logs that were not recorded yet in the data file to the data file. This process is a rollforward.

Autocommit By default SQL Server automatically commits a transaction to the database and every transaction is handled as a single process. When you perform a query that issues a DML statement (insert/update/delete), SQL Server will automatically commit the transaction by recording an LSN in the transaction log. Because this process occurs without any explicit request from you to confirm the action.

When working with transactions SQL Server supports two types of transactions: implicit and explicit. When using implicit transactions you will need to commit every statement to the database after executed. The difference between an implicit transaction and autocommitted transaction is that you still need to COMMIT the transaction by the end of the statement.

                   In order to group transactions together as one single unit, you will have to use explicit transactions. An explicit transaction occurs when the statement you issue is preceded by a BEGIN TRAN or BEGIN TRANSACTION statement. When working with explicit transactions, you identify the transactions by using a BEGIN TRANSACTION and a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.