Data Integrity, Transactions and Availability

Introduction

A database is valuable only when its data remains correct, complete, consistent, available to authorised users, and recoverable after failure. Cybersecurity therefore extends beyond confidentiality and access control. An attacker who changes an account balance, deletes audit records, corrupts a configuration table, or makes a service unavailable can cause serious operational and legal harm without stealing any information.

This chapter examines three connected responsibilities: preserving data integrity, controlling transactions and concurrent activity, and engineering availability and resilience. These responsibilities are implemented through database constraints, ACID transactions, locking and isolation, checksums, change control, replication, clustering, backups, and tested recovery procedures.

Learning Objectives

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

• distinguish entity, referential, domain, and business-rule integrity;
• explain ACID transaction properties and transaction boundaries;
• describe common concurrency anomalies and appropriate isolation controls;
• use checksums, hashes, audit trails, and validation to detect unauthorised or accidental change;
• compare replication, clustering, redundancy, backup, and failover strategies;
• calculate and interpret availability, recovery point objective, and recovery time objective;
• design a proportionate integrity and availability strategy for a business scenario;
• evaluate governance, ethical, regulatory, and operational limitations.

1. Data Integrity as a Security Property

Data integrity is the assurance that data is accurate, complete, consistent, authentic, and protected from unauthorised or improper modification. Integrity has several dimensions:

• Accuracy: values represent the real-world facts they claim to represent.
• Completeness: required records and fields are not missing.
• Consistency: related values do not contradict one another.
• Validity: values conform to permitted formats, ranges, and rules.
• Authenticity: the source and history of data can be trusted.
• Timeliness: information is sufficiently current for its purpose.

Integrity is not the same as correctness in every possible sense. A database may contain syntactically valid information that is factually wrong—for example, a valid date assigned to the wrong customer. Technical controls must therefore be supported by sound business processes and human review.

1.1 Integrity Constraints

A relational database expresses many integrity rules directly in its schema.

A primary key should be stable, unique, and non-null. A foreign key prevents orphaned references when referential actions are correctly configured. For example, deleting a customer may be prohibited while orders exist, or related records may be archived under a carefully governed rule. Cascading deletes must be used cautiously because one operation can remove a large amount of evidence or business data.

Domain constraints should be enforced as close to the data as practical. Application validation improves user experience, but database constraints provide a final control against defective applications, direct connections, import errors, and malicious input. Business rules that cannot be expressed simply as constraints may require triggers, stored procedures, application services, or controlled workflows. These mechanisms must be documented and tested because hidden logic can make auditing difficult.

1.2 Integrity Across Systems

Integrity becomes more difficult when data crosses databases, APIs, queues, spreadsheets, and third-party services. Common controls include:

• schema validation and typed interfaces;
• duplicate detection and reconciliation reports;
• idempotent message processing, so retrying a message does not create duplicate effects;
• source-to-destination record counts and control totals;
• time-stamped audit logs;
• segregation of duties for high-risk changes;
• periodic comparison with an authoritative source.

A control total may be a count, sum, or cryptographic digest. A count can detect missing rows but may not detect two substitutions. A sum can detect many substitutions but may fail when changes cancel each other. A cryptographic hash provides stronger change detection, but it does not prove that the original data was truthful or that the person who supplied it was authorised.

2. Transactions and the ACID Model

A transaction is a logical unit of work that should produce one acceptable state change. For example, transferring money requires debiting one account and crediting another. Treating these actions as independent operations could produce an incorrect total if a failure occurs between them.

The ACID model describes four desirable properties:

1. Atomicity: all operations in a transaction take effect, or none take effect.
2. Consistency: a committed transaction preserves defined integrity rules.
3. Isolation: concurrent transactions do not produce unacceptable interference.
4. Durability: after commit, changes survive relevant failures.

ACID is a design goal implemented by a database management system and its storage and recovery mechanisms. It is not a guarantee that every application is logically correct. A transaction can commit an incorrect value if the application supplied that value or if a necessary business rule was omitted.

2.1 Transaction Lifecycle

A transaction normally moves through states such as active, partially committed, committed, failed, and aborted. The database records enough information to either make committed work durable or undo incomplete work. A simplified sequence is:

1. begin a transaction;
2. read and validate required data;
3. perform updates;
4. check constraints and business rules;
5. commit, or roll back if a failure occurs.

A common recovery technique is write-ahead logging (WAL). Before a changed data page is written to storage, the log describing the change is written first. If the system fails, recovery can redo committed changes and undo incomplete changes. Exact implementations vary, but the principle is that the recovery record must be available before the corresponding data change is treated as persistent.

2.2 Worked Example: Transfer Transaction

Suppose account A contains £800 and account B contains £300. A transfer of £150 should result in £650 and £450. A transaction might conceptually perform:

BEGIN
  verify A has at least 150
  set A = A − 150
  set B = B + 150
  record transfer in the audit table
COMMIT

The total before the transaction is £1,100, and after a successful transaction it remains £1,100. If the debit succeeds but the credit fails, atomicity requires rollback. A check constraint such as balance >= 0 can help prevent an invalid debit, while authorisation and fraud controls determine whether the transfer should be allowed at all.

The transaction must also define its scope. Holding locks while calling a slow external service may increase contention. Conversely, committing before an external payment is confirmed can create inconsistency between systems. Distributed workflows may therefore use an outbox pattern, acknowledgements, compensation actions, or a carefully designed saga rather than pretending that independent systems share one simple local transaction.

3. Concurrency Control and Isolation

Concurrency allows many users and services to work at the same time, improving throughput. Without control, however, interleaving operations may produce anomalies.

• Dirty read: a transaction reads data written by another transaction that has not committed.
• Non-repeatable read: a row is read twice and has different committed values between reads.
• Phantom read: repeating a range query returns additional or missing rows because another transaction inserted or deleted matching records.
• Lost update: one committed update overwrites another update without incorporating it.
• Write skew: separate transactions read a shared condition and make individually valid updates that together violate a rule.

Isolation levels express the permitted trade-off between consistency and concurrency. Names and exact behaviour can vary by database system, so the vendor documentation must be consulted.

Pessimistic control locks data before use. Shared locks support reading; exclusive locks protect modification. Optimistic control permits work to proceed and checks for conflicts at commit, often using a version number. Optimistic methods suit low-conflict workloads; pessimistic methods may be preferable when conflicts are costly or predictable.

A deadlock occurs when transaction T1 waits for a lock held by T2 while T2 waits for a lock held by T1. Databases detect deadlocks or use timeouts, abort one transaction, and allow the other to continue. Applications should retry safe, idempotent transactions with bounded back-off rather than repeatedly submitting uncontrolled requests.

Good practice includes acquiring resources in a consistent order, keeping transactions short, indexing appropriately, avoiding user interaction inside a transaction, and selecting isolation based on the business risk rather than using the strongest level automatically.

4. Detecting and Preventing Unauthorised Change

4.1 Checksums, Hashes, and Message Authentication

A checksum is a compact value calculated from data to detect accidental alteration. It is useful for transmission errors and routine comparison but is generally not designed to resist deliberate manipulation. Cryptographic hash functions produce a fixed-length digest with strong change-detection properties when used correctly. A hash alone does not authenticate its producer: an attacker who can replace both the file and its hash can evade comparison.

For adversarial integrity protection, use a keyed message authentication code (MAC) or a digital signature. A MAC requires a secret key shared by the validating parties. A digital signature uses a private key to sign and a public key to verify, supporting stronger separation of signing and verification responsibilities. Key protection, rotation, revocation, and time-stamping are essential.

Hashes and signatures should complement, not replace, access control, database constraints, logging, and backups. Passwords require slow, salted password-hashing schemes rather than ordinary fast file hashes.

4.2 Audit Trails and Tamper Evidence

An audit trail records who performed an action, what changed, when it occurred, from which system or location, and whether it succeeded. Important properties include:

• append-only or tightly controlled storage;
• synchronised and trustworthy time sources;
• separation between administrators and audit reviewers;
• protection against deletion and alteration;
• alerting for unusual activity;
• retention consistent with legal and business requirements.

Hash chaining can make later alteration evident by incorporating the previous record's digest into the next record. It does not make logs magically immutable; an attacker with control of the entire logging environment may still rewrite history. Centralised, access-controlled, monitored, and independently backed-up logs provide stronger assurance.

5. Change Control and Configuration Integrity

Many integrity failures arise from authorised but poorly controlled change. A change-management process should classify the change, assess risk, identify dependencies, obtain approval, test it, define a rollback plan, schedule implementation, and verify the outcome.

A professional change record normally includes:

• business justification and scope;
• affected data, schemas, services, and users;
• security and privacy impact;
• test evidence and acceptance criteria;
• implementation and rollback steps;
• named approvers and implementers;
• monitoring and post-change review.

Emergency changes may follow an expedited process, but they still require retrospective review. Separation of duties reduces the possibility that one person can create, approve, and conceal a dangerous change. Version-controlled schema migration tools, infrastructure as code, peer review, automated tests, and protected deployment pipelines improve repeatability and traceability.

6. Availability and Resilience

Availability is the probability or proportion of time that an authorised service is usable when required. A simple operational measure is:

Uptime and downtime must be measured over the same defined period and according to an agreed service definition. Planned maintenance, partial degradation, and dependency failures must be classified consistently.

For example, over a 30-day period of 43,200 minutes, a service with 21.6 minutes of unavailability has:

This calculation assumes downtime is measured accurately and that the service-level definition treats all 21.6 minutes as unavailable. Availability percentages can conceal the user impact of one long outage, so incident duration, frequency, and affected transactions should also be reported.

Availability of independent components in a series is approximately the product of their availabilities, because all must work. For parallel redundant components, availability can be higher because the service works when at least one component works. These calculations assume independence; common power, network, software, or human failures violate that assumption.

6.1 RTO and RPO

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

An RPO of 15 minutes means the organisation aims to recover data no older than 15 minutes before the incident; it does not guarantee that exactly 15 minutes of data will always be recoverable. RTO includes detection, decision-making, restoration, validation, and return to service, unless the organisation defines it differently.

These objectives must be set according to business impact. A public information page may tolerate hours of outage, while a clinical monitoring or payment service may require seconds or minutes. Lower RTO and RPO generally require greater cost, automation, redundancy, bandwidth, and operational maturity.

6.2 Backups and Recovery

A resilient design commonly combines:

• full, incremental, or differential backups;
• encryption in transit and at rest;
• geographically or logically separate copies;
• offline or immutable copies to resist ransomware;
• documented retention and deletion schedules;
• restoration tests using representative data;
• recovery procedures for applications, keys, configurations, and dependencies.

The frequently used 3-2-1 principle recommends at least three copies, on two different media or storage types, with at least one copy off-site. It is a practical guideline rather than a universal guarantee. Backup success is not the same as recoverability; only restoration testing demonstrates that the organisation can meet its objectives.

6.3 Replication, Clustering, and Redundancy

Replication maintains copies of data on multiple systems. Synchronous replication confirms that copies are updated before commit, reducing data loss but increasing latency and dependence on network conditions. Asynchronous replication allows the primary system to commit before replicas are current, improving performance and distance tolerance but creating a replication lag and possible data loss after failure.

Clustering connects multiple servers to provide failover, load distribution, or both. Active-passive clusters keep a standby ready to assume service. Active-active clusters serve users simultaneously but require careful coordination, conflict handling, session management, and consistent data access.

Redundancy does not automatically provide resilience. Two servers sharing one power supply, network switch, storage array, or administrator may share a single point of failure. Designs must identify dependencies and failure domains. Failover also creates risks such as split-brain operation, stale replicas, duplicate processing, and accidental promotion of corrupted data.

7. Applied Case: Illustrative Online Pharmacy

Background

An illustrative online pharmacy stores prescriptions, customer details, inventory, and payment records. It operates continuously and must protect patient safety, privacy, and transaction accuracy.

Problem

The organisation experiences duplicate orders during network retries, occasional stock discrepancies, and concern that ransomware could affect both the production database and its backups. Management requests a design with an RPO of 15 minutes and an RTO of one hour for order processing.

Analysis

Duplicate orders indicate that the application treats retries as new requests. Stock discrepancies may result from inadequate transaction isolation or from inventory updates that are not part of the order transaction. A backup connected continuously to the production environment may be encrypted by the same ransomware event. Patient and payment data also require strict access control and auditable handling.

Approach and Solution

The design uses a unique client-generated order identifier and idempotent order processing. A transaction validates the prescription, checks stock, creates the order, decrements inventory, and records an audit event. Database constraints enforce unique identifiers, non-negative stock, valid product references, and required fields. A suitable isolation strategy prevents two concurrent orders from claiming the same final unit.

The primary database replicates asynchronously to a separate failure domain, with monitoring of replication lag. Encrypted backups run at least every 15 minutes, while immutable or offline copies are retained separately. A warm standby and documented failover procedure target the one-hour RTO. Recovery drills restore a representative environment and verify application behaviour, data integrity, keys, and audit logs.

High-risk schema changes require peer review, testing, approval, and rollback instructions. Privileged access is separated, logs are forwarded to an independently protected system, and alerts identify unusual exports, mass updates, or failed authentication.

Outcome

The design addresses duplicate processing, concurrent stock allocation, recovery objectives, and ransomware exposure. It does not eliminate risk: asynchronous replication can lose recent commits, failover can expose application defects, and prescription validation still requires appropriate professional and regulatory controls.

Learning Points

1. Integrity is a system property, not merely a database feature.
2. Idempotency is essential when networks and clients retry operations.
3. Replication and backup solve different problems and should not be treated as interchangeable.
4. Recovery objectives must be tested, measured, and revised as business needs change.

8. Governance, Ethics, and Contemporary Practice

Integrity and availability controls affect people as well as systems. Excessive logging may expose sensitive health, financial, or personal information. Retaining data indefinitely increases privacy and breach impact. Monitoring should be proportionate, access should follow least privilege, and retention should reflect legal, contractual, and operational requirements.

Organisations should assign data owners, define acceptable recovery objectives, maintain evidence of control operation, and review suppliers and cloud providers. Regulatory obligations may require demonstrable accuracy, availability, auditability, breach response, and continuity planning, although exact duties depend on jurisdiction and sector.

Cloud platforms provide managed replication, snapshots, zones, and failover services, but responsibility is shared. A provider's durability statement does not guarantee that an application can recover, that a deleted object can be restored, or that credentials are protected. Automation and AI-assisted operations can identify anomalies and predict capacity problems, but automated remediation may amplify a false diagnosis. Human approval, testing, explainable alerts, and safe rollback remain important for high-impact changes.

Best-Practice Checklist

• Define data owners, integrity rules, RTO, and RPO.
• Enforce primary keys, foreign keys, domains, uniqueness, and business constraints.
• Keep transactions short, explicit, and appropriately isolated.
• Design retry-safe and idempotent operations.
• Use protected logs, hashes or MACs where appropriate, and independent monitoring.
• Apply tested change control and separation of duties.
• Separate replicas, backups, credentials, and failure domains.
• Protect at least one backup from online ransomware access.
• Test restoration and failover under realistic conditions.
• Measure actual recovery performance and update the design after incidents.

Chapter Summary

Data integrity requires accurate, valid, complete, consistent, authentic, and appropriately current information. Database constraints provide foundational protection, while application validation, reconciliation, audit trails, and access control address wider risks. Transactions group related operations into atomic units and use ACID properties to preserve reliable state. Concurrency control manages dirty reads, lost updates, phantoms, deadlocks, and related anomalies through isolation, locks, versioning, and sound transaction design.

Checksums and hashes detect change, while MACs and digital signatures provide stronger authenticity mechanisms. Change control reduces the risk of unsafe authorised modifications. Availability depends on redundancy, replication, clustering, backups, failover, and recovery testing. RTO and RPO translate business needs into technical objectives, but their value depends on clear definitions and measured performance. A resilient design must consider shared failure domains, ransomware, privacy, governance, supplier responsibility, and human decision-making.

Key Terms

ACID: Properties of atomicity, consistency, isolation, and durability for reliable transactions.

Atomicity: The requirement that a transaction completes fully or has no effect.

Availability: The degree to which an authorised service is usable when required.

Checksum: A calculated value used mainly to detect accidental data alteration.

Clustering: Connecting multiple systems to provide failover, load distribution, or both.

Concurrency control: Methods that coordinate simultaneous database operations.

Constraint: A database rule restricting permitted data or relationships.

Deadlock: A condition in which transactions wait indefinitely for one another's locks.

Digital signature: A public-key mechanism providing integrity and evidence of signing authority.

Durability: The ACID property that committed changes survive relevant failures.

Idempotency: The property that repeating an operation produces the same intended result rather than duplicate effects.

Isolation: The ACID property controlling interference between concurrent transactions.

MAC: A keyed message authentication code used to detect unauthorised modification and authenticate a message to key holders.

Replication: Maintaining copies of data on multiple systems.

RPO: Recovery Point Objective; the targeted maximum age of data after recovery.

RTO: Recovery Time Objective; the targeted maximum time to restore service.

Transaction: A logical unit of database work.

Write-ahead logging: Recording recovery information before corresponding data pages are persisted.

Review Questions

Short Answer Questions

1. What is the purpose of a foreign key?
2. Define atomicity in the ACID model.
3. What is a dirty read?
4. State the difference between RTO and RPO.
5. Why is a backup restoration test necessary?

Long Answer Questions

1. Explain the four ACID properties and illustrate each with an online payment example.
2. Compare pessimistic and optimistic concurrency control, including suitable use cases.
3. Explain how database constraints, audit logs, and cryptographic mechanisms work together to protect integrity.
4. Compare synchronous and asynchronous replication and discuss their effects on performance and recovery.
5. Design a change-control process for a high-risk database schema migration.

Analytical / Application Questions

1. A service operated for 43,200 minutes in a month and was unavailable for 43.2 minutes. Calculate its availability and explain one limitation of relying on this percentage alone.
2. Two customers attempt to purchase the last item in stock at the same time. Analyse the possible concurrency failure and propose transaction and constraint controls.
3. A company has an RPO of 15 minutes and an RTO of one hour, but its only backup is an online daily copy. Evaluate the design and recommend improvements.

Multiple Choice Questions with Answers

Question 1

Which constraint ensures that a child row refers to an existing parent row?

A. CHECK constraint

B. Foreign key

C. DEFAULT constraint

D. UNIQUE constraint

Correct Answer: B. Foreign key

Question 2

Which ACID property requires all operations in a transaction to succeed or none to take effect?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 3

Which anomaly occurs when one transaction reads data written by another transaction that has not committed?

A. Phantom read

B. Lost update

C. Dirty read

D. Write skew

Correct Answer: C. Dirty read

Question 4

A service is available for 99,950 minutes out of 100,000 minutes. What is its availability?

A. 95%

B. 99.5%

C. 99.95%

D. 99.995%

Correct Answer: C. 99.95%

Question 5

What does an RPO primarily describe?

A. The maximum targeted time to restore service

B. The maximum targeted age of recovered data

C. The number of redundant servers

D. The percentage of successful transactions

Correct Answer: B. The maximum targeted age of recovered data

Question 6

Which mechanism provides authentication and integrity using a secret key shared by the validating parties?

A. Plain checksum

B. Unkeyed hash

C. Message authentication code

D. Database index

Correct Answer: C. Message authentication code

Question 7

What is a principal risk of asynchronous replication?

A. It always prevents failover

B. It may lose recent changes if the primary fails

C. It cannot use encryption

D. It prevents all read operations

Correct Answer: B. It may lose recent changes if the primary fails

Question 8

Which practice most directly helps prevent duplicate effects when a client retries an order request?

A. Increasing screen resolution

B. Idempotent request processing

C. Removing primary keys

D. Disabling transaction logs

Correct Answer: B. Idempotent request processing

Question 9

What is a deadlock?

A. A failed checksum comparison

B. A permanently corrupted backup

C. Transactions waiting on one another's locks

D. A replica with no historical data

Correct Answer: C. Transactions waiting on one another's locks

Question 10

Which statement about backup success is most accurate?

A. A successful backup automatically proves recovery will meet the RTO

B. A backup is unnecessary when replication is enabled

C. Restoration testing is needed to demonstrate recoverability

D. Online backups are always protected from ransomware

Correct Answer: C. Restoration testing is needed to demonstrate recoverability