Implicit and Explicit Transactions - Creating Savepoints

Introduction

The Implicit and Explicit Transactions - Creating Savepoints module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Analyzing the effect of implicit transaction on Transact-SQL statements
• Creating an explicit transaction on Transact-SQL statements
• Implementing savepoints in transactions

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

Exam Objectives

Three exam objectives are covered in this lab.

• Demonstrate the effect of implicit transaction mode on a set of Transact-SQL statements
• Demonstrate how to create an explicit transaction with a set of Transact-SQL statements
• Demonstrate how to create a savepoint in a transaction and illustrate how transactions can be rolled back to a savepoint

Exercise 1 - Analyzing Implicit Transaction on Transact-SQL Statements

A transaction refers to a sequence of operations on a database. These operations form a single unit and run in logical order, either manually or automatically. The transactions, as a whole unit, can either be executed successfully or may fail for certain reasons.

SQL Server supports two types of transaction mode:

1. Implicit transaction
2. Explicit transaction

An implicit transaction refers to the method in which the SQL Server database engine automatically starts a new transaction and ends it only when commit or rollback commands are specified in the statement. Thus, when the earlier transaction is committed or rolled back, the SQL Server Database Engine starts a new transaction, by default. This results in creating a series of transactions implicitly and continues to do so until the implicit transaction mode option is disabled.

In this exercise, you will learn to understand the execution of Transact-SQL statements, when implicit transaction mode is turned on.

Exercise 2 - Creating an Explicit Transaction on Transact-SQL Statements

An explicit transaction refers to the method in which the user starts the execution of the Transact-SQL statements by specifying the BEGIN TRANSACTION statement at the start of the transaction and COMMIT TRANSACTION or ROLLBACK TRANSACTION at the end of the transaction. When the execution of a transaction is unsuccessful, due to errors, then you typically end the transaction with a ROLLBACK TRANSACTION statement. When the execution of a transaction is successful, you typically end the transaction with a COMMIT TRANSACTION statement. You normally use explicit transaction while creating triggers and stored procedures.

In this exercise, you will learn to work with explicit transaction mode.

Exercise 3 - Implementing Savepoints for Rolling Back Transactions

A user can divide a big transaction into different sections and define savepoints for each section. When a savepoint is implemented along with rollback, the part of the transaction with savepoint can be alone rolled back. Savepoints can be used in locations where there are fewer chances of occurrence of errors. Thus, savepoints can reduce the time taken to complete the transaction by rolling back to the savepoint instead of rolling back to the beginning of the transaction.

In this exercise, you will learn to implement savepoints in Transact-SQL statements and use it with rollback command to complete the transaction.