Why Files Fail as Databases: Concurrent Access, Update Anomalies & Crash Recovery

Suppose you are an engineer building the account balance service for a fintech startup.

To keep your deployment simple, you decide to store user balances inside a plain text CSV file on local disk (balances.csv):

user_id,account_number,balance_cents
101,ACC-9810,50000
102,ACC-4412,120000
103,ACC-3319,7500

When User 101 deposits $50, your application opens balances.csv, reads the text line, parses 50000, adds 5000 cents, rewrites the file, and closes it.

Everything works in local testing.

Then you launch your product to 50,000 active users.

Within two days, financial disaster strikes:

Lost Updates: Two concurrent deposit requests arrive for User 101 at the exact same millisecond. Request A reads balance 50000. Request B reads balance 50000. Request A writes back 55000. One millisecond later, Request B overwrites the file with 52000. User 101’s $50 deposit vanishes.
Corrupted Files on Crash: Mid-way through rewriting balances.csv, your server experiences a physical power failure. The file is left truncated halfway through line 2. The entire file becomes un-parseable syntax junk.
Unusable Latency: To check User 103’s balance, your server opens a 10-gigabyte file and reads every line sequentially from line 1 down to line 4,000,000. Lookups take 12 seconds per API request.

These failures explain why flat files cannot act as production database engines.

The 5 Failure Modes of File-Based Persistence

                                RAW FILE PERSISTENCE CEILING
                                
  +-----------------------------------------------------------------------------------+
  | balances.csv / data.json                                                         |
  |                                                                                   |
  |  1. Race Conditions    ---> Concurrent threads overwrite each other's updates.    |
  |  2. Partial Writes     ---> Power failure mid-write corrupts the entire file.     |
  |  3. Table Scans        ---> Reading record #5,000,000 forces O(N) linear scans.  |
  |  4. Lock Bottlenecks   ---> OS file locks block all reads during a write.         |
  |  5. Update Anomalies   ---> Redundant data fields drift out of sync.              |
  +-----------------------------------------------------------------------------------+

1. Race Conditions & The Lost Update Problem

When multiple application threads or web processes read and write to the same physical file concurrently, file system I/O provides no transaction isolation.

THREAD A (User 101 +$50)                          THREAD B (User 101 +$20)
------------------------                          ------------------------
1. Reads line: balance = 50000                    
                                                  2. Reads line: balance = 50000
3. Computes 50000 + 5000 = 55000                  
4. Writes file: "101,ACC-9810,55000"              
                                                  5. Computes 50000 + 2000 = 52000
                                                  6. Writes file: "101,ACC-9810,52000"
                                                     (Thread A's $50 deposit is LOST!)

To fix this with plain files, you would have to acquire an OS-level file lock (flock) on every access. But locking the entire file turns your web server into a single-threaded bottleneck.

2. Partial Writes and Disk Corruption on Crash

When an application calls fileOutputStream.write(bytes), the data flows through several intermediate memory buffers:

$\text{JVM Heap Buffer} \longrightarrow \text{OS Page Cache} \longrightarrow \text{Disk Controller Buffer} \longrightarrow \text{Physical Disk Platter}$

If power cuts out while the disk controller is overwriting sector 402:

The beginning of the file contains new data.
The middle of the file contains zeroes or partial binary fragments.
The end of the file contains old data.

Without crash-recovery algorithms (like Write-Ahead Logging), a partial write corrupts the file permanently.

3. The $O(N)$ Linear Scan Penalty

To locate User 103 in a CSV file, an application must execute a line-by-line string parsing loop:

public UserAccount findUser(String targetUserId) throws IOException {
    try (BufferedReader reader = new BufferedReader(new FileReader("balances.csv"))) {
        String line;
        while ((line = reader.readLine()) != null) {
            String[] fields = line.split(",");
            if (fields[0].equals(targetUserId)) { // Scans every single line sequentially!
                return new UserAccount(fields[0], fields[1], Double.parseDouble(fields[2]));
            }
        }
    }
    return null;
}

Finding a record in a 10,000,000-row file requires checking 10,000,000 strings. Searching takes $O(N)$ linear time.

Database engines solve this using indexed tree data structures (B+ Trees) to jump straight to target records in $O(\log N)$ time (3 to 4 disk page reads).

4. Redundancy & Update Anomalies

Suppose your CSV file stores user names alongside orders:

order_id,user_id,user_email,product
1001,101,[email protected],Laptop
1002,101,[email protected],Mouse
1003,101,[email protected],Keyboard

If Alex changes their email address to [email protected], your application must locate and update every historical row containing [email protected].

If an application crash or network glitch aborts the update midway through line 2, your file contains inconsistent state: Order 1001 lists [email protected], while Order 1002 lists [email protected].

This flaw led Edgar F. Codd to formulate the Relational Model and Normalization Rules in 1970.

What a Real Database Management System (DBMS) Provides

A Database Management System is a specialized mediating engine positioned between application code and raw physical storage hardware.

APPLICATION CODE
  |
  | (SQL Queries: SELECT, INSERT, UPDATE, DELETE)
  v
+-----------------------------------------------------------------------------------+
| DATABASE MANAGEMENT SYSTEM (DBMS)                                                 |
|                                                                                   |
|  - Storage Engine: Translates records into fixed-size 16KB Pages & Blocks         |
|  - Index Engine: B+ Trees for O(log N) record searches                            |
|  - Concurrency Control: MVCC & Locking to isolate concurrent operations           |
|  - Crash Recovery: Write-Ahead Logging (WAL) & ARIES for zero-data-loss recovery  |
|  - Query Planner: Cost-Based Optimizer choosing efficient join algorithms        |
+-----------------------------------------------------------------------------------+
  |
  v (Block I/O)
PHYSICAL DISK HARDWARE

Quick Summary

Plain files fail as databases because they lack concurrency control, index search algorithms, and crash recovery guarantees.
Concurrent file writes create lost updates; power failures during file overwrites cause permanent data corruption.
Reading records from unindexed flat files forces expensive $O(N)$ linear scans across the entire disk file.
A DBMS acts as a mediating storage engine, organizing data into fixed-size pages, B+ tree indexes, and Write-Ahead Logs.

References & Further Reading

Gray, J. (1978). Notes on Data Base Operating Systems. Operating Systems, Lecture Notes in Computer Science (Vol. 60, pp. 393–481). Springer.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th Edition) — Storage and File Structure. McGraw-Hill.
Stonebraker, M. (1981). Operating System Support for Database Management. Communications of the ACM, 24(7), 412–418.