Think that you are transferring money from one bank account to another. You would not want your money to be debited from your account without reaching the receiver’s account. That’s where Transaction comes into the frame. In SQL Server, a transaction is a group of database operations that are arranged in sequence and when needed, it executes all of them at once as a single unit.
In this article, we are going to see how transactions maintain data consistency and data integrity by using control commands like COMMIT and ROLLBACK.
Table of Contents
Transactions in SQL Server
In SQL Server, transactions are groups of one or more commands that are kept together and executed once as a single unit. If all the commands run successfully, then the changes are saved to the database. If there is any failure in command, then the changes will be undone.
Transactions make sure about the data integrity and consistency in the database.
Syntax of Transaction
BEGIN TRANSACTION;
-- Set of SQL Commands that you want to execute
-- If all of your commands run successfully, then commit the transaction
COMMIT;
-- If there is any error, then rollback the transaction
ROLLBACK;
Transaction Properties
Transaction properties in SQL are commonly known as ACID properties, which stand for atomicity, consistency, isolation, and durability. Let’s discuss these properties one by one.
- Atomicity: It makes sure that all commands that are part of the transaction must be executed successfully. If not, then the whole transaction should be canceled and rolled back to the previous state using the ROLLBACK command.
- Consistency: It makes sure that once the transaction is committed successfully then, only the database should change and save the state. It allows you to protect your data from crashes.
- Isolation: It makes sure that each transaction is working independently and isolated from other transactions. It also checks whether the commands are transparent to each other or not.
- Durability: It makes sure that once the transaction is committed, even if the database crashes or fails, it should still be saved in the database.
Transaction Isolation Levels in SQL Server
SQL Server does not always follow lock hints when dealing with system metadata. Metadata contains system views, built-in functions, and stored procedures that give information about the databases.
Important Points to Know:
- When accessing metadata, SQL Server does not go beyond the READ COMMITTED isolation level.
- If a transaction runs with SERIALIZABLE or anything that is more strict isolation level, all metadata queries are done with READ COMMITTED.
- Accessing metadata using SNAPSHOT ISOLATION can be problematic as the metadata is not versioned.
| Isolation Level | Supported? | Guaranteed? |
| READ UNCOMMITTED | No | Not guaranteed |
| READ COMMITTED | Yes | Yes |
| REPEATABLE READ | No | No |
| SNAPSHOT ISOLATION | No | No |
| SERIALIZABLE | No | No |
What Might Fail Under SNAPSHOT ISOLATION?
- System catalogue views
- Compatibility views
- Information schema views
- Metadata-related built-in functions
- sp_help procedures
- SQL Server Native Client catalog procedures
- Dynamic management views and functions
Types of Transactions in SQL Server
There are three common types of transactions in SQL Server. Let’s look at each kind of transaction one by one.
- Explicit Transactions
This type of transaction starts manually by using the BEGIN TRANSACTION command. Once completed, then we can COMMIT TRANSACTION. If the commands give some errors, then we can use ROLLBACK TRANSACTION to get back to the last saved process.
- Implicit Transactions
This type of transaction in SQL Server automatically starts a transaction for each statement. To implement it, we use SET IMPLICIT_TRANSACTIONS ON.
- Autocommit Transactions
This is the default type of transaction, in which SQL Server treats each command as a transaction and automatically commits the changes if there are no errors.
Transaction Control
Below are the mentioned commands that are used to control transactions:
- BEGIN TRANSACTION: This command shows where each transaction is starting.
- COMMIT: This command shows where we need to save the changes permanently.
- ROLLBACK: This command allows us to go back to the last saved transaction state if any command fails.
- SAVEPOINT: This type of command allows us to create save checkpoints where we can roll back to that point instead of rolling back entire transactions.
- RELEASE SAVEPOINT: This command is used to remove that SAVEPOINT that is already existing.
- SET TRANSACTION: This command assigns a name to a transaction, allowing it to be classified as read-only or read/write, or to allocate it to a specific rollback segment.
Examples of Transactions in SQL Server
Let’s create a table named INTELLIPAAT with columns ID, Name, Age and Salary. We will perform implicit, explicit, and automatic transactions one by one in SQL Server.
CREATE TABLE INTELLIPAAT (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT,
Salary DECIMAL(10, 2)
);
1. Implicit Transaction
Here, we just add a row in a table, as implicit transactions are automatically started when we execute statements like INSERT, UPDATE, and DELETE. They don’t require BEGIN TRANSACTION or COMMIT.
INSERT INTO INTELLIPAAT (ID, Name, Age, Salary)
VALUES (1, 'John Doe', 30, 50000);
SELECT * FROM INTELLIPAAT;
2. Explicit Transaction
As we know, it is a manually controlled transaction, so we need to use commands like BEGIN TRANSACTION, COMMIT and ROLLBACK.
BEGIN TRANSACTION;
INSERT INTO INTELLIPAAT (ID, Name, Age, Salary)
VALUES (2, 'Jane Smith', 25, 60000);
SELECT * FROM INTELLIPAAT;
COMMIT TRANSACTION;
SELECT * FROM INTELLIPAAT;
3. Automatic Transaction with ROLLBACK
Automatic transactions can be rolled back if we encounter any error or if explicitly instructed. Here, you will notice that (3, ‘Alice Brown’, 28, 55000) is not added to the table as we have implemented ROLLBACK.
BEGIN TRANSACTION;
INSERT INTO INTELLIPAAT (ID, Name, Age, Salary)
VALUES (3, 'Alice Brown', 28, 55000);
ROLLBACK TRANSACTION;
SELECT * FROM INTELLIPAAT;
4. Transaction with Error Handling
We can use TRY and CATCH blocks to handle the errors. Whenever we get an error, it will automatically roll back to the last saved checkpoint.
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO INTELLIPAAT (ID, Name, Age, Salary)
VALUES (4, 'Bob Johnson', 35, 70000);
INSERT INTO INTELLIPAAT (ID, Name, Age, Salary)
VALUES (1, 'Duplicate Entry', 40, 80000);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Error occurred: ' + CAST(ERROR_MESSAGE() AS NVARCHAR(4000));
SELECT * FROM INTELLIPAAT;
Deadlocks Caused By Transaction
A deadlock occurs when two transactions block each other, waiting for a resource that the other holds, thus rendering both unable to progress.
Understanding deadlock with an example:
- Let’s assume transaction A locks Row 1 and waits for Row 2.
- Simultaneously, Transaction B locks Row 2 and waits for Row 1.
This is an example of an inter-dependency. Both are waiting for each other which causes a deadlock.
How to Avoid Deadlocks
We can use TRY…CATCH with ROLLBACK to handle deadlocks, or we can use WITH (NOLOCK) (only if dirty reads are acceptable).
BEGIN TRANSACTION;
BEGIN TRY
UPDATE INTELLIPAAT SET Salary = Salary + 5000 WHERE ID = 1;
UPDATE INTELLIPAAT SET Salary = Salary - 5000 WHERE ID = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back due to deadlock: ' + ERROR_MESSAGE();
END CATCH;
Conclusion
The TRANSACTION statements are one of the most helpful statements, allowing us to avoid any partial updates to the database tables where we also need to maintain the ACID property.
By implementing transactions carefully in our database, we can maintain data consistency and integration. By using transactions properly, we can boost the performance and reliability of our databases.