Welcome to Appsloveworld technologies , In this session, we learn about transactions in the SQL server.
In this post, We will discuss the following topics
- How to write Rollback and Commit Transaction
- Create SQL Server procedure in a transaction
what’s a transaction?
A transaction is a group of SQL commands that change the data stored in the database. A transaction is treated as a single logical unit of work. and the most important thing is that A transaction ensures that either all of the SQL commands succeed or none of them.
If one of the SQL commands in the transaction fails, all of the SQL commands fail and any data that was modified in the database is then rolled back.
Let’s look at an example. I have a Table TblProducts which has information about products
At the moment, the quantity of Mobile Phones available is 100 at $10000. Now, let’s say I want to change the price to 12000.
TblProducts set price=10000 where id=2
Now, when We execute an update statement, it will update the price of the Mobile Phone in TblProducts Table.
Sql transaction real time example
Now, right now ,sql update statement is not part of any transaction. So as soon as you execute the update statement the change is made permanent to the database.
On the other hand, you can make an updated statement belong to a transaction by including begin transaction. So now let’s try to change the price to 15000 with a transaction.
So As you can see in the above query ,Now our update statement is part of the transaction. When we execute these two statements(i.e update and select) together, within the same window, within the same connection.
When I say, get the data back from the table, look at the below image. It shows 15000 which means our query executed successfully.
But then let’s try to make another connection to the SQL server. Click on the “New Query” button it will open a new connection for us.
Now, let’s try to get the data from the same table, look at what’s going to happen. It says executing the query and you will never get the data.
if you look at the TblProducts table, it has got only two rows to select those rows from that table. It should take less than a second. But it will return the data.
That’s because you have initiated transaction. But you did not either commit or Rollback the transaction because of which other users will not be able to see that uncommitted data by default.
And that’s because SQL Server default isolation level is read committed, meaning read only committed data.
But here we have some uncommitted data in TblProducts. That’s why we are not able to get the data from that table.
if I want to see that data, what I can do is basically set the transaction isolation level to read uncommitted data.
So let’s execute the above query, we are able yoy get the uncommitted data
Now, let’s Rollback transaction
when I execute select * from TblProducts we get the previous data i.e price is not updated to 15000 because we Rollback the transaction.
so the change is undone. now whenever you are issuing updating, insert, delete statements, and if these statements are part of a transaction, the change is not made immediately permanent to the database until you commit the transaction.
So if you want to make the change permanent, you need to commit the transaction. If you want to undo the change, then you can Rollback the transaction.
Let’s try to make that change permanent
I basically have to say commit transactions
At this point, the change is made permanent to the database.
Now transaction processing follows very simple steps. You Begin a transaction using “Begin Transaction” and then you process the database commands.
You might have one or more SQL servers of commands or inserts, updates, and deletes and then you check for any errors. If there are errors, you rollback the transaction, otherwise, you commit the transaction.
Transaction in sql server stored procedure example
Let’s look at an example. I have two tables here TblCustomerPhysicalAddress and TblShipingAddress. let’s say we are shopping online.
Most of the e-commerce sites have a Physical address and Shiping address, and most of us will have the same physical address and Shiping address along the same lines here.
If you look at this, we have a physical and a Shiping address of the customer.
if look at UserId with 1000, he has the same physical and Shiping address. Now, if you look at the city name, the city spelling, misspelled in Shiping Address.
Now I want to correct this, to correct this. I have a stored procedure here.
And if you look at here procedure, it’s a pretty simple.
All we are doing here is we are updating the TblCustomerPhysicalAddress table and TblShipingAddress table and we are setting the city to the correct spelling.
if you look at two update statements in procedure, these are wrapped between begin and Commit transaction blocks and this block is further wrapped between Begin try and End try. And then following the Try block with catch block where we are Rollback the transaction back.
so obviously when we excude the stored procedure, what’s going to happen?
That’s going to execute the first update statement, and then it’s going to exclude the second update statement. And if there are no errors, the transaction will be committed.
If there are errors, whenever you are executing first or second update statement, what’s going to happen?
The control will immediately be transferred to the catch block and within the catch block, we are rolling the transaction back.
Now, since these are two simple updates, statements, both of these statements should succeed and correct spelling to New Delhi.
So let’s exclude the stored procedure, it will update the city name correctly
if you look at the city column length in the TblShipingAddress table. So if you look at the city column length, it’s 10 characters.
Now let’s ceate one more stored procedure, But now I am trying to update the City = ‘New Delhi Heaven City 1100091’ and it is definitely more than 10 characters. So obviously when this update statement is being executed, we will get an error.
Now, when we execute this procedure, what’s going to happen if it comes to updates the TblCustomerPhysicalAddress city, which is fine.
And then when it comes to updating the TblShipingAddress, what’s going to happen?the city column length is 10, but the value is more than 10. So this is going to throw an exception.
So when an exception occurs, what’s going to happen?
The control immediately goes to the catch block and the catch block is rolling back the transaction.
So when we roll the transaction back, what’s going to happen?
Whatever change that was made by this first update statement is also going to be undone.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?