SQL Interview Questions

A transaction in SQL is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE, or SELECT) executed as a single unit of work. A transaction is designed to ensure data integrity and consistency, and it either completes fully or not at all. Transactions are essential in maintaining the consistency and reliability of databases, particularly in multi-user environments.

Key Features of Transactions

1. Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back, and no changes are applied to the database.
2. Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining database invariants.
3. Isolation: Ensures that transactions are isolated from one another until they are completed, preventing concurrent transactions from interfering with each other.
4. Durability: Ensures that once a transaction is committed, the changes are permanent and will survive system failures.

ACID Properties

The ACID properties are a set of principles that guarantee reliable transaction processing. They stand for Atomicity, Consistency, Isolation, and Durability.

1. Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. It guarantees that all operations within a transaction are either fully completed or fully rolled back.

• Example: In a banking system, transferring money from one account to another involves debiting one account and crediting another. Atomicity ensures that both operations occur successfully, or neither occurs at all.

2. Consistency

Consistency ensures that a transaction brings the database from one valid state to another, preserving the integrity and correctness of the data. All defined rules, constraints, and triggers are maintained.

• Example: If a database enforces a constraint that account balances cannot be negative, a transaction must respect this rule and cannot result in an account having a negative balance.

3. Isolation

Isolation ensures that transactions execute independently of one another. The intermediate states of a transaction are not visible to other transactions, preventing interference and ensuring correctness.

• Example: If two transactions are updating the same record simultaneously, isolation ensures that each transaction's operations are isolated, avoiding potential conflicts or data corruption.

4. Durability

Durability ensures that once a transaction is committed, the changes are permanent and will survive system crashes or failures. The results of the transaction are stored permanently.

• Example: After a banking transaction commits the transfer of funds, the changes must be saved permanently, ensuring that even if the system crashes, the transferred funds are not lost.

Example of a Transaction in SQL

Here is an example demonstrating a transaction in SQL, using the ACID properties:

BEGIN TRANSACTION;

-- Atomicity and Consistency: Ensure both operations succeed or fail together
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Check for consistency
IF @@ERROR <> 0
BEGIN
    -- Rollback if any error occurs, ensuring atomicity
    ROLLBACK TRANSACTION;
    PRINT 'Transaction failed. Changes rolled back.';
END
ELSE
BEGIN
    -- Commit the transaction, ensuring durability
    COMMIT TRANSACTION;
    PRINT 'Transaction succeeded. Changes committed.';
END

Conclusion

Transactions are fundamental to maintaining the integrity, consistency, and reliability of a database. The ACID properties—Atomicity, Consistency, Isolation, and Durability—ensure that transactions are processed reliably and accurately, even in the presence of system failures or concurrent access. Understanding and implementing transactions according to these principles is essential for robust database management.