Transactions, ACID, Isolation, Privileges and Storage Engines

1. Transactions

A transaction is a sequence of database operations treated as one logical unit. A bank transfer must debit one account and credit another as one business action:

START TRANSACTION;

UPDATE account
SET balance = balance - 500
WHERE account_id = 10
  AND balance >= 500;

UPDATE account
SET balance = balance + 500
WHERE account_id = 20;

COMMIT;

This outline is incomplete until the application verifies that both accounts exist, the debit changed exactly one row, every statement succeeded and business rules remain valid. A transaction provides mechanisms; correct boundaries and checks remain part of the design.

2. Transaction Boundaries and Autocommit

MySQL sessions commonly begin with autocommit enabled. Each standalone transactional statement commits when it succeeds unless an explicit transaction is active.

START TRANSACTION begins an explicit unit. COMMIT makes its effects permanent under the engine's durability model. ROLLBACK ends it and undoes its uncommitted effects.

Do not leave transactions open while waiting for user input or making slow network calls. Long transactions retain locks or old row versions, increase contention and can interfere with cleanup.

3. Atomicity

Atomicity means the transaction takes effect as a whole or has no effect. If a transfer fails after the debit, rollback must prevent the database from retaining only half the transfer.

Atomicity applies to transactional operations on suitable storage engines. It does not automatically undo an email already sent, a message delivered to an external service or a file written outside the database.

Coordinating external effects requires patterns such as transactional outboxes, idempotent consumers and compensating operations.

4. Consistency

Consistency means a correct transaction takes the database from one valid state to another while preserving relevant invariants.

The DBMS enforces declared primary keys, foreign keys, checks and type rules. Application and procedural logic must enforce business rules that are not fully represented as constraints.

Consistency is not automatic merely because COMMIT succeeds. A transaction that adds money to both accounts can satisfy SQL constraints while violating the conservation rule of a transfer.

5. Isolation

Isolation governs how concurrent transactions observe and interfere with each other. Complete serial execution would provide strong separation but poor concurrency.

Isolation levels permit different implementation tradeoffs while preventing specified phenomena. Application code must choose an appropriate level and use locking or conflict checks where the business decision depends on current shared state.

Isolation does not mean transactions run physically one after another. It defines allowed observations and outcomes.

6. Durability

Durability means that after successful commit, changes survive failures covered by the DBMS configuration and storage model.

InnoDB uses redo logging and coordinated flushing to recover committed changes. Durability strength depends on server settings, storage reliability, replication mode and backup strategy.

Durability is not the same as backup. A committed accidental deletion is durable too. Backups, point-in-time recovery, replication and tested restore procedures address broader loss scenarios.

7. Dirty Reads

A dirty read occurs when one transaction reads changes made by another transaction that has not committed. If the writer rolls back, the reader acted on a value that never became part of committed history.

The READ UNCOMMITTED level can permit dirty reads. Higher standard isolation levels prevent them.

Dirty reads rarely suit business decisions because later rollback can invalidate calculations, notifications and external actions based on the temporary value.

8. Nonrepeatable Reads

A nonrepeatable read occurs when a transaction reads one row, another transaction commits a change to that row and the first transaction rereads it and sees a different value.

READ COMMITTED can allow this because each statement may observe a newer committed snapshot. REPEATABLE READ aims to keep consistent reads stable within the transaction.

When the transaction intends to update a row after checking it, a locking read or optimistic version condition may be needed. Snapshot consistency alone does not reserve the row.

9. Phantom Reads

A phantom occurs when repeating a predicate query produces a different qualifying row set because another transaction inserted, deleted or changed matching rows.

For example, a transaction counts open reservations for a date. Another transaction inserts a qualifying reservation. Repeating the count reveals a new phantom row.

Preventing a row from changing is not enough to protect a predicate range. MySQL InnoDB can use next-key and gap locking for locking reads under applicable isolation and indexes.

10. Isolation Levels

The standard levels are:

Level	Dirty reads	Nonrepeatable reads	Phantoms
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented by the model	product-specific handling
SERIALIZABLE	prevented	prevented	prevented

This table is a conceptual minimum. MySQL uses multiversion concurrency control and locking details that must be understood from its actual version and statement type.

InnoDB's default is commonly REPEATABLE READ, but deployments can change it.

11. Consistent and Locking Reads

A normal InnoDB SELECT often performs a nonlocking consistent read from a snapshot. SELECT ... FOR UPDATE performs a locking read for rows the transaction intends to update. SELECT ... FOR SHARE obtains shared-style protection where supported.

SELECT balance
FROM account
WHERE account_id = 10
FOR UPDATE;

A locking read must run inside an intentional transaction. Its effectiveness depends on predicates and indexes; a broad scan can lock more rows or ranges than expected.

12. Lost Updates

A lost update can occur when two sessions read the same value, compute separate replacements and one write overwrites the other.

Atomic SQL arithmetic avoids a read-modify-write race:

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 50
  AND quantity > 0;

The affected-row count indicates whether stock was available.

Optimistic locking uses a version:

UPDATE document
SET content = ?, version = version + 1
WHERE document_id = ?
  AND version = ?;

Zero affected rows signals a conflict requiring a refresh or retry.

13. SAVEPOINT

A savepoint marks an intermediate point:

START TRANSACTION;
UPDATE ...;
SAVEPOINT after_header;
INSERT ...;
ROLLBACK TO SAVEPOINT after_header;
COMMIT;

Rollback to a savepoint undoes work after that marker while keeping the surrounding transaction active. RELEASE SAVEPOINT removes the marker.

A savepoint is not a commit, backup or independent nested transaction. The earlier work remains uncommitted until the outer transaction commits.

14. Locks and Waiting

InnoDB uses locks to coordinate conflicting changes. Row-level locking improves concurrency, while indexes help locate and lock a narrow set.

Transactions can wait when another transaction holds an incompatible lock. A lock wait timeout reports excessive waiting. The correct response is usually rollback and controlled handling, not pretending the operation succeeded.

Keep transactions short, access rows in a consistent order, use selective indexed predicates and avoid unnecessary user interaction while locks are held.

15. Deadlocks

A deadlock forms when transactions wait in a cycle. Transaction A holds one resource and waits for B, while B holds another and waits for A.

InnoDB detects deadlocks and aborts a victim so the other transaction can continue. Deadlocks are possible in correct concurrent systems and should be handled.

Retry the complete appropriate business transaction, not only the final failed statement. Use bounded retries with fresh transaction state. Consistent lock order and smaller transactions reduce risk but cannot guarantee elimination.

16. Logging and Recovery

Undo information supports rollback and consistent historical reads. Redo information records changes needed to recover committed work after a crash.

The binary log records server changes for replication and point-in-time recovery according to configuration. It serves a different role from InnoDB redo and undo.

Crash recovery is not a replacement for tested backups. Backup procedures must capture a consistent state and retain the logs needed for the desired recovery point.

17. DDL and Transactions

Many MySQL DDL statements cause implicit commits. Creating, altering, dropping or truncating a table should not be mixed casually with a business transaction under the assumption that ROLLBACK reverses everything.

Atomic DDL improves crash consistency for supported schema operations, but it does not make ordinary DDL part of user-controlled transactional rollback.

Run schema migrations as controlled deployment steps and verify behavior for the exact MySQL version.

18. Database Accounts

MySQL accounts combine a user name and host identity. Authentication verifies an account, while authorization determines what it may do.

Applications should use dedicated accounts rather than administrator credentials. Different services and environments should have separate identities so permissions and activity can be controlled and audited.

Use supported authentication plugins, secure connections, secret storage and credential rotation. Never embed unrestricted production passwords in source code.

19. GRANT and REVOKE

GRANT assigns privileges:

GRANT SELECT, INSERT, UPDATE
ON sales.*
TO 'sales_app'@'10.%';

REVOKE removes privileges:

REVOKE DELETE
ON sales.*
FROM 'sales_app'@'10.%';

Privileges can apply at global, database, table, column, routine and other scopes. Verify effective privileges rather than assuming one statement removed permissions inherited through another grant or role.

20. Roles and Least Privilege

A role groups privileges by responsibility. Accounts receive appropriate roles, simplifying review and revocation.

Least privilege gives each account only the operations and objects required for its task. A reporting user may need SELECT on controlled views, while an application service may need limited procedure execution or DML on selected tables.

Avoid broad wildcard grants when a narrower scope works. Separate migration privileges from runtime application privileges.

21. GRANT OPTION

GRANT OPTION allows a recipient to grant certain privileges to other accounts. It is a delegation capability and should be restricted.

An account that only queries data rarely needs the power to authorize new readers. Delegated privileges expand the paths that administrators must audit and revoke.

Use centralized roles and controlled administration instead of distributing GRANT OPTION for convenience.

22. Storage Engines

MySQL storage engines implement table storage and important behavior behind the SQL layer. Tables in one server can use different engines, though mixing them in a business transaction can weaken guarantees.

Inspect the engine through metadata rather than assuming defaults. Engine choice affects transactions, locking, recovery, foreign keys, indexing and specialized capabilities.

For ordinary transactional applications, InnoDB is the standard choice.

23. InnoDB

InnoDB provides ACID transactions, crash recovery, row-level locking, multiversion concurrency control and foreign-key enforcement.

Its tables use a clustered primary-key organization: the primary key determines the clustered record organization and secondary index entries contain the primary-key value. Very wide or changing primary keys therefore enlarge secondary indexes.

If no suitable primary key is defined, InnoDB chooses another internal organization. Defining a stable explicit primary key is better for identity and storage.

24. MyISAM and Other Engines

MyISAM is a nontransactional engine with table-level locking and no InnoDB-equivalent rollback or foreign-key enforcement. It remains relevant for understanding legacy tables but is unsuitable for most new transactional application data.

MEMORY stores data primarily in memory and loses it on restart; it has specialized limits and locking behavior. CSV exposes comma-separated storage with limited features. ARCHIVE targets compressed archival patterns.

Engine selection should follow documented requirements, failure behavior, concurrency and maintenance needs rather than a simplistic speed claim.

25. Designing a Transaction

Define one business invariant and the statements needed to preserve it. Begin the transaction as late as possible. Validate current database state under the required locking or conflict strategy. Check every affected-row count and error.

Commit only after the complete unit succeeds. Roll back on any failure and ensure the connection is returned to a clean state. For retryable conflicts, repeat the whole idempotent unit with a bound.

After correctness, measure contention and optimize indexes or transaction length. Transactions, constraints, privileges and the storage engine work together: none can compensate for an incorrectly chosen business boundary.