Definition
Database Transaction
A single unit of work, consisting of one or more (SQL) operations which are executed atomically such that all changes take effect together (commit) or none do (rollback).
Importance of ACID Properties in a Database Transaction
- ACID is an acronym for: Atomicity, Consistency, Isolation, Durability.
- All transactions must adhere to ACID principles, which are the following:
- Atomicity is when all tasks in a transaction are performed (committed), or none are (rollback).
- This ensures there are no partial writes and corrupt data writes.
- Consistency is where the data written must be valid according to existing rules.
- Isolation indicates each transaction must not interfere with other transactions.
- This helps avoid data inconsistency among concurrent transactions.
- Durability is the concept where when a transaction is committed, the change to the database is permanent, even if system failure occurs.
- Atomicity is when all tasks in a transaction are performed (committed), or none are (rollback).
Example
- Atomicity: A bank transfer where debit and credit must both succeed or both roll back.
- Consistency: Foreign‑key prevents an order pointing to a non‑existent customer.
- Isolation: Two shoppers don’t buy the last ticket twice.
- Durability: After reboot, confirmed bookings are still there.
Using Transactions, States and Updates to Maintain Data Consistency (and Integrity)
- As mentioned before, transactions help maintain data consistency and integrity through its adherence to ACID principles.
- Meanwhile, the current database state is the entire collection of data as it exists at a single moment.
- Consistency means every committed state obeys all keys, validation rules and business constraints.
- An update (INSERT, UPDATE or DELETE) is the individual change executed within the transaction.
- On its own, an update might violate a rule (e.g., make stock negative), but when enclosed in a transaction it either becomes part of a new consistent state or is cancelled.
Example
- Picture an airline database with two key tables:
- Seats(flight_id, seat_no, status) where status can be FREE or BOOKED
- Passengers(flight_id, seat_no, name)
- At 09:00 the database state S₀ is consistent: seat 12A on flight BA123 is FREE and no passenger record references that seat.
- When a customer now books 12A, inside the transaction, the system executes two updates:
- UPDATE Seats SET status = 'BOOKED' WHERE flight_id = 'BA123' AND seat_no = '12A';
- INSERT INTO Passengers VALUES ('BA123', '12A', 'Aria Patel');
- Only when both statements succeed does the DBMS commit, producing state S₁ where seat 12A is BOOKED and Aria Patel is linked to that seat, again a fully valid snapshot.
- If the insert fails (e.g., duplicate key, network glitch), the transaction (update) rolls back, restoring state S₀ so no passenger ever sees a map claiming 12A is simultaneously free and booked.
- In this way transactions move the database cleanly from one consistent state to the next, never exposing impossible seat allocations.
Concurrency in a Data Sharing Situation
- When several users or processes are trying to read and change the same data at the same time, they operate concurrently.
- The DBMS needs to control this situation and does so with the following techniques:
- Locking: the DBMS places shared (read) or exclusive (write) locks
- Optimistic Checks: DBMS lets transactions proceeds and then aborts transactions if a conflicting version was committed first.
Functions Performed on Databases
- Query (read/retrieve):
- A database must let users ask for data typically through SQL SELECT or an API and return rows that match the criteria.
- This powers reports, dashboards and application screens (e.g., “show all orders for customer #42”).
- Update (create/modify/remove):
- The second core function is changing the stored data: INSERT adds new rows, UPDATE edits existing ones, and DELETE removes them.
- Together these operations keep the database current such as recording a new sale, correcting an address or clearing obsolete records.
Role of Data Validation and Data Verification.
- Data validation is an automatic, rule‑based check applied before data are accepted into the system.
- Its role is to catch input that is incomplete, ill‑formed or clearly unreasonable, so only data that fit the required format, range or logic reach the database.
- Data verification is a follow‑up accuracy check that compares the stored value with an authoritative source or a second entry.
- Its role is to confirm that the data, although valid in format, are correct and faithful to the original information.
Example
- Validation:
- Rejecting a date of “31 Feb”, blocking a student mark above 100, or ensuring an email matches a regex pattern.
- By filtering at the point of entry, validation boosts data integrity and reduces downstream errors.
- Verification:
- Double‑keying a passport number and comparing the two entries, hashing a downloaded file and matching the checksum, or cross‑checking a typed invoice total against the printed receipt.
- Verification guards against transcription mistakes, hardware glitches or tampering, ensuring the database holds the true values users expect.
