Examples of SQL Transactions Example

Understanding SQL Transactions

SQL Transactions are crucial in ensuring that a series of operations in a database are executed reliably and maintain data integrity. A transaction is a sequence of one or more SQL operations that are treated as a single unit. If any operation within the transaction fails, the entire transaction can be rolled back to maintain consistency. Below are three diverse practical examples of SQL transactions.

Example 1: Bank Transfer Transaction

In the banking sector, transferring money between accounts requires a careful handling of transactions to prevent data inconsistency. This example demonstrates how to safely transfer funds from one account to another.

To ensure that the debit from one account and the credit to another are both successful, we use a transaction. If either operation fails, the transaction will roll back.

BEGIN TRANSACTION;

-- Step 1: Deduct amount from sender's account
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Step 2: Check if the balance is negative
IF (SELECT balance FROM accounts WHERE account_id = 1) < 0
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transaction failed: Insufficient funds.';
END
ELSE
BEGIN

    -- Step 3: Add amount to receiver's account
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
    COMMIT TRANSACTION;
END

Notes:

• This transaction ensures that either both updates succeed, or neither does.
• You can replace the amounts and account IDs based on your requirements.

Example 2: Order Processing Transaction

In an e-commerce application, processing an order involves multiple steps, including updating inventory and creating an order record. Using transactions ensures that all these steps are completed successfully before finalizing the order.

BEGIN TRANSACTION;

-- Step 1: Check inventory
DECLARE @currentStock INT;
SELECT @currentStock = stock FROM products WHERE product_id = 101;

IF @currentStock <= 0
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transaction failed: Out of stock.';
END
ELSE
BEGIN

    -- Step 2: Deduct stock
    UPDATE products SET stock = stock - 1 WHERE product_id = 101;

    -- Step 3: Create order record
    INSERT INTO orders (product_id, quantity, order_date) VALUES (101, 1, GETDATE());
    COMMIT TRANSACTION;
END

Notes:

• This transaction prevents overselling products by checking stock before updating.
• Modify the product_id and quantities as per your database schema.

Example 3: User Registration Transaction

When a new user registers on a website, multiple tables may need to be updated, such as user details and user roles. This example illustrates how to handle user registration safely using a transaction.

BEGIN TRANSACTION;

-- Step 1: Insert new user details
INSERT INTO users (username, password, email) VALUES ('newUser', 'securePassword', 'user@example.com');

-- Step 2: Get the new user's ID
DECLARE @newUserId INT;
SET @newUserId = SCOPE_IDENTITY();

-- Step 3: Assign default user role
INSERT INTO user_roles (user_id, role) VALUES (@newUserId, 'basic');

COMMIT TRANSACTION;

Notes:

• This example captures the new user’s ID immediately after inserting to ensure data integrity.
• Adjust the role assignments based on your user management system.

By using these examples of SQL transactions, you can better manage your database operations, ensuring data integrity and consistency across your applications.