Transactions in
SQL server
Presented by: Grishma
Shrestha
Introduction
Definition:
• A transaction represents a logical unit of work in a database that contains
one or more operations executed together, ensuring that all operations
complete successfully or none at all.
Importance
• Transactions are essential for maintaining data integrity and
consistency, coordinating user access, and preventing data
anomalies in multi-user environments.
ACID Properties
Atomicity Consistency Isolation Durability
This property ensures Transactions must This property Once a transaction has
that a transaction is an transition a database determines how been committed, its
all-or- nothing from one valid state to transaction integrity is effects are permanent,
operation; either all of another valid state, visible to other even in the event of a
its modifications are preserving the defined transactions, ensuring system failure, thus
performed, or none rules and constraints, that concurrent ensuring that
are, leaving the thus ensuring that no executions do not completed
system unchanged in invalid data is ever interfere with each transactions can be
the event of a failure. written during the other's operations, relied upon to persist.
process. providing a virtually
private workspace.
Types of Transaction
Implicit Explicit Distributed
These transactions are Explicit transactions are Involves multiple databases
automatically managed by SQL defined and controlled by the or servers where a single
Server, beginning with the user, allowing for specific transaction may span
execution of certain commands commands to initiate and multiple data sources,
and requiring no explicit manage the transaction necessitating coordination to
handling from the user unless lifecycle, providing greater ensure ACID properties are
completed or rolled back flexibility and control over preserved across all
manually. complex operations. involved systems.
Transaction control statements
Begin Commit Rollback Savepoint
This statement marks The COMMIT Used to undo all Creates a point within a
the starting point of a statement indicates that operations performed transaction that allows
transaction, allowing all operations within within the transaction you to roll back to a
the database to track the transaction have since its initiation, specific location
changes made during been executed reverting the database without affecting the
the transaction's successfully, making all back to the state before entire transaction,
execution until it's changes permanent the transaction began, offering a more
either committed or within the database. particularly useful in granular
rolled back. error handling. recovery option.
Locking Mechanism
Purpose:
Locking is essential to prevent concurrent transactions
from conflicting, ensuring data integrity by controlling
access to resources during a transaction's execution
phase.
Types of Locking:
SQL Server employs different locks-including shared,
exclusive, and update locks-each serving specific
functions to balance concurrency and consistency.
Deadlock and resolution
A deadlock occurs when two or more transactions
hold locks that the other transactions need,
necessitating resolution methods like timeout,
deadlock detection, or priority attribution.
Maximizing Integrity & Consistency
Minimize Use appropriate Avoid long
Transaction running
01 Scope 02 isolation
levels 03 transactions
Narrow the range of Select isolation levels Lengthy transactions
operations Included in a based on the specific consume resources and can
transaction to reduce the requirements of data lead to increased locking
impact on locks and consistency and contention, so they should
improve concurrency by performance needs per be avoided in favor of
holding resources for transaction, balancing more frequent,
shorter intervals. trade-offs efficiently. smaller transactions
CREATE DATABASE Transs;
USE Transs;
CREATE TABLE product (
product_id INT PRIMARY KEY IDENTITY(1,1),
product_name VARCHAR(100) NOT NULL,
stock INT NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
CREATE TABLE users (
user_id INT PRIMARY KEY IDENTITY(1,1),
username VARCHAR(50) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY IDENTITY(1,1),
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
order_date DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO product (product_name, stock, price)
VALUES
('Laptop', 5, 1500.00),
('Smartphone', 10, 800.00);
INSERT INTO users (username, balance)
VALUES
('Lana', 2000.00),
('Evans', 1000.00);
--Implicit transaction
BEGIN TRANSACTION;
UPDATE product
SET stock = stock - 1
WHERE product_id = 1 AND stock > 0;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK;
PRINT 'Transaction failed: Out of stock.';
RETURN;
END
-- Explicit transaction
BEGIN TRANSACTION;
UPDATE users
SET balance = balance - 1500
WHERE user_id = 1 AND balance >= 1500;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK;
PRINT 'Transaction failed: Insufficient balance.';
RETURN;
END
COMMIT;
PRINT 'Transaction successful: Order placed.';
T h a n k
Yo u !
