Impact of Transactions on DML Statements

Introduction

The Impact of Transactions on DML Statements module provides you with the instructions and devices to develop your hands on skills in the following topic.

• Demonstrating impact of transactions on DML statements

Lab time: It will take approximately 20 minutes to complete this lab.

Exam Objectives

One exam objective is covered in this lab.

• Demonstrate how transactions impact the results of DML statements

Exercise 1 - Demonstrating Impact of Transaction on DML Statements

A transaction is a set of SQL statements that performs one or more tasks in a logical order. Execution of transaction statements results in the data in the database being changed. Consider a scenario where you are inserting or updating one or more records in a table. The set of statements used to perform this task is considered to be a transaction. The transaction inserts or updates records in a table, thereby, permanently changing the records in the database. The syntax to include a set of statements as a part of a transaction is as follows:

BEGIN {TRAN | TRANSACTION}

A transaction ends with the following statements:

• ROLLBACK: It erases all the data modifications from the beginning of the transaction to the end or up to the last saved statement in the transaction.
• COMMIT: It makes all the data modifications from the beginning of the transaction to the end, a permanent part of the database.

You can place multiple transaction statements in a nested format with the help of @@TRANCOUNT system function. The @@TRANCOUNT function records the nesting level of the current transaction. Each BEGIN TRANSACTION increments @@TRANCOUNT function by one. Each COMMIT TRANSACTION decrements the count by one.

You can also include transactions in a TRY...CATCH block. The TRY...CATCH block has two parts, namely, a TRY block and a CATCH block. The transaction statements that might cause error conditions are placed inside the TRY block. The error-processing code is placed inside the CATCH block. When SQL Server detects an error condition inside the TRY block, it transfers the control to the first statement inside the associated CATCH block. After all the statements inside the CATCH block are executed, the system transfers the control to the first statement after the END CATCH statement. The syntax to use the TRY...CATCH block is as follows:

BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ]

In this exercise, you will use the transaction statements to observe the impact of transactions on DML statements.