1. Introduction to SQL Transactions
In SQL Server we call a complete set of action as ‘Transaction’. The SQL Transaction induces a meaningful change to the database. Say, for Example, let us say you are transferring money from one bank account to another one. This involves the below specified actions:
- Create a withdraw entry in the first back account.
- Reduce the balance in your first bank account.
- Create a deposit entry in the second bank account.
- Increment the balance in the second bank account.
We will consider first bank account as Savings Account and second bank account as Current Account within the same bank. Now we can say the bank website will see all these four actions together as Money Transfer Transaction. Even a single action fails the entire SQL transaction get cancelled to avoid data corruption.
In this article, we will explore how to use transactions in SQL server. Here, we will explore three distinct types SQL transactions. They are:
- Auto Commit Transactions
- Implicit Transactions
- Explicit Transactions
2. Auto Commit SQL Transactions
In ‘Auto Commit Transaction’ mode, SQL Server immediately commits the change(s) after running the statement. That’s why we call this as Auto Commit Transaction. This is the default Transaction mode in SQL Server.
2.1 Executing Single SQL Statement
Have a look at the video 1 (No Audio) and follow the explanation below.
Video 1: Single Statement As One Transaction
From the video you can say that we ran three update statements by highlighting each update one by one. The second update statement is made wrong intentionally. After running all three statements, the select query result shows first and the third update went on to the database. Here, after executing each statement, SQL Server commits the changes to the database automatically. That is why we call this as Auto Commit Transaction, which is the default SQL transaction mode in SQL server 2005.
2.2 Executing Multiple SQL Statements
Now, have a look at the video 2 (No Audio) and follow the explanation below.
Video 2: Multiple Statements As One Transaction
The script is same. We have same three update statements with a same spelling mistake in the second update statement. However, this time we updated all three update statements at once by selecting all three and hitting the execute button. Here, we executed all these three statements on a single go and hence we call these three statements as one batch. Since the second update statement failed in the batch, none of the row update takes place in the database. Here, SQL Server performed a Rollback because of the batch failure (2nd update).
2.3 Forming SQL Batches Using ‘Go’
The Statement ‘Go’ forms the batches. In the video 1 and video 2, the manual selection of statement(s) formed the batches. We can re-write the script for video 1 and video 2 using ‘Go’ as shown below. This will avoid the manual selection of the statement(s) to form the transaction. When SQL Server sees a ‘Go’, it forms a transaction till the ‘Go’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
--Example 1 --Batch 1 Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176'; Go --Batch 2 Update Authors Ser Au_LName = 'Voyer' where Au_Id = '213-46-8915'; Go --Batch 3 Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766'; Go --Batch 4 Select Au_Id, Au_Fname + ',' + Au_Lname as Name, Phone, City from Authors where Au_id in ('172-32-1176', '213-46-8915','238-95-7766'); Go --Batch 5 Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176'; Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915'; Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766'; Go --Example 2 --Treat All three as a Single Batch. --Batch 1 Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176'; Update Authors Ser Au_LName = 'Voyer' where Au_Id = '213-46-8915'; Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766'; Go --Batch 2 Select Au_Id, Au_Fname + ',' + Au_Lname as Name, Phone, City from Authors where Au_id in ('172-32-1176', '213-46-8915','238-95-7766'); Go --Batch 3 Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176'; Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915'; Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766'; Go |
From the above statements, SQL Server performs a Commit or Rollback after the Go statement. Hence, in the Auto Commit Transaction we don’t have to control the Commit and Rollback as SQL Server takes care of it.
Also note that SQL Server will not apply the Rollback when SQL Server encounters a Runtime error. Imagine that in example 2, instead of the misspelled keyword we violated a constraint (Say Primary key constraint) in the second statement. In this case, SQL server commits first and third statement.
3. Implicit SQL Transaction
In ‘Implicit Transaction’ Mode, we can control the ‘ Rollback’ and the ‘ Commit’ actions. A new transaction starts after the commit/Rollback. We can turn ON and Turn OFF this transaction mode based on the need. Now have a look at the below depiction.
In the above picture, two Commit and one rollback statements are executed after turning on the Implicit Transaction. As already told after a Commit or a Rollback a new SQL Transaction gets started and that is how we see three Transactions in the above depiction. Once we turn off the Implicit Transaction, SQL Server will set back Transaction Mode to the default Auto Commit Transaction.
We can use the below SQL statements to Turn ON or Turn OFF the Implicit Transaction:
|
1 2 |
Set Implicit_Transactions On; Set Implicit_Transactions Off; |
Now we can have a look at the below example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--Example 3 Set Implicit_Transactions On; --Transaction 1 Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176'; commit; --Transaction 2 Update Authors Set Au_LName = 'Voyer' where Au_Id = '213-46-8915'; Rollback; --Transaction 3 Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766'; commit; --Select the Authors, to see the effect of Update statement Select Au_Id, Au_Fname + ',' + Au_Lname as Name, Phone, City from Authors where Au_id in ('172-32-1176', '213-46-8915','238-95-7766'); --Revert back to Original Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176'; Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915'; Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766'; Go Set Implicit_Transactions Off; |
Explanation
In this example, three update statements are executed followed by either a Rollback or a Commit. Note that, here we have the freedom of specifying where we want to do a Commit or a Roll back which we cannot do in the default Auto Commit Transaction mode. Also, know that even though we can specify where we want to end the Transaction, it is not possible to control the starting of the Transaction. Because a new SQL Transaction starts after the existing one ends.
After the first Update statement, we asked for a commit, so the update of the author’s last name as Black is committed to the database and after that SQL Server starts a new Transaction. The second Transaction comes to an End when SQL Server runs the rollback statement. The update of the author’s last name (as Voyer) kept in the memory is discarded after the rollback and SQL Server starts one more new Transaction. Note, at the end of the script we turned off the implicit transaction. Once we turn OFF the Implicit Transaction, the mode changes to default Auto Commit Transaction.
OK. What is the result of executing this script? The first and last update statement committed to the database, and the second update is discarded.
4. Explicit SQL Transaction
In ‘Explicit Transaction’, we control the starting and ending of the SQL transaction. Now we will have a look at the below Picture:
As we control the starting and ending of the transaction, this transaction type is widely used while making the compiled unit of code say for Ex. Stored Procedures. Have a look at the below specified code:
Here we started two transactions and note that the transaction will end when there is a commit or rollback. In this example, the first SQL transaction will get through and the “Data Inserted message” will appear in the message area while executing the script. The second SQL Transaction fails as we violated the Not Null column constraint from the insert statement. Note, skipping a column means we like to leave the column as null.
As this is a constraint violation, and this takes place at runtime, the @@Error in our script returns a Non-Zero Error number. When the data insert succeeds with no problem, we get zero in the @@Error status variable. The remaining portion of the script is obvious. If you need to copy the example, you can take that from below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
--Example 04 Declare @ErrNo int Begin Transaction; Insert into Authors(Au_id, au_lname, au_fname, Phone, contract) values ('112-33-1811', 'Jane', 'Marey', '409 210-2331', 1); Set @ErrNo = @@Error; if @ErrNo != 0 Begin Print 'Error Occurred. Transaction Cancelled'; RollBack; End else Begin Print 'Data Inserted.'; Commit; End Begin Transaction; Insert into Authors(Au_id, au_lname, au_fname, Phone) values ('112-33-1234', 'Billy', 'Jones', '409 234-2232'); Set @ErrNo = @@Error; if @ErrNo != 0 Begin Print 'Error Occurred. Transaction Cancelled'; RollBack; End else Begin Print 'Data Inserted.'; Commit; End -- Revert back the Change Delete from Authors where Au_Id = '112-33-1811'; |
Categories: SQL Server
Tags: Auto Commit Transactions, Explicit Transactions, Implicit Transactions
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.