Shared from "Study" on Inkdown
A transaction is a logical unit of work that consists of one or more database operations. It represents a complete business operation that must succeed or fail as a whole.
ACID guarantees data integrity even in the face of errors, power failures, and concurrent access.
Definition: A transaction is treated as a single unit. Either all operations complete successfully, or all are rolled back.
Implementation:
Definition: A transaction brings the database from one valid state to another, maintaining all defined rules (constraints, triggers, cascades).
Important Distinction:
Definition: Concurrent transactions execute independently. The result is as if transactions ran serially, one after another.
Without Isolation (Problems):
SQL standard defines 4 isolation levels. Higher level = more isolation = less concurrency.
READ UNCOMMITTED:
READ COMMITTED (Default in PostgreSQL, Oracle):
REPEATABLE READ (Default in MySQL):
SERIALIZABLE:
Definition: Once a transaction is committed, it remains committed even in case of system failure (power loss, crash).
Implementation:
PostgreSQL and MySQL (InnoDB) use MVCC instead of locking for isolation.
MVCC Benefits:
MVCC Costs:
When conflicts occur, databases use locks.
Avoiding Deadlocks: