T-SQL – Transactions

Transaction is a part of work recognized as a single unit.
You can commit transaction – save all changes, or rollback transaction cancel all changes (i.e. in case of an error).

Simple transation:

BEGIN TRANSACTION
INSERT INTO Production.Categories(categoryname, description) VALUES ('Category A', 'My category A');
COMMIT

You can list all transactions:

SELECT * FROM sys.dm_tran_active_transactions;

Transactions can be nested. Only last commit really commits the transaction. Always first rollback rollbacks all nested transactions.

In the following example first T1 inserts single category and commits. Nested transaction T2 raises an error while inserting the data. Due that fact transactions are rolledback and nothing is saved (even the first insert).

BEGIN TRANSACTION T1
	BEGIN TRANSACTION T2
		INSERT INTO Production.Categories(categoryname, description) VALUES ('Category c', 'My category c');
	COMMIT TRAN T2;
	BEGIN TRY
		INSERT INTO Production.Products VALUES('My product c', NULL, NULL, -1, 1); -- will raise an error
		COMMIT TRAN T1;
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION T1; -- rollback after error will cause that nothing is commited
	END CATCH

Following example shows how to use @@TRANCOUNT and XACT_STATE().
@@TRANCOUNT informs how many nested transactions exist.
XACT_STATE() informs about the state of the transaction (0 – no transaction, 1 transaction can be commited, -1 transaction cannot be commited).

SET XACT_ABORT ON;
SELECT 'Tran count: ' + CAST(@@TRANCOUNT AS varchar(10)); -- 0 => there is no transaction at all
SELECT 'Tran state: ' + CAST(XACT_STATE() AS varchar(10)); -- 0 => there is no transaction at all
BEGIN TRANSACTION T1
	SELECT 'Tran count: ' + CAST(@@TRANCOUNT AS varchar(10)); -- 1 => there is one transaction
	SELECT 'Tran state: ' + CAST(XACT_STATE() AS varchar(10)); -- 1 => there is transaction and can be commited
	BEGIN TRANSACTION T2
		SELECT 'Tran count: ' + CAST(@@TRANCOUNT AS varchar(10)); -- 2 => there are two nested transactions
		INSERT INTO Production.Categories(categoryname, description) VALUES ('Category c', 'My category c');
	COMMIT TRAN T2;
	BEGIN TRY
		INSERT INTO Production.Products VALUES('My product c', NULL, NULL, -1, 1); -- will raise an error
		COMMIT TRAN T1;
	END TRY
	BEGIN CATCH
		SELECT 'Tran count: ' + CAST(@@TRANCOUNT AS varchar(10)); -- 1 => there is one transaction again
		SELECT 'Tran state: ' + CAST(XACT_STATE() AS varchar(10)); -- -1 => transaction cannot be commited
		ROLLBACK TRANSACTION T1; -- rollback after error will cause that nothing is commited
	END CATCH

Savepoints – are points which mark transaction state. You can rollback transaction to the savepoint and then not everything will be rolled back – changes before savepoint will be persisted.

BEGIN TRANSACTION T1
	BEGIN TRANSACTION T2
		INSERT INTO Production.Categories(categoryname, description) VALUES ('Category c', 'My category c');
	COMMIT TRAN T2;
	BEGIN TRY
		INSERT INTO Production.Products VALUES('My product c', NULL, NULL, -1, 1); -- will raise an error
		COMMIT TRAN T1;
		SAVE TRAN SP1;
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION SP1; -- rollback to savepoint SP1 will cause that first insert is persisted (despite the rollback)
	END CATCH
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s