What is "Transaction" in Sql Server with example

Welcome to Appsloveworld Technologies. In this session, we will learn about transactions in the SQL Server.

In this post, we will discuss the following topics:

How to write Rollback and Commit Transaction.
Creating 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. It's treated as a single logical unit of work. The key aspect of a transaction is ensuring that either all SQL commands within it succeed, or none of them do.

If any SQL command within the transaction fails, all the commands are rolled back, and any data modifications made by them are undone.

For example, let's consider a table named TblProducts, which contains information about products.

At the moment, there are 100 Mobile Phones available priced at $10,000. Now, let’s say I want to change the price to $12,000.

TblProducts set price=10000 where id=2

Now, when we execute an update statement, it will update the price of the Mobile Phone in the TblProducts table.

Sql Transaction Real-Time Example

Now, currently, the 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 update statement belong to a transaction by including 'begin transaction'. So now let’s try to change the price to 15000 within 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, our update statement is now 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 retrieve the data back from the table, as shown in the image below, it displays 15000, which means our query executed successfully.

But let’s try to make another connection to the SQL server. Click on the “New Query” button to open a new connection for us.

Now, let’s try to retrieve the data from the same table. Look at what happens. It says executing the query, but you will never get the data.

If you look at the TblProducts table, it only has two rows. Selecting those rows from the table should take less than a second. But it doesn't return the data.

That’s because you have initiated a transaction, but you did not either commit or rollback the transaction. Because of this, other users will not be able to see that uncommitted data by default.

This is because SQL Server's default isolation level is read committed, meaning it only reads committed data.

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., the price is not updated to 15000 because we rolled back the transaction.

So the change is undone. Now, whenever you are issuing update, 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 commands such as 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 e-commerce sites have both a physical address and a shipping address. For many of us, these addresses are often the same.As you can see here, we have the physical and shipping addresses of the customer.

If you look at the UserId with 1000, you'll notice that he has the same physical and shipping address. However, there is a misspelling in the city name in the shipping address.

To correct this issue, 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

If you look at this procedure, it’s quite simple. All we are doing here is updating the TblCustomerPhysicalAddress table and TblShippingAddress table, setting the city to the correct spelling.

If you examine the two update statements in the procedure, they are enclosed within begin and commit transaction blocks. This block is further enclosed within begin try and end try. Following the Try block, there's a catch block where we rollback the transaction.

So when we excude the stored procedure, what’s going to happen?

This will execute the first update statement, and then proceed to the second update statement. If there are no errors encountered, the transaction will be committed.

If there are errors, whenever you are executing first or second update statement, what’s going to happen?

If an error occurs during the execution of the procedure, the control will be transferred to the catch block where the transaction will be rolled back. However, since these are simple update statements, both statements should succeed and correct the spelling to 'New Delhi'. Let's execute the stored procedure to update the city name correctly.

If you examine the length of the city column in the TblShipingAddress table, you'll find that it's 10 characters.

Capture

Now let’s create one more stored procedure, but this time I'm attempting to update the City to ‘New Delhi Heaven City 1100091’, which is definitely more than 10 characters. So, obviously, when this update statement is 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 will happen is that it will update the city in TblCustomerPhysicalAddress, which is fine. However, when it comes to updating the TblShippingAddress, there's an issue. The city column length is set to 10, but the value being updated is more than 10 characters. This will throw an exception.

When an exception occurs, what’s going to happen?

The control immediately goes to the catch block, and within the catch block, the transaction is rolled back.

When we roll the transaction back, what’s going to happen?

The changes made by the first update statement will also be undone.