T-SQL Transactions with practical examples

T-SQL transactions tutorial with practical examples using the BEGIN, COMMIT and ROLLBACK.

T-SQL transactions are a sequence of one or more operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, known as the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.T-SQL supports the use of transactions, which are important for preserving database integrity by ensuring that batches of SQL operations execute completely or not at all. They are used to make sure that work is committed (completed) or rolled back (undone) if errors occur in the batch.

1. Understanding T-SQL Transactions and the ACID concept

A T-SQL transaction is a single unit of work that consists of one or more operations. It’s an essential concept in SQL Server, ensuring that all SQL commands within a single transaction are executed successfully as a group. If any command within the transaction fails, the entire transaction fails, and no database updates are applied.

A transaction has a definite beginning and end. It starts when the first T-SQL command is executed and can end in one of two ways: either by being committed, which means all changes are saved to the database, or by being rolled back, which means all changes are discarded. Transactions follow the ACID properties, a critical set of principles for database transactions:

Atomicity: Atomicity ensures that a transaction is treated as a single, indivisible unit of work. It either completes in its entirety or not at all. If an error occurs during the transaction, all changes are rolled back, and the database remains unchanged.
Consistency: Consistency guarantees that a transaction brings the database from one valid state to another. The database integrity constraints must be maintained for all transactions. For example, if a transaction attempts to violate a database constraint, it will be rolled back.
Isolation: Isolation ensures that each transaction is executed independently of others. This means that the execution of one transaction does not affect the execution of other transactions.
Durability: Durability ensures that once a transaction has been committed, its changes remain in the database even in the event of system failure.

Understanding transactions and the ACID properties can help you design and implement better and more reliable database operations in T-SQL. They are critical for maintaining data integrity and ensuring the reliable execution of operations in a database.

2. Transaction Control Language (TCL) Commands in T-SQL

The 4 following keywords are the key Transaction Control Language commands:

BEGIN TRANSACTION: This marks the start of the transaction block. The syntax is BEGIN TRAN [transaction_name].
COMMIT: This command saves the transaction permanently in the database. The syntax is COMMIT TRAN [transaction_name].
ROLLBACK: This command undoes all the transaction statements and changes. The syntax is ROLLBACK TRAN [transaction_name].
SAVE TRANSACTION: This sets a savepoint within a transaction, which can be used to roll back part of a transaction. The syntax is SAVE TRAN [savepoint_name].

In addition to transaction, to improve an d organize code and performance, you can use SQL Server indexes.

Create a SQL Server index

3. Practical Example Using a T-SQL Transaction

Here’s an example of a transaction using your Sales table. We’re inserting a new record, but we want to make sure that both Price and Sales are updated together or not at all to maintain data integrity.

BEGIN TRAN; -- Start the transaction

-- Try to insert a new record
INSERT INTO Sales (CustomerID, MonthID, YearID, Qty, Price, Sales, ProductID, ProductName) 
VALUES (9, N'1', N'2023', 10.00, 1000.00, 10000.00, 20, N'ProductX');

-- Update the Price and Sales for the new record
UPDATE Sales 
SET Price = 1200.00, Sales = 12000.00 
WHERE CustomerID = 9 AND ProductID = 20 AND YearID = '2023';

COMMIT; -- If no error so far, commit the transaction

4. Handling Errors using The Rollback Operation in T-SQL

If an error occurs during the INSERT or UPDATE operations, you can issue a ROLLBACK command to undo all operations within this transaction:

ROLLBACK; -- Rollback the transaction because of an error

The ROLLBACK command undoes all the changes made in the current transaction back to the point of the last BEGIN TRAN statement.

5. Advanced Usage with T-SQL Savepoints

You can also use a SAVE TRANSACTION command to set a savepoint within a transaction. This allows you to roll back part of a transaction:

BEGIN TRAN;

-- Try to insert a new record
INSERT INTO Sales (CustomerID, MonthID, YearID, Qty, Price, Sales, ProductID, ProductName) 
VALUES (10, N'1', N'2023', 10.00, 1000.00, 10000.00, 21, N'ProductY');

-- Save a transaction savepoint
SAVE TRAN SP1;

-- Try to update the Price and Sales for the new record
UPDATE Sales 
SET Price = 1200.00, Sales = 12000.00 
WHERE CustomerID = 10 AND ProductID = 21 AND YearID = '2023';

-- If an error occurs during the update operation, rollback to the savepoint
ROLLBACK TRAN SP1;

COMMIT;

In this example, if an error occurs during the UPDATE operation, the ROLLBACK TRAN SP1 command undoes all the changes made since the SAVE TRAN SP1 statement, but it won’t affect the insert operation.

Conclusion on SQL Server transactions and their concepts

Understanding transactions and proper usage of BEGIN, COMMIT, ROLLBACK, and SAVE commands are crucial for maintaining the ACID properties in your SQL Server database operations. To wrap up, in this comprehensive tutorial, we explored various aspects of T-SQL subqueries and transactions. Subqueries and transactions form an essential part of T-SQL as they help manage data efficiently and effectively, maintaining the integrity and reliability of operations. Subqueries enable us to write compact and more readable queries, while transactions ensure the database’s consistency, following the ACID principles.

In the real world, databases and operations are often far more complex than the examples provided. However, with the concepts and skills learned in this tutorial, you are well-equipped to handle those challenges. Always remember the ACID principles when working with transactions and try to leverage subqueries when possible to make your queries more efficient and readable.

To go further, let’s dive into a more classical and widely used topic, the T-SQL subqueries.