Welcome to Appsloveworld Technology. In this session we will learn more about SQL Server transactions.
In this article we discuss the following topics
- How to write otkat and commitment.
- Creating an SQL Server procedure in a transaction
, what’s this about?
The transaction is a group of SQL commands that modify the data in the database. The transaction is considered a single logical unit of work and, most importantly, the transaction ensures that all or none of the SQL commands are successful.
If one of the SQL commands in a transaction fails, all SQL commands fail and all data that has changed in the database will be undone.
Let’s take an example. I have a TblP products table with information about the products
Currently there are 100 mobile phones available for $10,000. Suppose I want to change the price of 12,000.
TblProducts determines the price = 10000, where id = 2
Now that we update the operator, it will update the phone price in the TblProducts table.
Real-time sql transaction Example
Currently, the sql update statement is not part of any agreement. So as soon as you perform an update, the change becomes permanent in the database.
On the other hand, you can assign the updated report to the transaction by taking the original transaction with you. Now let’s try to raise the price to 15,000 with the deal.
Update
Update TblProducts Set the price to 15000, where id=2
-Select the data in Table
and select * from the TblProducts list.
As you can see in the request above, our request for an update is now part of the agreement. If we execute these two instructions (i.e. Refresh and Select) together, in the same window, in the same connection.
When I say to return the data from the table, look at the image below The screen shows 15000, which means that our query has been completed successfully.
But then we want to try to establish another connection to the SQL server. Click on the button New request, a new connection will be opened for us.
Now let’s try to get some data from a table to see what’s happening. It indicates that the application is running and that you will never receive the data.
If you look at the TblProducts table, there are only two rows to choose from in this table. This will take less than a second. But he’s going to return the data.
That’s because you initiated the deal. However, you have not made a commit or withdrawal, so that other users cannot see this uncommitted data by default.
This is because the standard isolation level of the SQL Server is fixed, which means that only fixed data can be read.
But here we have unrelated data in TblProducts. Therefore, we cannot extract data from this table.
If I want to see this data, I can essentially define the level of transaction isolation for reading unrelated data.
Define the transaction isolation level for reading the unrelated
selection * of TblProducts.
Thus, if we ask the question above, we can obtain unrelated data
.
.Now let’s rewind to case.
First transaction
Update TblProducts sets price=15000, where id=2
Cancellation transaction
– data selection in table
selects * in TblProducts.
When I execute the * option of TblProducts, we get the previous data, i.e. the price is not updated to 15000 when we cancel the exchange.
So the change is reversed. If you now publish updates, insert statements, delete statements, and if these statements are part of a transaction, the change will not immediately be permanently stored in the database until you have completed the transaction.
So if you want to make the change permanent, you have to make a trade. If you want to reverse the change, you can reverse the transaction.
Try to make this change constant.
Basically, I have to say that I do business…
First transaction
Update TblProducts fixed price=15000 where id=2
Mandatory transaction
At this point, the change becomes permanent in the database.
Transaction processing is now done in very simple steps. You start a transaction by starting an operation and then process the commands in the database.
You can have one or more SQL servers for commands or insertions, updates and deletions, and error checking. If errors occur, you must reverse the transaction, otherwise you will execute the transaction.
The transaction on the sql server stores the sample procedures
Let’s take an example. I’ve got two tables here: TblCustomerPhysicalAddress and TblShipingAddress. Let’s say we shop online.
USAGE [Test Database]
GO
/****** Subject : Table [dbo]. TblCustomerPhysicalAddress] Date of Scenario : 10/16/2020 5:17:59 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblCustomerPhysicalAddress](
[Id] [int] NOT NULL,
[UserId] [int] NULL,
[Address Line] [nvarchar](max) NULL,
[Location] [varchar](10) NULL,
[Country] [nvarchar](50) NULL,
[ZipCode] [nvarchar](50) NULL,
[full name] [varchar](200) NULL,
[width] [nvarchar](50) NULL,
[length] [nvarchar](50) NULL,
CONSTRACTED [PK_customer address] MAIN KEY CLUSTERED
(
[Id] ASC
)
)
GO
/****** object : Table [dbo]. Date of the scenario : 10/16/2020 5:17:59 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblProducts] (
[Id] [int] NOT ZERO,
[product name] [nvarchar](100) NOT ZERO,
[quantity] [int] NOT ZERO,
[price] [swimming] ZERO,
CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
/****** Object : Table [dbo]. [TblShipping address] Scripting date : 10/16/2020 5:17:59 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblShipping address](
[Id] [int] NOT NULL,
[UserId] [int] NULL,
[AddressLine] [nvarchar] (Maximum) NULL,
[Location] [varchar] [10] NULL,
[Country] [nvarchar] [50] NULL,
[ZipCode] [nvarchar] [50] NULL,
[full name] [varchar](200) NULL,
[latitude] [nvarchar](50) NULL,
[longitude] [nvarchar](50) NULL,
CONSTRACTED [PK_ShipingAddress] MAIN KEY CLASSIFIED
(
[Id] ASC
)
)
GO
INSERT [dbo].TblKundenPhysikalischeAddress] ([Id], [BenutzerId], [Adresszeile], [Stadt], [Country], [Postcode], [Vollständiger Name], [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′) INSERT
GO
[dbo]. TblCustomerPhysical address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full Name], [Latitude], [Longitude]). INSERT 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
[dbo].TblKundenPhysikalischeAdresse] ([Id], [BenutzerId], [Adresszeile], [Stadt], [Country], [Postcode], [Vollständiger Name], [Latitude], [Longitude]). INSERT 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] TblCustomerPhysical address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full Name], [Latitude], [Longitude]). INSERT VALUES (4, 1009, N’1863 Grim Avenue’, N’San Diego’, N’USA’, N’201308′, N’Audrey C Chambers’, NULL)
GO
[dbo].TblKundenPhysikalischeAdresse] ([Id], [BenutzerId], [Adresszeile], [Stadt], [Country], [Postcode], [Vollständiger Name], [Latitude], [Longitude]). INSERT VALUES (5, 1008, N’285-1294, Nabasama, Yotsukaido-shi, Chiba’, N’Tokyo’, N’USA’, N’2840034′, N’Yoshiyuki’, NULL)
GO
INSERT [dbo]. ([Id], [product name], [quantity], [price]) VALUES (1, N Pounds, 10, 100)
GO
INSERT [dbo].TblProducts] ([Id], [product name], [quantity], [price]) Price (2, N Mobile phone, 100, 15000)
GO
INSERT [dbo]. TblDelivery address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full name], [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′) INSERT
GO
[dbo]. TblDelivery address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full Name], [Latitude], [Longitude]). INSERT 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
[dbo]. TblDelivery address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full Name], [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′) INSERT
GO
[dbo]. TblDelivery address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full Name], [Latitude], [Longitude]). INSERT Prizes (4, 200, N’1863 Grim Avenue’, N’San Diego’, N’USA’, N’201308′, N’Audrey C Chambers’, ZERO)
GO
[dbo]. TblDelivery address] ([Id], [UserId], [Address bar], [City], [Country], [Postcode], [Full name], [Latitude], [Longitude]). VALUES (5, 202, N’285-1294, Nabasama, Yotsukaido-shi, Chiba’, N’ Tokyo’, N’ USA’, N’ 2840034′, N’ Yoshiyuki, ZERO)
GO
Most e-commerce sites have a physical address and a shipping address, and most of us will have the same physical address and shipping address here on the same lines.
If you look at this, we have the physical address and delivery address of the customer.
If you look at the UserId with 1000, it has the same physical and delivery address. Now, if you look at the name of the city, the spelling of the ship’s address is wrong.
Now I want to fix it, fix it. I keep the procedure here.
spUpdatecCustomerAddress set StartStartUpdate TblCustomerPhysicalAddress set City = ‘New Delhi’ with Id = 1
Update shipping address (TblShipingAddress) City = New Delhi
with Id = 1
Start of transaction
End of attempt
Start of capture
Withdrawal of transaction
End of capture
End of capture
And if you look at this procedure, it’s pretty simple.
All we do here is update the TblCustomerPhysicalAddress table and the TblShipingAddress table and adjust the city to the correct spelling.
, if you look at the two update operators in the procedure, they wrap between the Start and Safe Transaction blocks, and wrap this block between the start and end attempts. Then we follow the test block with the capture block, where we cancel the transaction.
So, of course, what happens when we get rid of the stored procedure?
It executes the first update operator and then shuts down the second update operator. And if there are no mistakes, then the deal is done.
If errors occur, what happens with the first or second update?
Control is immediately transferred to the Catch block, and in the Catch block we cancel the transaction.
Now that these are two simple updates, both statements in New Delhi must be successful and spelled correctly.
If we exclude the stored procedure, the place name is thus correctly updated
.
.if you look at the length of the City Column in the TblShipingAddress table. For example, if you look at the length of the city column, then it is 10 characters long.
Now let’s finish another saved procedure, but I’m trying to update City = New Delhi Heaven City 1100091, and there are definitely more than 10 characters. It is therefore obvious that we will get an error if we run this update operator.
spUpdateCustomerAddressNew create asStartAttempt to startInitial TransactionUpdate TblCustomerPhysicalAddress set City = ‘New Delhi’ with Id =
Update of address set TblShipingAddress City = New Delhi Heaven City 1100091
with Id = 1
Mandatory trade
End of catch
Start of catch
Mobile trade
End of catch
End of catch
Now that we have this procedure, what happens when we talk about updating the city TblCustomerPhysicalAddress, which is quite normal.
And then, when it comes to updating the TblShipingAddress, what happens? The length of the city column is 10, but the value is greater than 10. So this would be an exception.
What happens if the exception occurs?
The check goes immediately to the registrar and the registrar cancels the transaction.
What happens if we cancel the agreement?
The changes made by this first update statement will also be cancelled.
Related Tags:
how much ram do i need to stream netflix,what are ram heavy tasks,64gb ram for streaming,how much ram does youtube use,best ram for gaming and streaming,how much ram for gaming and streaming reddit,how much ram do you need for editing,cpu for streaming,how much ram does minecraft need,how much ram do i need for streaming netflix,dedicated streaming pc specs,minimum specs for streaming laptop,can my pc stream test,amd equivalent to i5 4670,amd rx 570 graphics card.,minimum requirements for streaming on youtube,how much storage for streaming pc,how much ram is needed for video editing,how much ram do i need for streaming video,how much ram do i need for streaming movies,how much ram do i need for gaming,recommended ram for streaming video,do i need 32gb of ram for streaming,how much ram do i need for video editing,does ram help with streaming on twitch