• Marek Vavrovic

SQL Server Transactions



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.


  1. Begin Transaction: It indicates that the transaction is started.

  2. 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.

  3. Rollback Transaction: It indicates that the transaction was Failed and will roll back the data to its previous state.

  4. 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:

  1. 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

  2. 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

  3. 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.

21 views0 comments