Unlocking Data Integrity: Transactions with START TRANSACTION in MariaDB

What it Does

• START TRANSACTION (also written as BEGIN) marks the beginning of a group of SQL statements that will be treated as a single unit.

How Transactions Work

• With START TRANSACTION, all subsequent SQL statements are queued up until you tell the database what to do with them. You have two options:

  • Use COMMIT to finalize the transaction and make all changes permanent.
  • Use ROLLBACK to undo all changes within the transaction, like if there were errors or unexpected outcomes.

• Imagine you're updating a bank account balance. You want to withdraw $50 and then deposit $20. This should be an atomic operation, meaning either both actions happen or neither does. Transactions ensure this atomicity.

Why Use Transactions?

• Transactions provide ACID properties for data manipulation:

  • Atomicity
    As mentioned earlier, transactions ensure all statements succeed or none do.
  • Consistency
    The database enforces data integrity rules within a transaction, preventing invalid states.
  • Isolation
    Transactions isolate changes from other users until a COMMIT or ROLLBACK, ensuring data consistency.
  • Durability
    Once COMMIT is issued, changes are persisted to disk and become permanent.

Using START TRANSACTION

• START TRANSACTION is typically used within programs or scripts that modify database data.

START TRANSACTION;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 20 WHERE id = 2;

COMMIT;

In this example, if the first update fails (e.g., insufficient funds), the entire transaction is rolled back using ROLLBACK, preventing any changes.

• Transactions cannot be nested within MariaDB.
• While START TRANSACTION is preferred in stored procedures and events, BEGIN can also be used.

---

Transferring funds between accounts (using prepared statements for security)

START TRANSACTION;

-- Prepared statements to prevent SQL injection
PREPARE transfer_stmt  FROM 'UPDATE accounts SET balance = balance - ? WHERE id = ?';
PREPARE deposit_stmt  FROM 'UPDATE accounts SET balance = balance + ? WHERE id = ?';

SET @from_account = 1;
SET @to_account = 2;
SET @amount = 100;

EXECUTE transfer_stmt USING @amount, @from_account;

-- Check if the transfer originated from an account with sufficient balance
IF ROW_COUNT() = 1 THEN
  EXECUTE deposit_stmt USING @amount, @to_account;
  COMMIT;
ELSE
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds in origin account';
END IF;

DEALLOCATE transfer_stmt;
DEALLOCATE deposit_stmt;

This example uses prepared statements (PREPARE) to prevent SQL injection vulnerabilities. It checks if the transfer originated from an account with sufficient funds before committing the transaction.

START TRANSACTION;

UPDATE products SET price = price * 0.9 WHERE category = 'electronics';

SAVEPOINT discount_applied;

UPDATE orders SET total_price = total_price * 0.9 WHERE order_date = CURDATE();

-- Additional logic, might throw an error

IF @@ERROR > 0 THEN
  ROLLBACK TO SAVEPOINT discount_applied;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error processing order discounts';
ELSE
  COMMIT;
END IF;

---

1. Simpler Logic
   

• If your modifications are straightforward and don't require complex error handling, you might be able to rewrite your queries to avoid the need for a transaction altogether.

1. Explicit Table Locking (Limited Use)
   

• MariaDB supports LOCK TABLE and UNLOCK TABLE statements. However, this approach has limitations:
  • It's not ACID compliant (no atomicity or isolation guarantees).
  • It can lead to concurrency issues if not used carefully.
  • It's generally not recommended for most scenarios.

1. Stored Procedures with Autocommit Disabled
   

• You can create stored procedures that disable autocommit (SET autocommit=0) at the beginning and handle commits/rollbacks explicitly. This offers some transaction-like behavior but can be more complex to manage.

Important Considerations

• Stored procedures with disabled autocommit require careful design and error handling.
• Explicit table locking can lead to performance issues and deadlocks in high-concurrency scenarios.
• When considering alternatives, ensure you understand the trade-offs. Without transactions, data consistency and integrity might be compromised.

• In most cases, using START TRANSACTION with COMMIT and ROLLBACK is the safest and most reliable approach for maintaining data integrity and consistency in MariaDB.