Reliable Relational Database Management
A practical lesson on keeping relational data valid, coordinating concurrent transactions, handling failures, and preparing for recovery.
Data Integrity and All-or-Nothing Transactions
Data Integrity and All-or-Nothing Transactions
A database must do more than store information: it must help keep that information valid when different applications or users add or change rows. Constraints are rules the database enforces at those moments, so important rules do not depend solely on each application remembering to check them.
Consider an order linked to a customer. A PRIMARY KEY gives each order a unique, non-null identifier; a table can have only one primary key, though it may combine multiple columns. NOT NULL requires a value to be present. UNIQUE prevents duplicates in a column or combination of columns. These rules address whether values are present or unique. A FOREIGN KEY, by contrast, enforces a relationship: the order’s customer ID must match a key in the customers table. It can be combined with NOT NULL when the customer reference must be provided. An action such as ON DELETE CASCADE specifies what happens to related rows if a referenced row is deleted.
A CHECK enforces a condition on a row, such as requiring an order total to be at least zero. That condition is not a substitute for a foreign key: it constrains a value, not a relationship between tables. Also, a CHECK alone does not generally rule out NULL, so pair it with NOT NULL when the value is required. For example, an order can have a primary-key order ID, a non-null customer ID referencing a customer, and a non-null total constrained to be at least zero. The last condition permits zero; it does not mean strictly greater than zero.
Constraints protect individual facts and relationships. A protects a set of related changes by treating them as one logical unit. With COMMIT, the changes are accepted; with ROLLBACK, they are undone. This is essential for a money transfer: reducing one account’s balance and increasing another’s must belong to the same . Otherwise, only one side could take effect. If a step fails, roll back instead of committing a partial transfer. The should also check that the source account exists and has sufficient funds.
The properties explain the goals:
Atomicity: all operations take effect, or none do.
Consistency: committed changes preserve the database’s defined constraints and rules.
Isolation: concurrent work does not violate the chosen isolation guarantees.
Durability: a successful commit survives failures covered by the database’s durability mechanisms.
Together, constraints and transactions address different scopes: constraints define lasting rules for rows and relationships, while transactions make related changes an all-or-nothing unit. Exact syntax and behavior can vary between database systems.
Concurrency, Isolation, and Access Coordination
Concurrency, Isolation, and Access Coordination
Concurrency means multiple transactions access data at the same time. Their operations can interfere if the database does not provide suitable controls. The anomalies below are easiest to tell apart by asking what changed: an uncommitted value, a previously read row, a search’s result set, or a value another also updated.
| Anomaly | What happens | Key distinction |
|---|---|---|
| Dirty read | A transaction reads another transaction’s uncommitted change, which may later be undone. | The value read is not yet committed. |
| Nonrepeatable read | A transaction reads the same row twice and gets different values after another transaction commits an update. | A previously read row has a new value. |
| Phantom read | Repeating a search returns a different set of rows after another transaction inserts or deletes matching rows. | The matching row set changes. |
| Lost update | Two transactions change a value based on the same old value, and one change overwrites the other. | One transaction’s update is overwritten. |
An defines which effects of concurrent transactions are prevented. Lower isolation can permit more concurrency, but may expose more changes between reads. Stronger isolation gives transactions a more consistent view, but can lead to more waiting or the need to retry. SERIALIZABLE aims to make concurrent transactions behave as if they ran one at a time. This is a correctness goal, not a promise that every product implements all isolation-level names identically.
Databases coordinate access using locks, row versions, or both. A lock can make a conflicting operation wait. With MVCC, the system keeps row versions, so readers can often use a consistent earlier view while other transactions make changes. Neither mechanism removes the need to choose appropriate boundaries and isolation guarantees.
The practical choice is a trade-off: use guarantees appropriate to the task rather than automatically selecting the strongest setting. Some systems detect conflicts and abort a . Retry only when it is safe and appropriate; when a retry is required, retry the whole . Because isolation behavior varies by database product, rely on the guarantees of the particular system in use.
Deadlocks and Safe Handling
Deadlocks and Safe Handling
A occurs when transactions wait on one another in a cycle. For example, one holds a lock on row A and waits for row B, while another holds row B and waits for row A. Each is waiting for a resource held by the other, so neither can proceed. A database may break the cycle by aborting one .
Applications should handle deadlocks and other failures explicitly rather than treating them as successful completion. Accessing records in a consistent order across transactions can reduce the chance of this kind of conflict. Keeping transactions short also helps: avoid waiting for user input or doing unnecessary work while a is open. These practices reduce risk; they do not guarantee that deadlocks will never occur.
Handle violations, failures, deadlocks, and serialization errors deliberately. Retry only when it is safe to do so. If a conflict requires a retry, retry the complete logical —not merely the last operation—so the work is attempted again as a coherent unit.
Recovery and Tested Backups
Recovery and Tested Backups
controls help manage active work, but reliable data management also requires a plan for recovery. Use backups and test restoring them: a backup is useful only if it can be restored successfully. Where the database system supports it, archived logs can enable recovery to a selected point in time. That capability is conditional on system support, so it should not be assumed to be available in every database.
: A concurrency-control approach that maintains multiple versions of rows.
: A recovery approach that uses archived logs to restore data to a selected earlier time.