SQL Transactions and ACID Properties

Implement a Bank Transfer Transaction

Write SQL statements to transfer funds between two accounts using a transaction that ensures ACID properties.

Expected output: Both accounts are updated atomically, or no changes occur if an error happens.

Hint: Use BEGIN TRANSACTION, UPDATE statements, and COMMIT or ROLLBACK.

Experiment with Isolation Levels

Test different isolation levels in your database and observe how concurrent transactions behave.

Expected output: Understand how isolation levels affect visibility and locking.

Hint: Use SET TRANSACTION ISOLATION LEVEL and run concurrent queries.

Atomicity guarantees that all operations within a transaction complete successfully as a unit, or none of them are applied, ensuring no partial changes occur.

Isolation ensures that concurrent transactions do not interfere with each other, preventing issues like dirty reads, non-repeatable reads, and phantom reads depending on the isolation level.

Durability ensures that once a transaction commits, its changes are permanently saved and will survive system crashes or failures.