What is a Transaction in SQL Server?
A transaction is a set of SQL statements that should be executed as one unit. That means a transaction ensures that either all the command succeeds or none of them. If one of the commands in the transaction fails, all the commands fail and any data that is modified in the database is rolled back. Transactions may consist of a single read, write, delete, or update operations or a combination of these.
How to implement Transaction Management in SQL Server?
Transaction processing involves three steps. First, we need to begin the transactions. Then we need to write the DML operations which we want to execute as a single unit. In the third step, we need to check for errors. If there is any error i.e. any of the DML statements fails, then roll back the transaction (any data that is modified in the database will be rollback) else commit the transaction so that the data is saved permanently to the database.
To manage the transaction in SQL Server, we have provided transaction control language (TCL). TCL provides the following four commands which we can use to implement transactions in SQL Server.
Begin Transaction: It indicates that the transaction is started.
Commit Transaction: It indicates that the transaction was completed successfully, and all the data manipulation operations performed since the start of the transaction are committed to the database and frees the resources held by the transaction.
Rollback Transaction: It indicates that the transaction was Failed and will roll back the data to its previous state.
Save Transaction: This is used for dividing or breaking a transaction into multiple units so that the user has a chance of roll backing a transaction up to a point or location.
Modes of the Transactions in SQL Server
SQL Server can operate three different transactions modes, and these are:
Autocommit Transaction mode is the default transaction for the SQL Server. In this mode, each T-SQL statement is evaluated as a transaction, and they are committed or rolled back according to their results. The successful statements are committed, and the failed statements are rolled back immediately
Implicit transaction mode enables to SQL Server to start an implicit transaction for every DML statement, but we need to use the commit or rolled back commands explicitly at the end of the statements
Explicit transaction mode provides to define a transaction exactly with the starting and ending points of the transaction
Examples
create schema xxx
create table xxx.FactSales
(SaleKey int identity(1,1) primary key,
SalesDate datetime default getdate(),
Amount int check(Amount >=0),
ProductKey int not null
)
Create table xxx.DimProduct
(ProductKey Int identity(1,1) primary key,
ProductName nvarchar(50)
)
ALTER TABLE [xxx].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimProduct] FOREIGN KEY([ProductKey])
REFERENCES [xxx].[DimProduct] ([ProductKey])
GO
insert into xxx.DimProduct(ProductName) values ('Product001'),('Product002')
insert into xxx.FactSales(Amount,ProductKey)
values (100,1)
Marked transactions in SQL Server
SQL Server allows us to mark and add a description to a specific transaction in the log files. In this way, we can generate a recovery point that is independent of the time. Such as, when an accidental data modification occurs in the database and we don’t know the exact time of the data modification, the data recovery effort can be taken a long time. For this reason, marked transactions can be a useful solution to find out the exact time of the data modifications. To create a marked transaction, we need to give a name to the transaction, and we also need to add WITH MARK syntax. In the following query, we will delete one row and we will also mark the modifications in the log file.
BEGIN TRANSACTION MyTran WITH MARK 'DeletingProductKey2'
DELETE FROM xxx.DimProduct WHERE ProductKey = 2
COMMIT TRANSACTION Mytran
SELECT * FROM msdb.dbo.logmarkhistory
The logmarkhistory table stores details about each marked transaction that have been committed and it is placed in the msdb database.
As we can see in the above image the logmarkhistory gives all details about the marked transaction. The following two options help to use marked transactions as a recovery point.
STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward
STOPBEFOREMARK rolls forward to the mark and excludes the marked transaction from the roll forward
Log history table can record just 1 modification inside a transaction. If there are multiple updates, insert statements inside one transaction, just the last one will be recorded.
Consider placing update-insert-delete inside multiple independent (can't be nested) transactions.
DECLARE @INSERT NVARCHAR(10) ='INSERT'
DECLARE @UPDATE NVARCHAR(10) ='UPDATE'
BEGIN TRAN MYTRAN WITH MARK 'Example'
INSERT INTO xxx.DimProduct(ProductName) VALUES ('Product003')
UPDATE xxx.DimProduct
SET ProductName ='DISCARD'
WHERE ProductKey = (SELECT MAX(ProductKey) FROM xxx.DimProduct)
COMMIT TRAN
SELECT * FROM msdb.dbo.logmarkhistory
Condition in transaction
The goal is to avoid duplicate product names in table. I have only two records so far.
If the record already exists in the table, transaction will be rolled back, else committed.
Since the record in the table already exists, the transaction will be rolled back.
DECLARE @ProductCount int
DECLARE @TRANSACTIONNAME NVARCHAR(50) = 'MyTransaction'
BEGIN TRAN @TRANSACTIONNAME
INSERT INTO xxx.DimProduct(ProductName) VALUES ('Product001')
SELECT @ProductCount = COUNT(ProductName) FROM xxx.DimProduct WHERE ProductName ='Product001'
IF (@ProductCount > 1) --PREVENTING ADDIND DUPLICATE RECORDS
BEGIN
ROLLBACK TRAN @TRANSACTIONNAME
PRINT 'Product001 ALREADY EXISTS IN THE DATABASE'
END
ELSE
BEGIN
COMMIT TRAN @TRANSACTIONNAME
PRINT 'Product001 ADDED TO THE DATABASE'
END
USING ERROR HANDLING
The TRY CATCH construct allows you to handle exceptions (error messages) in SQL Server.
Server retuned an exception when trying to update INT data type with NVARCHAR
using TRY CATCH
The TRY CATCH construct works with insert
...trying to insert varchar value into int data type
The TRY CATCH construct can't handle certain things
NESTED TRANSACTIONS
When you try to rollback an INNER TRANSACTION, you will get an error.
Transaction TRAN2 is still opened.
You can rollback an inner transaction under some defined condition by creating a save point. In this example there is no condition defined, just rolling back the transaction using the save point. This prevents the server from throwing an exception. The highlighted transaction has been rolled back; inserts ran.
BEGIN TRAN TRAN1
INSERT INTO xxx.DimProduct(ProductName) VALUES ('XXX')
SAVE TRAN TRAN2
UPDATE xxx.DimProduct SET ProductName ='CCC'
WHERE ProductKey = (SELECT MAX(ProductKey) FROM xxx.DimProduct)
ROLLBACK TRAN TRAN2
INSERT INTO xxx.DimProduct(ProductName) VALUES ('ZZZ')
COMMIT TRAN TRAN1
before transaction
RESULT: after transaction
ProductKey ProductName
1 Product001
2 Product002
3 XXX
4 ZZZ
This table contains the current state. Now I am going to run a transaction with 2 SAVE POINTS.
Everything from row 7 to 8 have been rolled back, means just the second save point was affected by rollback tran command.
As I said, SAVE POINTS or SAVE TRANSACTION commands are used with condition and are intended for stored procedures.
In this example I am going to insert one more product. I will have four products in total. After that there will be impossible to insert more products into the table. SAVE TRANSACTION command will return all attempts back.
In this example I am going to insert new records into FactSales table. I will be inserting Amount and ProductName.
When an existing product will be inserted, nothing will happen in DimProduct table. When a new product will be inserted into FactSales table, new product will be added into DimProduct table.
I will be using SAVE TRANSACTION command to insert new products into DimProduct table. If the product already exists in there, transaction will be rolled back.
First procedure
1. inserts new products into DimProduct (transaction will be rolled back if that product already exists)
2. passes the ProductKey to FactSales table.
CREATE PROC spNewProducts (@NewProductName nvarchar(50))
as
BEGIN
DECLARE @Productkey int
SAVE TRAN XX
--INSERT NEW ProductName into DimProduct
INSERT INTO xxx.DimProduct(ProductName) VALUES(@NewProductName)
--IF ProductName ALREADY EXISTS IN DimProduct ROLLBACK TRANACTION
IF (SELECT COUNT(ProductName) FROM XXX.DimProduct
WHERE ProductName = @NewProductName)>1
BEGIN
ROLLBACK TRAN XX
END
ELSE
BEGIN --RETURN ProductKey FOR FACTTABLE
SELECT @Productkey = ProductKey from xxx.DimProduct WHERE ProductName = @NEWProductName
RETURN @Productkey
END
END
Second procedure depends on the first one. After the ProductKey is returned, inserts
the Amount and ProductKey into FactSales table.
CREATE PROC spInsertSales (@Amount int, @ProductName nvarchar(50))
AS
BEGIN
BEGIN TRAN
DECLARE @NewProductName NVARCHAR(50)
SET @NewProductName = @ProductName
EXECUTE @ProductName = spNewProducts @NewProductName
DECLARE @ProductKey INT
SELECT @ProductKey = ProductKey FROM XXX.DimProduct where ProductName = @NewProductName
INSERT INTO xxx.FactSales(Amount,ProductKey) VALUES(@Amount,@ProductKey)
COMMIT TRAN
END
Inserting an existing product. New record in FactSales table has been added.
Inserting a new Product, new records have been added into both tables.
it is important that the DimProduct table will be modified first because there is a 1-many relationship between these two tables.
Comments