What is transaction in sql server with example

Welcome to Appsloveworld technologies , In this session, we learn about transactions in the SQL server.

In this post, We will discuss the following topics

1. How to write Rollback and Commit Transaction
2. 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.

Begin Transaction
update TblProducts set price=15000 where id=2

--selecting data back from the table
select * from TblProducts

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.

set transaction isolation level read uncommitted
select * from TblProducts

So let’s execute the above query, we are able yoy get the uncommitted data

Now, let’s Rollback transaction

Begin Transaction
update TblProducts set price=15000 where id=2
Rollback Transaction
--selecting data back from the table
select * from TblProducts

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

Begin Transaction
update TblProducts set price=15000 where id=2
Commit Transaction

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.

USE [TestDatabase]
GO
/****** Object: Table [dbo].[TblCustomerPhysicalAddress] Script Date: 10/16/2020 5:17:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblCustomerPhysicalAddress](
[Id] [int] NOT NULL,
[UserId] [int] NULL,
[AddressLine] [nvarchar](max) NULL,
[City] [varchar](10) NULL,
[Country] [nvarchar](50) NULL,
[ZipCode] [nvarchar](50) NULL,
[FullName] [varchar](200) NULL,
[latitude] [nvarchar](50) NULL,
[longitude] [nvarchar](50) NULL,
CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
/****** Object: Table [dbo].[TblProducts] Script Date: 10/16/2020 5:17:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProducts](
[Id] [int] NOT NULL,
[ProductName] [nvarchar](100) NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [float] NULL,
CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
/****** Object: Table [dbo].[TblShipingAddress] Script Date: 10/16/2020 5:17:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblShipingAddress](
[Id] [int] NOT NULL,
[UserId] [int] NULL,
[AddressLine] [nvarchar](max) NULL,
[City] [varchar](10) NULL,
[Country] [nvarchar](50) NULL,
[ZipCode] [nvarchar](50) NULL,
[FullName] [varchar](200) NULL,
[latitude] [nvarchar](50) NULL,
[longitude] [nvarchar](50) NULL,
CONSTRAINT [PK_ShipingAddress] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
INSERT [dbo].[TblCustomerPhysicalAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (1, 1000, N'57, Shakti khand 3, Indrapuram', N'New Delhi', N'India', N'201014', N'mohit Choudhary', N'28.6508241', N'77.3660891')
GO
INSERT [dbo].[TblCustomerPhysicalAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (2, 1005, N'81 Faubourg Saint Honoré', N'Paris', N'France', N'75019', N'Arienne V Du Trieux', N'28.6496675', N'77.3661788')
GO
INSERT [dbo].[TblCustomerPhysicalAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (3, 1007, N'4514 Zimmerman Lane', N'Los Angele', N'USA', N'90014', N'Gladys W Blackwelder', N'28.634583429365982', N'77.37023908626168')
GO
INSERT [dbo].[TblCustomerPhysicalAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (4, 1009, N'1863 Grim Avenue', N'San Diego', N'USA', N'201308', N'Audrey C Chambers', NULL, NULL)
GO
INSERT [dbo].[TblCustomerPhysicalAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (5, 1008, N'285-1294, Nabasama, Yotsukaido-shi, Chiba', N'Tokyo', N'USA', N'2840034', N'Yoshiyuki', NULL, NULL)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (1, N'Books', 10, 100)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (2, N'Mobile Phone', 100, 15000)
GO
INSERT [dbo].[TblShipingAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (1, 1000, N'57, Shakti khand 3, Indrapuram', N'New Delhi', N'India', N'201014', N'mohit Choudhary', N'28.6508241', N'77.3660891')
GO
INSERT [dbo].[TblShipingAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (2, 35, N'81 Faubourg Saint Honoré', N'Paris', N'France', N'75019', N'Arienne V Du Trieux', N'28.6496675', N'77.3661788')
GO
INSERT [dbo].[TblShipingAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (3, 45, N'4514 Zimmerman Lane', N'Los Angele', N'USA', N'90014', N'Gladys W Blackwelder', N'28.634583429365982', N'77.37023908626168')
GO
INSERT [dbo].[TblShipingAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (4, 200, N'1863 Grim Avenue', N'San Diego', N'USA', N'201308', N'Audrey C Chambers', NULL, NULL)
GO
INSERT [dbo].[TblShipingAddress] ([Id], [UserId], [AddressLine], [City], [Country], [ZipCode], [FullName], [latitude], [longitude]) VALUES (5, 202, N'285-1294, Nabasama, Yotsukaido-shi, Chiba', N'Tokyo', N'USA', N'2840034', N'Yoshiyuki', NULL, NULL)
GO

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.

Create Procedure spUpdatecCustomerAddress
as
Begin
Begin Try
Begin Transaction
Update TblCustomerPhysicalAddress set City = 'New Delhi'
where Id = 1

Update TblShipingAddress set City = 'New Delhi'
where Id = 1
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End

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.

Create Procedure spUpdatecCustomerAddressNew
as
Begin
Begin Try
Begin Transaction
Update TblCustomerPhysicalAddress set City = 'New Delhi'
where Id = 1

Update TblShipingAddress set City = 'New Delhi Heaven City 1100091'
where Id = 1
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End

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.

The post What is transaction in sql server with example appeared first on Software Development | Programming Tutorials.