In SQL Server, a transaction is a sequential set of actions (such statements or queries) carried out as a single task. Data in a database may be read, written, updated, or deleted throughout these activities. Transactions that adhere to the ACID characteristics guarantee data integrity.
- Atomicity: Ensures that a transaction is treated as a single unit. Either all operations in the transaction are completed successfully, or none are applied, maintaining the "all-or-nothing" principle.
- Consistency: Guarantees that a transaction transforms the database from one valid state to another, adhering to all defined rules, constraints, and relationships.
- Isolation: Ensures that concurrent transactions do not interfere with each other, maintaining data integrity as if transactions were executed sequentially.
- Durability: Ensures that once a transaction is committed, its changes are permanently recorded in the database, even in the event of a system failure.
Transaction Control
The following are the commands used to control transactions.
- BEGIN TRANSACTION: Marks the start of a transaction. All subsequent operations will be part of this transaction until it is committed or rolled back.
- COMMIT: Saves all the changes made during the transaction permanently to the database. Once committed, the changes cannot be undone.
- ROLLBACK: Reverts all changes made during the transaction to their state at the start of the transaction, effectively canceling the transaction.
- SAVEPOINT: Creates a checkpoint within a transaction. This allows rolling back a transaction to a specific point without undoing the entire transaction.
- RELEASE SAVEPOINT: Deletes a previously defined SAVEPOINT. Once released, SAVEPOINT can no longer be used for rollback.
- SET TRANSACTION: Configures a transaction with specific properties, such as setting it to read-only or read/write, or associating it with a specific rollback segment.
Types of Transactions
- Implicit Transactions
- Automatically initiated by the database system when specific commands (e.g., INSERT, DELETE, UPDATE) are executed.
- The transaction remains active until explicitly committed or rolled back by the user.
- Explicit Transactions
- Manually initiated and controlled by the user.
- Typically defined using BEGIN TRANSACTION, followed by COMMIT or ROLLBACK to either save or undo changes.
- Autocommit Transactions
- The default transaction mode in most SQL systems.
- Each individual SQL statement is automatically committed if it executes successfully. No explicit commands are needed to commit or rollback.
- Savepoints
- Checkpoints within a transaction that allow partial rollbacks.
- Useful for rolling back a specific part of a transaction without undoing the entire
Basic Transaction Syntax
Explicit Transaction Example
BEGIN TRANSACTION;
-- Deduct from one account
UPDATE EmpSalary_int
SET Salary = Salary - 100
WHERE EmpID = 1;
-- Add to another account
UPDATE EmpSalary_int
SET Salary = Salary + 100
WHERE EmpID = 2;
-- Commit the transaction
COMMIT;
Using ROLLBACK
BEGIN TRANSACTION;
UPDATE products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 5;
-- Simulating an error
IF @@ERROR <> 0
BEGIN
ROLLBACK;
PRINT 'Transaction failed and was rolled back.';
END
ELSE
BEGIN
COMMIT;
PRINT 'Transaction completed successfully.';
END
Failed transaction
Savepoints for Partial Rollbacks
BEGIN TRANSACTION;
-- Step 1
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 1, GETDATE());
SAVE TRANSACTION SavePoint1;
-- Step 2
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (101, 2, 5);
-- Rollback to SavePoint1 if needed
ROLLBACK TRANSACTION SavePoint1;
-- Commit remaining operations
COMMIT;
Here, we can see in the second table that order_details data is not saved because we have set rollback savepoint1.
TRY...CATCH Example
BEGIN TRY
BEGIN TRANSACTION;
UPDATE EmpSalary_int
SET Salary = Salary - 100
WHERE EmpID = 1;
UPDATE EmpSalary_int
SET Salary = Salary + 'null'
WHERE EmpID= 2;
COMMIT;
PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
ROLLBACK;
PRINT 'An error occurred. Transaction rolled back.';
END CATCH;
Failed transaction
Conclusion
In SQL, transactions are sequences of operations performed as a single logical unit of work, ensuring data consistency and integrity. A transaction follows the ACID properties: Atomicity (all-or-nothing execution), Consistency (ensures data validity), Isolation (independence of concurrent transactions), and Durability (changes persist after completion). Transactions are crucial for managing database operations reliably and are typically controlled with commands like BEGIN, COMMIT, and ROLLBACK.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
