Transaction Control Language in SQL Server
In this article, I am going to discuss Transaction Control Language in SQL Server with some examples. As part of this article, we are going to discuss the following pointers.
- Understanding Auto Commit, Implicit, and Explicit Transaction Mode in SQL Server.
- What is the Transaction Control Language?
- Why we need Transaction Control Language in SQL Server?
- What is the rule of Transaction?
- How to implement Transaction in SQL Server?
- Example to understand Transaction Control Language.
Please read Transaction Management in SQL Server article before proceeding to this article where we discuss the following things
- What is Transaction Management?
- Why we need Transaction Management?
- What is a Transaction?
- How to implement Transaction Management?
- Finally, we discussed some examples.
Before understanding the Transaction Control Language in SQL Server, let us first discuss the different type of transactions supported by SQL Server. The SQL Server transactions are classified into three types, they are as follows
- Auto Commit Transaction Mode (default)
- Implicit Transaction Mode
- Explicit Transaction Mode
Auto Commit Transaction Mode:
This is the default mode of transaction. In this mode, each SQL statement is treated as a separate transaction.
In Auto Commit Transaction Mode, the programmers are not responsible for either beginning a transaction (i.e. Begin Transaction) or ending a transaction (i.e. Commit or Roll Back). Whenever we perform or execute any DML statement, the SQL Server will only begin the transaction and end the transaction with a Commit or Rollback i.e. if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back. So the programmer does not have any control over them.
Implicit Transaction Mode:
In Implicit mode of transaction, the SQL Server is responsible for beginning a transaction implicitly before the execution of any DML statement and the developers are responsible to end the transaction with a commit or rollback. Once the transaction is ended, automatically a new transaction will start. That means, in the case of implicit mode, a new transaction starts automatically once the current transaction is committed or rolled back.
The Implicit mode for transactions in SQL Server is set to ON or OFF by using the command SET IMPLICIT_TRANSACTIONS to ON or OFF. If the implicit mode is set to ON, then a new transaction is implicitly started whenever any particular SQL statement is executed.
The particular SQL statement includes INSERT, SELECT, DELETE, AND UPDATE SQL statements. It is called implicit mode because of the fact that once IMPLICIT_TRANSACTIONS is set to ON then the transactions are created implicitly for certain SQL statements without having to say we want to create a transaction each time.
Explicit Transaction Mode:
In the Explicit mode of transaction, the programmer is only responsible for beginning the transaction as well as for ending the transaction. We can also say that the transactions that have a START and END explicitly written by the programmer are called as an explicit transaction.
In the explicit mode of transaction, every transaction starts with a BEGIN TRANSACTION statement and ends with either a ROLLBACK TRANSACTION statement (when the transaction does not complete successfully) or a COMMIT TRANSACTION statement (when the transaction completes successfully). The explicit mode is most commonly used in triggers, stored procedures and application programs.
What is the Transaction Control Language?
The Transaction Control Language (TCL) is a computer language and a subset of SQL which is used to control the transactional processing in a database. A transaction is a logical unit of work that comprises one or more SQL statements, usually, a group of Data Manipulation Language (DML) statements.
Why we need Transaction Control Language in SQL Server?
We need the Transaction Control Language in SQL Server to safeguard enterprise data (to make enterprise data consistent and to achieve the data integrity).
A transaction is the propagation of one or more changes to the database. For example, if we are creating a record or updating a record or deleting a record from the table, then we are performing the transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
What is the rule of Transaction?
The rule of transaction tells that either all the statements in the transaction should be executed successfully or none of those statements to be executed.
How to Implement a Transaction in SQL Server?
The following commands are provided by TCL (Transaction Control Language) to control transactions:
Transactional Control Language commands are only used with the DML statements such as INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.
Let’s understand the Transaction Control Language with an example:
Please use below SQL Script to create and populate the CurrentAddress and PermanentAddress tables with test data.
CREATE TABLE CurrentAddress ( AddressID INT PRIMARY KEY, EmployeeID INT, HouseNumber VARCHAR(50), StreetAddress VARCHAR(50), City VARCHAR(50), PostalCode VARCHAR(50) ) GO INSERT INTO CurrentAddress VALUES (1, 1001, '#1001', 'IRC Village', 'BBSR', '755019') CREATE TABLE PermanentAddress ( AddressID INT PRIMARY KEY, EmployeeID INT, HouseNumber VARCHAR(50), StreetAddress VARCHAR(50), City VARCHAR(10), PostalCode VARCHAR(50) ) INSERT INTO PermanentAddress VALUES (1, 1001, '#10', 'IRC Village', 'BBSR', '755019') GO
An employee with the EmployeeID 1001 has the same address as his current and permanent address. His city name is stored as BBSR instead of Bhubaneswar. The following stored procedure ‘spUpdateAddress’ updates the current and permanent addresses. Both the UPDATE statements are wrapped between the BEGIN TRANSACTION and COMMIT TRANSACTION which in turn is wrapped between the BEGIN TRY and END TRY block.
So if both the UPDATE statements are succeeded without any errors then the transaction is committed and the data is updated to the database. But, if there is any error then the control is immediately transferred to the catch block.
The ROLLBACK TRANSACTION statement in the CATCH block rolls back the transaction and any data that was written to the database by the commands is backed out.
CREATE PROC spUpdateAddress AS BEGIN BEGIN TRY BEGIN TRANSACTION UPDATE CurrentAddress SET City = 'Bhubaneswar' WHERE AddressId = 1 and EmployeeID = 1001 UPDATE PermanentAddress SET City = 'Bhubaneswar' WHERE AddressId = 1 and EmployeeID = 1001 COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH END
Intentionally we make the second UPDATE statement fail. The CITY column length in PermanentAddress table is 10. The second UPDATE statement fails because the value for CITY column is more than 10 characters.
Now if we execute the spUpdateAddress stored procedure the first UPDATE statement succeeds but the second UPDATE statement fails. As soon as the second UPDATE statement fails the control is immediately transferred to the CATCH block. The CATCH block rolls the transaction back. So the change made by the first UPDATE statement is undone.
In the next article, I am going to discuss ACID Properties in SQL Server with examples. Here, in this article, I try to explain the Transaction Control Language in SQL Server step by step with some examples.