5.2 Transactions and Concurrency

 

5.2 Transactions and Concurrency

In SQL Server, transactions are used to ensure the consistency and integrity of the database. Transactions are a series of one or more SQL statements that are executed as a single unit of work. The ACID properties (Atomicity, Consistency, Isolation, Durability) ensure that transactions are processed reliably.

5.2.1 Introduction to Transactions

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions are used to ensure the consistency and integrity of the database.

Example:

-- Start a transaction
BEGIN TRANSACTION;
-- SQL statements here
-- Commit the transaction
COMMIT;

5.2.2 Concurrency Control

Concurrency control ensures that multiple transactions can run concurrently without compromising the consistency of the database. SQL Server provides various isolation levels to control the visibility of changes made by one transaction to other transactions.

Example:

-- Set the isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Start a transaction
BEGIN TRANSACTION;
-- SQL statements here
-- Commit the transaction
COMMIT;

5.2.3 Transaction Management Commands

SQL Server provides commands to manage transactions:

Example:

-- Start a transaction
BEGIN TRANSACTION;
-- SQL statements here
-- Check for errors
IF @@ERROR <> 0
    ROLLBACK; -- Rollback the transaction if an error occurs
ELSE
    COMMIT; -- Commit the transaction if successful

5.2.4 Query Output

Let's simulate a scenario where two transactions are trying to update the same record concurrently.

Example:

-- Session 1

BEGIN TRANSACTION;

-- Update the balance in the accounts table

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

-- Wait for a while to simulate concurrent access

WAITFOR DELAY '00:00:05';

-- Commit the transaction

COMMIT;

-- Session 2
BEGIN TRANSACTION;
-- Update the balance in the accounts table
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1;
-- Commit the transaction
COMMIT;

Session	Step	SQL Statement	Resulting Balance
1	1	BEGIN TRANSACTION;	-
1	2	UPDATE Accounts SET Balance = Balance - 100 ...	-
2	1	BEGIN TRANSACTION;	-
2	2	UPDATE Accounts SET Balance = Balance + 100 ...	-
1	3	WAITFOR DELAY '00:00:05';	-
2	3	COMMIT;	1100
1	4	COMMIT;	900

In this example, we have two sessions (Session 1 and Session 2) concurrently updating the balance in the Accounts table. The table format helps visualize the sequence of steps and the resulting balance after each step.

This is a simple representation, and in a real-world scenario, you would use appropriate error handling and isolation levels based on your application's requirements.