Lecture 8: Transactions ACID in SQL

Lecture 8: Transactions ACID in SQL#

Learning Objectives#

By the end of this lecture, students will be able to:

  • Understand the ACID properties and their importance in database transactions.

  • Identify scenarios where the all-or-nothing principle of atomicity is violated.

  • Implement transactions in SQL to ensure atomicity, consistency, isolation, and durability.

  • Use constraints to maintain data integrity.

  • Apply different isolation levels to balance performance and consistency.

  • Handle errors and ensure data consistency using transactions and error handling techniques.

Connecting to the Database#

# load the ipython-sql extension
%load_ext sql

import json
import urllib.parse

with open('data/credentials.json') as f:
    login = json.load(f)
    
username = login['user']
password = urllib.parse.quote(login['password'])
host = login['host']
port = login['port']

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Let’s connect to your dvdrental database

%sql postgresql://{username}:{password}@{host}:{port}/dvdrental

'Connected: postgres@dvdrental'

All-or-Nothing Principle#

Introduction to SQL Transactions#

A transaction in SQL is a sequence of one or more SQL operations that are executed as a single unit of work. Transactions are fundamental to ensuring data integrity and consistency in a database. They allow multiple operations to be grouped together so that they either all succeed or all fail, maintaining the database in a consistent state.

Transactions are particularly important in scenarios where multiple operations need to be performed atomically, such as transferring money between bank accounts or updating related records in different tables. SQL transactions are governed by the ACID properties, which ensure that transactions are processed reliably and predictably.

Scenario

Consider a scenario where we are transferring money between two bank accounts. The transfer involves two operations:

  1. Debiting the amount from the source account.

  2. Crediting the amount to the destination account.

If the system crashes or an error occurs after the debit operation but before the credit operation, the transaction will be left in an inconsistent state, violating the all-or-nothing principle of atomicity.

Sample Data#

%%sql
DROP TABLE IF EXISTS accounts;

-- Create the accounts table
CREATE TABLE accounts (
    account_id VARCHAR(10) PRIMARY KEY,
    balance DECIMAL(10, 2) 
);

-- Insert sample data into the accounts table
INSERT INTO accounts (account_id, balance) VALUES
    ('A', 1000.00),
    ('B', 500.00)
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.
2 rows affected.
account_id balance
A 1000.00
B 500.00

Example Query#

-- Debit from Account A
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- Simulate a system crash or error before the credit operation
-- (In a real scenario, this could be a system failure, network issue, etc.)

-- Credit to Account B
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

Explanation

In this example:

  • The first UPDATE statement debits $100 from Account A.

  • Before the second UPDATE statement can credit $100 to Account B, a system crash or error occurs.

As a result:

  • Account A has been debited $100.

  • Account B has not been credited $100.

The database is left in an inconsistent state, violating the atomicity principle. This example demonstrates a violation of the all-or-nothing principle of atomicity. In a real-world scenario, such a situation could arise due to various reasons such as system crashes, network issues, or application errors.

To prevent this, transactions should be used to ensure that either both operations succeed or neither does, maintaining the consistency and integrity of the database.

ACID Properties#

1. Atomicity#

  • Definition: Atomicity ensures that a transaction is treated as a single unit of work. Either all operations within the transaction are completed successfully, or none of them are.

  • Example: Consider a bank transfer where money is moved from Account A to Account B. The operation takes place in two steps:

    • Debit money from account A

    • Credit money to account B

Atomicity ensures that if a system failure occurs between the debit from Account A and the credit to Account B, or if either operation is invalid for any reason, the entire transaction will be aborted and none of the operations will be processed.

2. Consistency#

  • Definition: Consistency ensures that a transaction brings the database from one valid state to another, maintaining database invariants.

  • Example: If a database constraint requires that the balance must always be larger than zero, a transaction that violates this constraint will be rolled back.

3. Isolation#

  • Definition: Isolation ensures that the operations of a transaction are isolated from other transactions. Intermediate states of a transaction are not visible to other transactions.

  • Example: Consider a scenario where two transactions are running concurrently in a database system. Both transactions are attempting to update the same record in the inventory table, which tracks the stock levels of products in a warehouse.

Incorrect approach

  • Transaction 1 reads the stock level as 50 and decrements it by 10, intending to set it to 40.

  • Transaction 2 reads the stock level as 50 and increments it by 20, intending to set it to 70.

If these transactions are not properly isolated, the final stock level could end up being either 40 or 70, depending on the order of execution, leading to inconsistent data.

The correct approach would be:

  • Transaction 1 reads the stock level as 50 and decrements it by 10, setting it to 40.

  • Transaction 2 reads the updated stock level as 40 (after Transaction 1 commits) and increments it by 20, setting it to 60.

4. Durability#

  • Definition: Durability ensures that once a transaction has been committed, it remains committed even in the case of a system failure.

  • Example: After a successful bank transfer, the changes to the account balances are permanently recorded in the database, even if the system crashes immediately afterward.

Transactions#

To ensure atomicity, we should use a transaction to group the operations together. If any part of the transaction fails, the entire transaction should be rolled back.

Implementing ACID Transactions in SQL#

  1. Starting a Transaction

    • Use the BEGIN statement to start a new transaction.

    BEGIN;

  2. Committing a Transaction

    • Use the COMMIT statement to save all changes made during the transaction.

    COMMIT;

  3. Rolling Back a Transaction

    • Use the ROLLBACK statement to undo all changes made during the transaction.

    ROLLBACK;

Example: Bank Transfer#

Before transaction

%%sql

SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 1000.00
B 500.00

Transaction

%%sql

-- Start a transaction
BEGIN;

-- Debit from Account A
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- Credit to Account B
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

-- Commit the transaction
COMMIT;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
1 rows affected.
1 rows affected.
Done.

[]

After transaction

%%sql
-- Display the updated balances
SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 900.00
B 600.00

Using variables in transactions#

What if we want to incorporate a variable name transaction_amount so we don’t have to hard-code the transaction amount in multipline lines?

The DO $$ ... END $$ block in PostgreSQL is a versatile tool for executing procedural code within a SQL script. It allows for complex operations, variable declarations, control structures, and exception handling, making it ideal for tasks that require more than simple SQL statements.

Here is a typical structure:

DO $$
DECLARE
    -- Variable declarations
BEGIN
    -- Procedural code
EXCEPTION
    WHEN OTHERS THEN
        -- Exception handling (optional)
END $$;

Explanation of Each Part

  1. DO $$ ... END $$:

    • The DO keyword initiates the block.

    • The $$ delimiters define the start and end of the block.

    • The END $$ marks the end of the entire DOblock.

  2. DECLARE:

    • This section is optional and is used to declare variables that will be used within the block.

  3. BEGIN ... END:

    • The BEGIN keyword starts the main body of the procedural code.

    • The END keyword ends the main body of the procedural code.

  4. EXCEPTION:

    • This section is optional and is used to handle exceptions that may occur during the execution of the block.

Example: Depositing money to an account#

Before transaction

%%sql

SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 900.00
B 600.00

Transaction

%%sql

-- Declare the transaction amount
DO $$
DECLARE
    deposit_amount DECIMAL(10, 2) := 100;

BEGIN
-- add money to account A
    UPDATE accounts
    SET balance = balance + deposit_amount
    WHERE account_id = 'A';

END $$;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

After transaction

%%sql
SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
B 600.00
A 1000.00

Example: Bank transfer transaction#

Now, let’s try to re-write the transaction using the DO $$ ... END $$ block

Before transaction

%%sql

SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
B 600.00
A 1000.00 
%%sql
-- Start the transaction
BEGIN;

-- Declare the transaction amount
DO $$
DECLARE
    transaction_amount DECIMAL(10, 2) := 100;

BEGIN
-- Debit from Account A
    UPDATE accounts
    SET balance = balance - transaction_amount
    WHERE account_id = 'A';

    -- Credit to Account B
    UPDATE accounts
    SET balance = balance + transaction_amount
    WHERE account_id = 'B';

    -- Commit the transaction
    COMMIT;
END $$;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.

[]

Summary

  • BEGIN: Starts a new transaction.

  • DO $$ ... END $$: Executes a block of procedural code.

  • DECLARE: Declares variables used within the procedural block.

  • UPDATE: Performs debit and credit operations on the accounts.

  • COMMIT: Commits the transaction, making all changes permanent.

%%sql
SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 900.00
B 700.00

Example: Transaction with ROLLBACK#

Let’s try another example with ROLLBACK. In this example, we will try to send an amount that exceeds our balance. The idea here is that if the transaction failed, it should undo all the changes made during the transaction.

Before transaction

%%sql

SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 900.00
B 700.00

Transaction

%%sql

-- Start the transaction
BEGIN;

-- Declare variables
DO $$
DECLARE
    transaction_amount DECIMAL(10, 2) := 10000;
    account_a_balance DECIMAL(10, 2);
BEGIN
    -- Debit from Account A
    UPDATE accounts
    SET balance = balance - transaction_amount
    WHERE account_id = 'A';

    -- Credit to Account B
    UPDATE accounts
    SET balance = balance + transaction_amount
    WHERE account_id = 'B';

    -- Check for consistency
    SELECT balance INTO account_a_balance FROM accounts WHERE account_id = 'A';
    IF account_a_balance >= 0 THEN
        -- Commit the transaction if consistent
        COMMIT;
    ELSE
        -- Rollback the transaction if inconsistent
        ROLLBACK;
    END IF;
END $$;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.

[]

After transaction

%%sql 
SELECT * FROM accounts;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.
account_id balance
A 900.00
B 700.00
Contents