ACID Principles of Database Transactions

A transaction represents a cohesive unit of database operations that must execute entirely or not at all. This unit adheres to four fundamental principles, commonly abbreviated as ACID.

1. Atomicity: All operations within the work unit are treated as a single indivisible entity. They must either all succeed or all fail, precluding any partial execution.

2. Consistency: The execution of a transaction must transition the database from one valid state to another. For instance, in a funds transfer between two accounts, a successful transaction guarantees the exact amount deducted from the source is added to the destination. If the transaction fails, no transfer occurs. The remaining three principles exist fundamentally to enforce this consistency.

3. Isolation: Often managed through concurrency control, serialization, or locking, isolation ensures that operations within a transaction remain shielded from concurrent executions by other users, preventing data inconsistencies.

4. Durability: Once a transaction is committed, its modifications are permanently recorded. The database system guarantees these changes survive system failures. However, this durability is logical; physical catastrophes like irreversible hardware damage can still result in data loss.

Initiating Transactions in MySQL

Using Explicit Start Commands

You can initiate a transaction using BEGIN or START TRANSACTION, and finalize it with either COMMIT or ROLLBACK. This approach also supports savepoints for partial rollbacks.

sql -- Demonstration of savepoints BEGIN DECLARE has_error INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

START TRANSACTION; SAVEPOINT initial_state;

INSERT INTO user_accounts (account_id, holder_name, balance) VALUES (146, 'alice', 9000);

SAVEPOINT after_first_insert;

INSERT INTO user_accounts (account_id, holder_name, balance) VALUES (101, 'bob', 9000); INSERT INTO user_accounts (account_id, holder_name, balance) VALUES (102, 'charlie', 9000);

IF has_error = 1 THEN ROLLBACK TO SAVEPOINT initial_state; INSERT INTO user_accounts (account_id, holder_name, balance) VALUES (151, 'david', 9000); INSERT INTO user_accounts (account_id, holder_name, balance) VALUES (152, 'eve', 9000); COMMIT; END IF; END;

Disabling Autocommit

Setting AUTOCOMMIT to 0 disables automatic execution. Every subsequentt operation becomes part of an active transaction. When a transaction concludes under this mode, the next statemant automatically initiates a new transaction.

sql SET AUTOCOMMIT = 0;

-- Data remains uncommitted without explicit intervention INSERT INTO product_catalog (product_id, item_name, price) VALUES (143, 'laptop', 9000); COMMIT;

-- A new transaction begins automatically after the previous commit INSERT INTO product_catalog (product_id, item_name, price) VALUES (258, 'mouse', 8000);

Implicit Commit Triggers

Transactions cannot be nested. Initiating a new transaction while one is active will force an implicit commit on the current one. Specific scenarios that trigger implicit commits include:

1. Opening a new transaction explicitly: Commits the preceding active transaction.

sql START TRANSACTION; INSERT INTO system_settings (config_key, config_val) VALUES ('mode', 'open'); -- The above insert is implicitly committed START TRANSACTION; INSERT INTO system_settings (config_key, config_val) VALUES ('mode', 'closed'); ROLLBACK;

2. Executing DDL or DCL statements: Data Definition Language and Data Control Language commands inherently start a new transaction, causing any pending changes to commit implicitly.

sql SET AUTOCOMMIT = 0; BEGIN; INSERT INTO order_logs (log_id) VALUES (1); -- The DDL statement commits the previous insert and starts a standalone transaction CREATE TABLE audit_logs (record_id INT PRIMARY KEY); INSERT INTO audit_logs (record_id) VALUES (2); ROLLBACK; -- Only the insert into audit_logs is reversed

3. Terminating a stored program block: Reaching the END clause of a stored routine forces an implicit commit.

sql BEGIN START TRANSACSION; INSERT INTO system_settings (config_key, config_val) VALUES ('theme', 'dark'); INSERT INTO system_settings (config_key, config_val) VALUES ('lang', 'en'); END; -- Implicit commit occurs here

Critical Transaction Considerations

• The BEGIN and END keywords defining a stored routine's body act as a transactional boundary; the END statement guarantees an implicit commit.

sql BEGIN INSERT INTO system_settings (config_key, config_val) VALUES ('timezone', 'UTC'); INSERT INTO system_settings (config_key, config_val) VALUES ('currency', 'USD'); -- Implicit commit occurs at END END

• Premature commits fracture atomicity. Issuing a COMMIT midway through a routine ends the transaction immediately, leaving subsequent operations unprotected.

sql CREATE TABLE client_data (uid INT PRIMARY KEY, client_name VARCHAR(100));

CREATE PROCEDURE register_client(IN p_uid INT, IN p_name VARCHAR(100)) BEGIN START TRANSACTION; INSERT INTO client_data VALUES (p_uid, p_name); COMMIT; -- Transaction terminates here INSERT INTO client_data VALUES (p_uid, p_name); -- Intentional duplicate key violation ROLLBACK; -- Ineffective because the first insert is already committed END;

• Refrain from utilizing DDL or DCL statements within stored routines. They provoke implicit commits, breaking atomicity, and generally require complex syntax to execute properly within procedural code.

• Understand the distinction between START TRANSACTION and disabling autocommit: START TRANSACTION applies only to the immediate logical block, whereas disabling autocommit ensures a continuous transactional state where concluding one transaction immediately initiates the next.

• Transactional support is engine-dependent:

  • MyISAM: Lacks transactional support; suited for read-intensive scenarios requiring speed.
  • InnoDB: Fully supports ACID compliance, row-level locking, and high concurrency.
  • Berkeley DB (BDB): Provides transactional capabilities.