In this article, I am giving a quick overview about Transactions in SQL Server. After completing this article you will understand:
What is Transaction in SQL Server?
What are different TCL commands?
What are different modes of Transactions?
Please give your valuable suggestions and feedback to improve this article.
What is Transaction in SQL Server?
Transaction is a single unit of work; if a Transaction is successful then all of the data modifications made during the transaction are committed and become a permanent part of the database. If a Transaction encounters any errors and must be cancelled or rolled back, then all of the data modification is erased. A Transaction always follows ACID rules. Click here to know about ACID Properties of Transaction.
What are different TCL commands?
To manage the Transaction we have TCL (Transaction Control language) with three commands in it COMMIT, ROLLBACK and Save Transaction. Click here to know about types of SQL Commands.
COMMIT- Commit Marks the end of a successful implicit or explicit transaction. COMMIT Transaction makes all data modifications performed since the start of the transaction a permanent part of the database.
ROLLBACK- Roll back an Explicit or Implicit transaction to the beginning of the transaction or to
Save Transaction- A user can set a save point or marker within a transaction. The save point defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a save point, it must be proceed to completion with more TSQL statement if needed and a COMMIT Transaction statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the ROLLBACK Transaction statement.
What are different modes of Transactions?
There are three modes of Transaction in SQL Server
1. Auto Commit Transaction
2. Implicit Transaction – A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK keyword.
3. Explicit Transaction – Each Transaction is explicitly stared with the Begin Transaction keyword and explicitly ended with a COMMIT or ROLLBACK keyword.
Auto Commit
By default SQL Server uses Auto commit transaction mode which means after executing each statement it will automatically commit it
Implicit Transactions
By default SQL Server uses Auto commit transaction mode but you can change it to Implicit Transaction mode using below query
SET IMPLICIT_TRANSACTONS ON
When SET IMPLICIT_TRNSACTIONS is set to ON, then it sets the connection into Implicit Transaction Mode, but when it is set to OFF, then it returns the connection to auto commit transaction mode. It is to be noted that When Transaction mode is changed to Implicit Transaction mode, it will remain ON only for the same connection it was set to ON.
Let’s understand the Transaction mode using simple examples
As I have already told there are three types of TRANSACTION MODE - Auto commit, IMPLICIT and EXPLICIT. By default in SQL Server Transaction mode is auto commit. But you can change to IMPLICIT Transaction mode using SET IMPLICIT_TRANSACTIONS command. Once you change it to IMPLICIT mode, Transaction mode will be changed to IMPLICIT mode but only for that particular connection. When you open another connection for that new connection TRANSACTION mode will be auto commit.
Let’s open two connections and in first connection we will SET IMPLICIT_TRANSACTIONS ON and in second connection we haven’t SET it to ON.
In first connection I am creating EMP table and in Second connection I am creating DEP table. From the below Image you can clearly see. I ran both the connection’s command and it ran successfully.
As I have already told in my beginning that if Transaction mode is set to IMPLICIT then we have to end the transaction by COMMIT or ROLLBACKbut in my first connection I have not done that. Now let’s close the first connection.
As you can see from the above Image when I tried to close the connection I got a pop up message, which is saying I have not committed the transaction. If I will click on Yes then it will commit the Transaction and EMP table will be created in the database for permanently. If I click on NO then Transaction will be ROLLED BACK and EMP table will not be created in TEACHMESQLSERVER database. Just for testing purpose I am clicking on NO.
Now let’s try to close the second connection, you can clearly see from the below Image when I try to close second connection it is not asking whether I want to commit or not. It is asking if I want to save my SQL command then I can save it. I am not saving the SQL command and clicking on NO.
Now again I create a new connection, let’s see how many table our TEACHMESQLSERVER database contains. As you can see from the below result, it say only DEP table is present in our database. This DEP table was created by the second connection. EMP table is not present in our database which was created by first connection. Even after successful execution EMP table is not present in the database it is because when we closed the First connection we clicked on NO. It didn't commit but it rolled back the transaction.
Explicit Transactions
Explicit Transaction start with BEGIN TRAN or BEGIN TRANSACTION keyword then it contains TSQL statements and ends with COMMIT or ROLLBACK keywords.
Syntax
BEGIN TRANSACTION
SQL Statement
COMMIT | ROLLBACK
Let’s have one-one example for both COMMIT and ROLLBACK.
We are inserting a record in DEP table and committing our transaction
USE TEACHMESQLSERVER
GO
BEGIN TRANSACTION
INSERT INTO DEP VALUES(1)
COMMIT
GO
SELECT * FROM DEP
As you can see this records becomes permanent and stored in DEP table because we COMMITTED our transaction.
In this below query we are inserting another record in DEP table but we are rolling back our transaction.
USE TEACHMESQLSERVER
GO
BEGIN TRANSACTION
INSERT INTO DEP VALUES(2)
ROLLBACK
GO
SELECT * FROM DEP
As you know TRUNCATE command can’t be rolled back until it is used within transaction. If you don’t know about what are the difference between Truncate and Delete command then
If you will run the below command then you will find, Truncate can also be rolled back if it is used in Transaction.
USE TEACHMESQLSERVER
GO
BEGIN TRANSACTION
TRUNCATE TABLE DEP
ROLLBACK
GO
SELECT * FROM DEP
We can also save transaction point in our Transaction; those save point can be used for roll back. Below example shows a simple example of SAVE TRANSACTION. We are inserting three records but we have created on transaction point after inserting first record. After two more insert operation we are rolling back to Saved Transaction point named as one and then committing the transaction.
USE TEACHMESQLSERVER
GO
BEGIN TRANSACTION
INSERT INTO DEP VALUES(2)
SAVE TRANSACTION ONE
INSERT INTO DEP VALUES(3)
INSERT INTO DEP VALUES(4)
ROLLBACK TRANSACTION ONE
COMMIT
GO
As you can see form the above result set record 3 and 4 was not inserted in to the table, it is because before committing the transaction we rolled back to the saved transaction point which comes after the first insertion which is 2.
Transaction
DEFINITION
BEGIN TRAN
UPDATE ABC SET A = 2
BEGIN TRAN
UPDATE ABC SET A = 3
ROLLBACK
UPDATE ABC SET A = 4
COMMIT
A set of actions that will succeed or fail as a batch.
FUNCTIONS (ACID)
Atomicity - nothing or all,
means, a set of actions that will succeed/fail as a batch.
means, Either all actions within a transaction will be succeeded or all actions within a transaction will be failed
means, a set of actions that will succeed/fail as a batch.
means, Either all actions within a transaction will be succeeded or all actions within a transaction will be failed
Consistency - At the end of a transaction, either a new state of data is available or original data will be available.
Isolation - During transaction (before rollback/commit), data will be hidden from any other transaction
Durability - After a transaction is committed, a final state of data will be available forever even server fails/restarts
TYPE OF TRANSACTION
Implicit - While dealing with ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, SELECT, INSERT, UPDATE, TRUNCATE, OPEN, REVOKE SQL server applies transaction
- By default IMPLICIT Transactions will be off means auto committed, you can make it on using following command. If you do so, you have to explicitly COMMIT/ROLLBACK after all above commands
SET IMPLICIT_TRANSACTIONS ON
Explicit -
- You can have your own transaction within a stored procedure.
- Each transaction will have 2 ends. Either Rollback or Commit
- Transaction can be started using BEGIN TRANSACTION command
- Transaction can be ended using COMMIT TRANSACTION / ROLLBACK TRANSACTION command
- It is a good practice to use transaction whenever multiple DML operations are there. For single DML operation, no transaction is required which will be taken care by IMPLICIT transaction
- If transaction is open on Object A, no other DML operation will be allowed on Object A from another session. Even SELECT query won’t work until WITH NOLOCK specified
- When huge number of records are getting operated, it is not good practice to use transaction because
- It will give a big time performance hit
- All the other users will be blocked
- Transaction log will grow exponentially which will consume disk space like anything
Alternate solution is – perform all the operations on temp table “without” using transaction and then make temporary table and actual table in sync using transaction.
- By default all the queries are auto committed in SQL Server
- COMMIT will commit only last (latest) open transaction
- ROLLBACK will roll back ALL active transactions. Any further COMMIT/ROLLBACK will throw an error saying “no corresponding BEGIN TRANSACTION”
BEGIN TRAN
UPDATE ABC SET A = 2
BEGIN TRAN
UPDATE ABC SET A = 3
ROLLBACK
UPDATE ABC SET A = 4
COMMIT
Above code won’t work and throw an error “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.”
- Transactions are not applicable on variables, neither on single value variable nor on table variables.
- So it is a good practice to check the TranCount before hitting COMMIT or ROLLBACK
IF (@@TRANCOUNT > 0)
COMMIT
- ROLLBACK will not reset identity back to its original stage.
- You can give the name also to Transaction
