In this article, we will talk about fundamental details of the transactions in SQL Server.
Introduction
A transaction is the logical work unit that performs a single activity or multiple activities in a database. Transactions may consist of a single read, write, delete, or update operations or a combination of these. Suppose that, when we want to withdraw money from the ATM, the ATM application will achieve this operation in three steps. As a first step, the application will check the balance of the account, and then it will deduct the money from the source account. Along with these two processes, it will keep the log of this money withdrawing activity. The following image basically illustrates the working principle of the transactions in the relational database systems.
The main idea of transactions is that when each of the statements returns an error, the entire modifications rollback to provide data integrity. On the other hand, if all statements are completed successfully the data modifications will become permanent on the database. As a result, if we experience any power outage or other problems during the withdrawal of money from an ATM, transactions guarantee our balance consistency. It would be the best method to perform all these steps through a transaction because the four main properties of the transactions enable all operations more accurate and consistent. All these properties are known as the ACID (atomicity, consistency, isolation, durability) in the relational database systems with the first letter of their names.
- Atomicity: The entire of the operations that are included by the transaction performed successfully. Otherwise, all operations are canceled at the point of the failure and all the previous operations are rolled back
- Consistency: This property ensures that all the data will be consistent after a transaction is completed according to the defined rules, constraints, cascades, and triggers
- Isolation: All transactions are isolated from other transactions
- Durable: The modification of the commited transactions becomes persist in the database
Pre-Requirements
In this article, we will create a sample table through the following query and will populate some sample data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE Person ( PersonID int PRIMARY KEY IDENTITY(1,1), LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255), Age INT ) GO INSERT INTO Person VALUES('Hayes', 'Corey','123 Wern Ddu Lane','LUSTLEIGH',23) INSERT INTO Person VALUES('Macdonald','Charlie','23 Peachfield Road','CEFN EINION',45) INSERT INTO Person VALUES('Frost','Emma','85 Kingsway North','HOLTON',26) INSERT INTO Person VALUES('Thomas', 'Tom','59 Dover Road', 'WESTER GRUINARDS',51) INSERT INTO Person VALUES('Baxter','Cameron','106 Newmarket Road','HAWTHORPE',46) INSERT INTO Person VALUES('Townsend','Imogen ','100 Shannon Way','CHIPPENHAM',20) INSERT INTO Person VALUES('Preston','Taylor','14 Pendwyallt Road','BURTON',19) INSERT INTO Person VALUES('Townsend','Imogen ','100 Shannon Way','CHIPPENHAM',18) INSERT INTO Person VALUES('Khan','Jacob','72 Ballifeary Road','BANCFFOSFELEN',11) |
Modes of the Transactions in SQL Server
SQL Server can operate 3 different transactions modes and these are:
- Autocommit Transaction mode is the default transaction for the SQL Server. In this mode, each T-SQL statement is evaluated as a transaction and they are committed or rolled back according to their results. The successful statements are committed and the failed statements are rolled back immediately
- Implicit transaction mode enables to SQL Server to start an implicit transaction for every DML statement but we need to use the commit or rolled back commands explicitly at the end of the statements
- Explicit transaction mode provides to define a transaction exactly with the starting and ending points of the transaction
How to define an Implicit Transaction in SQL Server
In order to define an implicit transaction, we need to enable the IMPLICIT_TRANSACTIONS option. The following query illustrates an example of an implicit transaction.
- Tip: @@TRANCOUNT function returns the number of BEGIN TRANSACTION statements in the current session and we can use this function to count the open local transaction numbers in the examples
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SET IMPLICIT_TRANSACTIONS ON UPDATE Person SET Lastname = 'Sawyer', Firstname = 'Tom' WHERE PersonID = 2 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTransactions COMMIT TRAN SELECT @@TRANCOUNT AS OpenTransactions |
The COMMIT TRANSACTION statement applies the data changes to the database and the changed data will become permanent.
How to define an Explicit Transaction in SQL Server
In order to define an explicit transaction, we start to use the BEGIN TRANSACTION command because this statement identifies the starting point of the explicit transaction. It has the following syntax:
|
1 2 |
BEGIN TRANSACTION [ {transaction_name | @tran_name_variable } [WITH MARK ['description']]] |
- transaction_name option is used to assign a specific name to transactions
- @trans_var option is a user-defined variable that is used to hold the transaction name
- WITH MARK option enable to mark a particular transaction in the log file
After defining an explicit transaction through the BEGIN TRANSACTION command, the related resources acquired a lock depending on the isolation level of the transaction. For this reason as possible to use the shortest transaction will help to reduce lock issues. The following statement starts a transaction and then it will change the name of a particular row in the Person table.
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN UPDATE Person SET Lastname = 'Lucky', Firstname = 'Luke' WHERE PersonID = 1 SELECT @@TRANCOUNT AS OpenTransactions |
As we stated in the previous section COMMIT TRAN statement applies the data changes to the database and the changed data will become permanent. Now let’s complete the open transaction with a COMMIT TRAN statement.
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN UPDATE Person SET Lastname = 'Lucky', Firstname = 'Luke' WHERE PersonID = 1 SELECT @@TRANCOUNT AS OpenTransactions COMMIT TRAN SELECT @@TRANCOUNT AS OpenTransactions |
On the other hand, the ROLLBACK TRANSACTION statement helps in undoing all data modifications that are applied by the transaction. In the following example, we will change a particular row but this data modification will not persist.
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRAN UPDATE Person SET Lastname = 'Donald', Firstname = 'Duck' WHERE PersonID=2 SELECT * FROM Person WHERE PersonID=2 ROLLBACK TRAN SELECT * FROM Person WHERE PersonID=2 |
The following table illustrates the structure of the explicit transactions in SQL Server.
|
BEGIN TRANSACTION |
The starting point of the transaction |
|
SQL commands |
DML and SELECT statements |
|
COMMIT TRANSACTION or ROLLBACK TRANSACTION |
Apply data changing to the database or Erase data changing to the database |
Save Points in Transactions
Savepoints can be used to rollback any particular part of the transaction rather than the entire transaction. So that we can only rollback any portion of the transaction where between after the save point and before the rollback command. To define a save point in a transaction we use the SAVE TRANSACTION syntax and then we add a name to the save point. Now, let’s illustrates an example of savepoint usage. When we execute the following query, only the insert statement will be committed and the delete statement will be rolled back.
|
1 2 3 4 5 6 7 8 9 |
BEGIN TRANSACTION INSERT INTO Person VALUES('Mouse', 'Micky','500 South Buena Vista Street, Burbank','California',43) SAVE TRANSACTION InsertStatement DELETE Person WHERE PersonID=3 SELECT * FROM Person ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Person |
Auto Rollback transactions in SQL Server
Generally, the transactions include more than one query. In this manner, if one of the SQL statements returns an error all modifications are erased, and the remaining statements are not executed. This process is called Auto Rollback Transaction in SQL. Now let’s explain this principle with a very simple example.
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN INSERT INTO Person VALUES('Bunny', 'Bugs','742 Evergreen Terrace','Springfield',54) UPDATE Person SET Age='MiddleAge' WHERE PersonID=7 SELECT * FROM Person COMMIT TRAN |
As we can see from the above image, there was an error that occurred in the update statement due to the data type conversion issue. In this case, the inserted data is erased and the select statement did not execute.
Marked transactions in SQL Server
SQL Server allows us to mark and add a description to a specific transaction in the log files. In this way, we can generate a recovery point that is independent of the time. Such as, when an accidental data modification occurs in the database and we don’t know the exact time of the data modification, the data recovery effort can be taken a long time. For this reason, marked transactions can be a useful solution to find out the exact time of the data modifications. In order to create a marked transaction, we need to give a name to the transaction and we also need to add WITH MARK syntax. In the following query, we will delete some rows and we will also mark the modifications in the log file.
|
1 2 3 4 |
BEGIN TRAN DeletePerson WITH MARK 'MarkedTransactionDescription' DELETE Person WHERE PersonID BETWEEN 3 AND 4 COMMIT TRAN DeletePerson |
The logmarkhistory table stores details about each marked transactions that have been committed and it is placed in the msdb database.
|
1 |
SELECT * FROM msdb.dbo.logmarkhistory |
As we can see in the above image the logmarkhistory gives all details about the marked transaction. The following two options help to use marked transactions as a recovery point.
- STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward
- STOPBEFOREMARK rolls forward to the mark and excludes the marked transaction from the roll forward
You can read the following articles to learn more details about recovering a database from the transaction log backups:
- Recovering Data from the SQL Server Transaction Log
- Recovery of Related Databases That Contain Marked Transaction
Conclusion
In this article, we have talked about the transaction in SQL Server statements. Transactions are a vital part of relational database systems because they provide integrity of the databases.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023