Data Integrity, Transactions and Availability

Introduction

A secure database must do more than prevent unauthorised access. It must preserve correct data, ensure that operations produce valid results, and remain accessible when users and applications need it. These objectives are closely related. A database that is confidential but contains contradictory account balances is not trustworthy; a database that is accurate but unavailable during a critical service outage is not operationally dependable.

This chapter examines three connected foundations of database security: integrity, transaction correctness, and availability. Integrity is supported through constraints, validation, transaction properties, concurrency control, replication, backup, recovery, and integrity verification. These mechanisms must be designed together because improving one objective can affect another. For example, synchronous replication can strengthen durability but may increase transaction latency, while relaxed consistency can improve availability but permit temporarily different database copies.

The discussion assumes a relational database environment, but many principles also apply to distributed and NoSQL systems.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain entity, referential, domain, and business-rule integrity.
2. Design suitable database constraints and understand their limitations.
3. Describe transactions and the ACID properties.
4. Explain common concurrency anomalies and isolation levels.
5. Compare locking, timestamp ordering, and optimistic control.
6. Describe replication, high availability, backup, and recovery strategies.
7. Apply integrity-verification techniques such as checksums, reconciliation, and audit logging.
8. Evaluate security, governance, and operational trade-offs in a database design.

1. Understanding Data Integrity

Data integrity is the degree to which data remains accurate, complete, consistent, and trustworthy throughout its lifecycle. Integrity has several dimensions.

1.1 Entity integrity

Entity integrity requires each row representing an entity to be uniquely identifiable. In a relational database this is normally implemented with a primary key. A primary key must be unique and must not be null. For example, a customer table may use customer_id as its primary key. Without entity integrity, two records could appear to represent the same customer, or an update could affect the wrong row.

1.2 Referential integrity

Referential integrity ensures that relationships between tables remain valid. A foreign key in one table refers to a candidate or primary key in another table. For example, an invoice may contain customer_id, which must refer to an existing customer. The database should prevent an invoice from referring to a nonexistent customer, unless a deliberately defined business process permits an unresolved reference.

Foreign-key actions commonly include:

• RESTRICT or NO ACTION: reject deletion or modification that would create an orphan record.
• CASCADE: propagate a change or deletion to related rows.
• SET NULL: remove the reference, provided the column permits null values.
• SET DEFAULT: replace the reference with a defined default value.

Cascading actions are convenient but must be used carefully. A single deletion can affect many tables and may cause accidental data loss.

1.3 Domain integrity

Domain integrity restricts values to an appropriate type, range, format, or set. Examples include a date column containing valid dates, a quantity being a non-negative integer, and a status being one of Pending, Approved, or Rejected. Domain controls are often implemented through data types, NOT NULL, CHECK, and controlled reference tables.

1.4 Business-rule integrity

Business rules express organisational requirements that may be more complex than ordinary column checks. Examples include:

• An employee may not approve their own expense claim.
• A customer may not have more than one active subscription of a particular type.
• A withdrawal must not exceed an account’s available balance.
• A shipment cannot be marked delivered before it has been dispatched.

Some rules can be enforced with unique indexes, checks, triggers, or stored procedures. Others require transaction logic in the application or a combination of database and application controls.

1.5 Constraints as preventive controls

Constraints are preferable to relying solely on application code because they provide a central enforcement point. If several applications write to the same database, a rule implemented only in one application can be bypassed by another application, a script, or an administrator.

A simplified table definition might include:

CREATE TABLE Account (
    account_id      INTEGER PRIMARY KEY,
    customer_id     INTEGER NOT NULL,
    balance         DECIMAL(12,2) NOT NULL CHECK (balance >= 0),
    status          VARCHAR(12) NOT NULL
                    CHECK (status IN ('OPEN', 'CLOSED')),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

The CHECK constraint prevents negative balances, but it does not by itself prevent a race condition in which two transactions independently observe the same balance. That problem requires transaction and concurrency control.

2. Transactions and ACID Properties

A transaction is a logical unit of work that changes data from one valid state to another. A transfer between two accounts is a typical example: money is deducted from one account and added to another. These operations must not be treated as unrelated updates.

The traditional ACID model describes four important properties.

2.1 Atomicity

Atomicity means that all operations in a transaction succeed, or none of them become permanent. If a transfer debits the source account but the credit operation fails, the debit must be rolled back.

Atomicity is implemented through transaction logs, undo information, recovery procedures, and commit protocols. It does not mean that every statement is automatically safe; the application must define the correct transaction boundary.

2.2 Consistency

Consistency means that a committed transaction preserves declared constraints and business invariants. If the total amount of money in a closed transfer system must remain constant, a valid transfer changes its distribution but not the total.

Consistency is not the same as “all replicas currently show identical data.” It refers primarily to valid state transitions. In distributed systems, replica convergence and application-level consistency are additional concerns.

2.3 Isolation

Isolation controls how much one transaction can observe the intermediate work of another. Strong isolation makes concurrent execution resemble serial execution, but it may reduce throughput or availability.

2.4 Durability

Durability means that once a transaction commits, its effects survive failures such as process crashes or power loss, subject to the system’s stated recovery guarantees. Write-ahead logging is a common technique: the log record describing a change is safely written before the corresponding data page is considered durable.

A commit acknowledgement must therefore have a clear meaning. If a system acknowledges a transaction before its required log or replica has safely recorded it, the system may lose acknowledged data after failure.

3. Transaction Processing

A transaction normally follows this lifecycle:

1. Begin transaction.
2. Read required data.
3. Validate permissions and business conditions.
4. Perform changes.
5. Validate constraints and invariants.
6. Commit, or roll back if an error occurs.

A transfer can be represented conceptually as:

BEGIN;

UPDATE Account
SET balance = balance - 100.00
WHERE account_id = 101 AND balance >= 100.00;

-- The application verifies that exactly one row was updated.

UPDATE Account
SET balance = balance + 100.00
WHERE account_id = 202;

COMMIT;

In production, the application must also verify that the destination exists, the source and destination are appropriate, the authenticated user is authorised, and the first update actually succeeded. If any condition fails, the transaction must roll back.

3.1 Invariants

An invariant is a condition that must remain true for every committed state. If account balances cannot be negative, then for every committed account record:

If a transfer of amount a moves money from account S to account D, then, ignoring fees and rounding, the balance changes are:

where B_S and B_D are the original balances, B_S' and B_D' are the resulting balances, and a ≥ 0. The equations apply only if both accounts exist, the source has sufficient funds, and the transaction commits completely.

Worked Example: Transfer Integrity

Account A has 500 monetary units and Account B has 200. A valid transfer of 125 produces:

• A: 500 − 125 = 375
• B: 200 + 125 = 325
• Combined total before: 700
• Combined total after: 700

If the debit succeeds but the credit fails and no rollback occurs, the combined total becomes 575. Atomicity prevents this invalid committed state.

4. Concurrency and Isolation

Concurrency allows multiple users or services to execute transactions at the same time. It improves performance but introduces risks when transactions access the same records.

4.1 Common anomalies

Lost update: Two transactions read the same value and both write changes. The later write overwrites the earlier one.

Dirty read: A transaction reads data written by another transaction that has not yet committed. If the writer rolls back, the reader used data that never became valid.

Non-repeatable read: A transaction reads a row twice and obtains different values because another transaction committed an update between the reads.

Phantom read: Repeating a query returns a different set of rows because another transaction inserted or deleted rows matching the query condition.

Write skew: Two transactions read related facts, each concludes that its own change is safe, and together they violate a rule. This can occur even when they update different rows.

4.2 Isolation levels

Isolation levels define permitted observations. Exact behaviour depends on the database engine and implementation, so documentation must be consulted rather than assuming names have identical semantics everywhere.

Serializable execution is the strongest conventional isolation guarantee, but it does not necessarily mean transactions literally run one at a time. A database may use locks, predicate protection, or serialisation validation to produce an equivalent result.

4.3 Locking

A shared lock permits compatible reads; an exclusive lock is required for modifications. Two-phase locking requires a transaction to acquire locks during a growing phase and release them during a shrinking phase. Strict two-phase locking commonly holds write locks until commit or rollback, helping prevent other transactions from reading uncommitted changes.

Locking introduces risks:

• Deadlock: Transaction T1 waits for T2 while T2 waits for T1.
• Blocking: One transaction waits for a lock held by another.
• Lock escalation: Many fine-grained locks are replaced with a coarser lock, potentially reducing concurrency.

Deadlocks are not necessarily evidence of a security breach, but they can be exploited for denial of service if an attacker deliberately creates conflicting transactions. Systems should use timeouts, deadlock detection, consistent lock ordering, and bounded retries.

4.4 Optimistic and timestamp-based control

Optimistic concurrency assumes conflicts are uncommon. A transaction reads data, performs its work, and validates that relevant versions have not changed before committing. A version column can support this approach:

UPDATE Product
SET stock = stock - 1, version_no = version_no + 1
WHERE product_id = 55
  AND stock > 0
  AND version_no = 8;

If zero rows are updated, another transaction changed the product or the stock condition was false. The application must handle the conflict safely.

Timestamp ordering assigns logical ordering information to transactions and rejects operations that violate that order. It can avoid some deadlocks but may cause transaction restarts.

4.5 Choosing isolation

Use the weakest isolation that still preserves the required business invariants, not merely the fastest setting. A dashboard that displays approximate totals may tolerate a weaker level than a funds-transfer operation. Document the decision, test under realistic load, and monitor anomalies and rollback rates.

5. Replication and Availability

Availability is the ability of authorised users and services to access required data within acceptable performance and recovery limits. Availability is not simply “the server is running.” It includes network access, authentication services, storage, dependencies, and the ability to return correct data.

5.1 Replication models

Synchronous replication requires a replica or quorum to confirm a change before commit acknowledgement. It can reduce data loss after a primary failure but may increase latency and become unavailable if required replicas cannot be reached.

Asynchronous replication allows the primary to acknowledge before replicas receive the change. It usually offers lower latency and better tolerance of network delay, but a sudden primary failure can lose changes that had not yet replicated.

Primary–replica architecture directs writes to a primary and reads to replicas. Read replicas can improve scalability, but applications must understand replication lag. A user who updates a profile and immediately reads from a lagging replica may see an older value.

Multi-primary replication permits writes at multiple sites. It can improve geographic availability but requires conflict detection, conflict resolution, and careful design of unique identifiers and business rules.

5.2 Recovery objectives

Two common planning measures are:

• Recovery Point Objective (RPO): the maximum acceptable amount of data loss measured in time.
• Recovery Time Objective (RTO): the maximum acceptable time to restore service.

For example, an RPO of 15 minutes means the organisation is prepared, at worst, to lose up to approximately 15 minutes of changes. An RTO of two hours means service should be restored within two hours. These are requirements, not guarantees, until supported by tested technology and procedures.

5.3 Backups

A resilient backup strategy commonly includes full backups, incremental or differential backups, transaction-log backups where supported, off-site or geographically separate copies, encryption, access control, retention policies, and routine restoration tests.

The “3-2-1” principle is a useful operational guideline: maintain at least three copies, on at least two types of storage or media, with at least one copy separated from the primary environment. It should be supplemented by immutable or offline protection against ransomware and administrative compromise.

Replication is not a replacement for backup. A corrupted or maliciously modified record may be replicated immediately to every copy. Backups provide historical recovery points; replicas primarily improve continuity and read capacity.

5.4 Failover and split-brain risk

Automatic failover requires reliable health detection and a mechanism to prevent two nodes from both believing they are primary. This condition, called split brain, can produce conflicting writes and severe integrity problems. Quorum, fencing, leases, and consensus mechanisms may be used to ensure that only an authorised primary accepts writes.

A failover plan must address DNS or routing, credentials, encryption keys, application connection pools, schema compatibility, monitoring, and the process for returning to normal operation. Failover drills are essential because an untested plan is only an assumption.

6. Integrity Verification and Auditability

Preventive controls are important, but organisations also need detective controls to identify corruption, omissions, unauthorised changes, and replication divergence.

6.1 Checksums and hashes

A checksum can detect accidental corruption. A cryptographic hash produces a digest that changes when input data changes. Hashes support comparison and evidence, but a basic hash alone does not prove authenticity: an attacker who can alter the data may also replace the hash. For adversarial environments, use authenticated mechanisms such as a keyed message authentication code or a digitally signed manifest, with protected keys.

6.2 Reconciliation

Reconciliation compares independently maintained records. Examples include comparing an order system with a payment processor, checking that ledger entries balance, and comparing row counts and sampled hashes between a primary and replica.

A reconciliation control should define:

• what is compared;
• the comparison frequency;
• acceptable differences;
• who investigates exceptions;
• how corrections are authorised and recorded.

6.3 Audit logs

An audit log should record significant events such as authentication, privilege changes, schema changes, sensitive reads, inserts, updates, deletes, exports, and administrative actions. Useful fields include timestamp, actor, source, action, object, result, request or transaction identifier, and reason where appropriate.

Logs must be protected from unauthorised alteration. Central collection, restricted access, time synchronisation, retention rules, and tamper-evident storage support accountability. Logging sensitive values unnecessarily can create privacy and security risks, so logs should contain the minimum information needed for investigation.

6.4 Periodic integrity checks

Operational checks may include orphan-record searches, duplicate detection, invalid-status detection, balance or total reconciliation, failed-constraint monitoring, and verification of backup restorability. A database administrator should distinguish a genuine data error from a temporary replication lag or an expected business exception.

7. Applied Case: Illustrative Digital Banking Scenario

Background

A regional bank operates a web and mobile platform. It uses a primary database, two read replicas, encrypted backups, and asynchronous replication to a disaster-recovery site.

Problem

Customers report that transfers occasionally appear successful in one channel but not another. During a traffic surge, two withdrawals from the same account are both accepted even though only one should have been affordable. The investigation also finds that administrators cannot reliably determine which service changed a customer’s contact details.

Analysis

The withdrawal defect is a concurrency problem. Each transaction reads the same balance before either transaction writes its update. The use of a non-serializable workflow permits a lost-update or write-skew-like outcome. The inconsistent customer display is caused by replica lag, while the investigation problem reflects inadequate audit logging.

Approach and solution

The bank adopts the following controls:

1. A transaction locks or conditionally updates the account row and verifies that exactly one withdrawal condition succeeds.
2. The withdrawal transaction includes the balance update and ledger entry in one atomic unit.
3. A transaction identifier is returned to the client so that status can be checked against the authoritative service.
4. Read-after-write operations are routed to the primary or to a replica known to have caught up.
5. Critical ledger replication is changed to a synchronous quorum design within the primary region; disaster recovery remains asynchronous because of geographic latency.
6. Audit records capture actor, service identity, transaction identifier, old and new values where justified, and outcome.
7. Backups are made immutable, and quarterly restoration and failover exercises are scheduled.
8. Alerts detect constraint failures, replication lag, unusual rollback rates, and reconciliation differences.

Outcome

The bank prevents overdrawing caused by concurrent withdrawals, makes transaction status clearer to customers, and can investigate changes using correlated audit events. The design does not eliminate all risk: a regional disaster can still create a recovery-point gap at the remote site, and stronger replication increases latency. Those residual risks are documented and accepted by management.

Learning points

The case demonstrates that integrity is not achieved by a single constraint. It requires valid schema rules, transaction boundaries, concurrency control, authoritative read decisions, protected replication, recoverable backups, and evidence-producing audit processes.

8. Governance, Security, and Contemporary Practice

Database integrity controls must align with legal and organisational obligations. Privacy regulations may require accuracy, correction, retention limitation, access control, and demonstrable accountability. Financial, health, and critical-infrastructure systems may impose stronger requirements for audit trails, segregation of duties, and recovery testing.

Least privilege should apply to database accounts, replication identities, backup operators, developers, and administrators. Separation of duties reduces the possibility that one person can alter data and erase evidence. Schema changes should be reviewed, tested, approved, and capable of rollback where practical.

Cloud-managed databases can provide automated backups, multi-zone deployment, encryption, monitoring, and failover. They do not remove the customer’s responsibilities. Organisations must still configure permissions, retention, network controls, key management, recovery objectives, and verification tests.

Artificial intelligence can help detect unusual access, replication failures, or abnormal update patterns, but it should support—not replace—deterministic constraints and transaction controls. An anomaly detector may identify suspicious behaviour after damage has occurred; a foreign key or transaction condition can prevent invalid state directly. AI systems also require careful handling of sensitive data, false positives, explainability, and human oversight.

9. Best-Practice Design Checklist

A professional database-security design should:

• define business invariants before implementation;
• enforce fundamental rules in the database where possible;
• use appropriate primary keys, foreign keys, data types, and checks;
• select transaction boundaries that correspond to real business operations;
• verify affected-row counts and handle errors explicitly;
• choose isolation based on required correctness and workload;
• prevent or detect deadlocks and bound automatic retries;
• document synchronous and asynchronous replication behaviour;
• define and test RPO and RTO;
• maintain protected, separate, and restorable backups;
• monitor replica lag and reconciliation exceptions;
• protect audit logs and minimise unnecessary sensitive content;
• test failure, recovery, failover, and rollback procedures;
• review residual risks with system owners and compliance personnel.

Chapter Summary

Data integrity means that stored information remains accurate, complete, valid, and trustworthy. Entity, referential, domain, and business-rule integrity are supported by primary keys, foreign keys, data types, checks, unique constraints, transaction logic, and audit controls. Transactions group related operations and use atomicity, consistency, isolation, and durability to preserve valid state transitions.

Concurrency can produce lost updates, dirty reads, non-repeatable reads, phantoms, and write skew. Isolation levels, locking, optimistic validation, and timestamp methods provide different balances between correctness, performance, and availability. Replication and failover improve service continuity, but synchronous and asynchronous models have different latency and data-loss implications. Backups remain essential because replication can copy corruption or malicious changes.

Integrity verification combines hashes, reconciliation, audit logs, constraint monitoring, and restoration tests. Effective database security therefore requires a layered approach covering prevention, detection, recovery, governance, and continuous testing.

Key Terms

Data integrity: Accuracy, completeness, validity, and consistency of data.

Entity integrity: The requirement that each row has a unique, non-null identifier.

Referential integrity: The requirement that foreign-key references remain valid.

Domain integrity: Restrictions on data type, format, range, or permitted values.

Business rule: An organisational condition governing valid data or operations.

Constraint: A database rule that restricts invalid states or relationships.

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing property of a transaction.

Consistency: Preservation of valid constraints and invariants after commitment.

Isolation: Control over the visibility and interaction of concurrent transactions.

Durability: Persistence of committed changes through specified failures.

Dirty read: Reading data written by an uncommitted transaction.

Lost update: An update overwritten by a concurrent update.

Deadlock: A circular wait among transactions or processes.

Replication: Maintaining copies of data on multiple database nodes.

Replication lag: The delay before a replica reflects a primary’s change.

RPO: Maximum acceptable time-related amount of data loss.

RTO: Maximum acceptable time to restore service.

Reconciliation: Comparison of records or totals to identify discrepancies.

Audit log: A protected record of significant actions and events.

Review Questions

Short Answer Questions

1. What is the difference between entity integrity and referential integrity?
2. Define atomicity and give one example of its importance.
3. What is a dirty read?
4. Distinguish synchronous from asynchronous replication.
5. Why is replication not a replacement for backup?

Long Answer Questions

1. Explain the ACID properties and show how they apply to a funds-transfer transaction.
2. Compare the principal isolation levels and discuss their performance and correctness trade-offs.
3. Describe how locks can cause blocking and deadlock, and explain methods for reducing these risks.
4. Design a backup, replication, and recovery strategy for a business-critical database.
5. Explain how constraints, audit logs, reconciliation, and cryptographic verification work together to protect integrity.

Analytical / Application Questions

1. Two users withdraw money simultaneously from an account whose balance is sufficient for only one withdrawal. Identify the likely anomaly and propose a transaction design that prevents it.
2. A company reads customer updates from a geographically distant asynchronous replica. Analyse why users may see old data and recommend an appropriate read strategy.
3. During an incident, a replica contains different totals from the primary, while all replicas show the same suspicious modification. Explain how you would distinguish replication lag from corruption and what recovery evidence you would examine.

Multiple Choice Questions with Answers

Question 1

Which constraint primarily enforces that every row in a table has a unique, non-null identifier?

A. Foreign key

B. Primary key

C. CHECK constraint

D. View

Correct Answer: B. Primary key

Question 2

Which ACID property ensures that a partially completed transfer does not become permanent?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 3

A transaction reads data written by another transaction that later rolls back. What anomaly occurred?

A. Phantom read

B. Lost update

C. Dirty read

D. Deadlock

Correct Answer: C. Dirty read

Question 4

What is the principal disadvantage of asynchronous replication during sudden primary failure?

A. It always prevents read scaling

B. It may lose changes not yet replicated

C. It requires all writes to be duplicated manually

D. It cannot use encryption

Correct Answer: B. It may lose changes not yet replicated

Question 5

Which objective specifies the maximum acceptable time to restore database service?

A. RPO

B. RTO

C. MTU

D. TTL

Correct Answer: B. RTO

Question 6

Two transactions each hold a lock needed by the other. This situation is a:

A. Dirty read

B. Checkpoint

C. Deadlock

D. Replica lag

Correct Answer: C. Deadlock

Question 7

Why should a database application verify the number of rows affected by a conditional UPDATE?

A. To confirm that the intended condition and update actually succeeded

B. To disable foreign-key checking

C. To guarantee asynchronous replication

D. To remove the need for authentication

Correct Answer: A. To confirm that the intended condition and update actually succeeded

Question 8

Which statement best describes a cryptographic hash used for integrity checking?

A. It guarantees that data is confidential

B. It can help detect changes to input data

C. It automatically repairs corrupted records

D. It prevents all authorised updates

Correct Answer: B. It can help detect changes to input data

Question 9

Why is replication alone insufficient as a recovery strategy?

A. Replicas cannot contain indexes

B. Replicas may immediately copy corruption or malicious changes

C. Replication works only for text data

D. Replicas cannot be monitored

Correct Answer: B. Replicas may immediately copy corruption or malicious changes

Question 10

Which isolation level aims to make concurrent execution equivalent to some serial order?

A. Read Uncommitted

B. Read Committed

C. Repeatable Read

D. Serializable

Correct Answer: D. Serializable