Run a mock round

Both approaches handle concurrent updates to the same data.

Pessimistic locking assumes conflicts are likely, so it locks a row when read: SELECT ... FOR UPDATE or a shared lock. Other transactions block until the lock is released at commit or rollback. This prevents lost updates directly but reduces concurrency and risks deadlocks and long waits. It suits high-contention, short transactions.

Optimistic locking assumes conflicts are rare. You read a row with its version or timestamp, do your work, then update with a condition that the version is unchanged.

UPDATE items SET qty = 5, version = version + 1
WHERE id = 7 AND version = 3;
-- 0 rows affected means someone else changed it

If zero rows are affected, you detect the conflict and retry or report it. This avoids holding locks during user think time and scales well, but every write must check the result and handle retries. Many ORMs expose both, and hybrid strategies are common.

SQL defines four isolation levels that trade consistency against concurrency. They are usually described by which anomalies they permit.

• Read uncommitted: dirty reads allowed, sees uncommitted changes.
• Read committed: no dirty reads, but non-repeatable reads and phantoms can occur. The common default in PostgreSQL and Oracle.
• Repeatable read: rows read stay stable within the transaction, but phantoms may appear. MySQL InnoDB's default, implemented with MVCC.
• Serializable: transactions behave as if run one at a time, preventing all three anomalies, at the cost of blocking or aborts.

level              dirty  non-repeatable  phantom
read uncommitted    yes        yes           yes
read committed      no         yes           yes
repeatable read     no         no            yes
serializable        no         no            no

Implementations vary: MVCC snapshots give readers a consistent view without blocking writers, while lock-based systems block. Higher isolation reduces anomalies but increases contention, deadlocks and retries, so choose per transaction rather than globally.

Scaling relational databases follows a progression, because a single node eventually hits CPU, memory or I/O limits.

1. Optimise first: fix queries, add indexes, tune the buffer pool and connection pooling, and archive old data.
2. Read scaling: add replicas and route reads to them, accepting replication lag. Use a cache such as Redis for hot data.
3. Vertical scaling: more RAM and faster disks, such as NVMe, often beat complex sharding and buy time.
4. Partition large tables by range or hash to keep working sets manageable.
5. Shard: split data across nodes by a shard key so writes scale. This makes cross-shard joins, transactions and unique constraints hard.

app -> primary (writes)
         | replication
         v
     replicas (reads)   + cache

Choose the shard key for even distribution and locality, and plan resharding and rebalancing. Some workloads are better served by a NoSQL or NewSQL store, or by CQRS with separate read and write models.

A clustered index defines the physical order of rows in the table. Because the data is stored in that order, a table can have only one clustered index, usually the primary key when the engine supports clustered storage, as SQL Server does. Range scans and ordered retrieval along the key are fast because rows are adjacent.

A non-clustered index is a separate structure that stores the indexed key plus a pointer back to the row. A table can have many. A lookup may require an extra hop, called a key lookup, to fetch remaining columns. If the index includes all queried columns, it covers the query and avoids that hop.

CREATE CLUSTERED INDEX ix_orders_date ON orders(order_date);
CREATE INDEX ix_orders_customer ON orders(customer_id) INCLUDE (total);

Choose a clustered key that is narrow, unique, stable and ever-increasing, like an identity, to avoid page splits. Wide or random clustered keys such as UUIDs can cause fragmentation and poor insert performance.

ACID describes the guarantees of a transaction, a unit of work that either completes fully or not at all.

• Atomicity: all statements commit or all roll back. Implemented with logging and undo.
• Consistency: the database moves from one valid state to another, respecting constraints, keys and invariants.
• Isolation: concurrent transactions do not observe each other's uncommitted intermediate states; the degree is set by the isolation level.
• Durability: once committed, changes survive a crash, usually by writing to a write-ahead log and fsyncing before acknowledging.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK

The transfer is atomic and consistent, and isolation prevents another transaction from seeing only the debit. Durability is often relaxed slightly for performance, and distributed systems extend the idea with two-phase commit or consensus protocols.

A primary key uniquely identifies each row in a table. It must be unique and not null, and a table has at most one. It may be a single column or a composite of several, and it is usually backed by a unique index. A natural key comes from the data, such as an email or ISBN; a surrogate key is generated, such as an auto-increment id or UUID.

A foreign key is a column, or set of columns, in one table that references the primary key or unique key of another. It enforces referential integrity: a child row cannot reference a parent that does not exist.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

Deletes and updates on the parent can be restricted, cascaded or set null depending on the rule. Index the foreign key column, because otherwise joins and cascade checks scan the child table. Primary keys define identity; foreign keys define relationships.

These three remove data at very different levels.

DELETE FROM t WHERE ... is DML. It removes selected rows, fires triggers, is fully logged, can be rolled back within a transaction and can cascade to child rows. Without a WHERE clause it removes everything, potentially slowly.

TRUNCATE TABLE t is DDL. It removes all rows quickly by deallocating pages rather than logging each row, resets identity counters in many systems, usually cannot be filtered and typically cannot be rolled back in the same way as DELETE. It is much faster for emptying a table.

DROP TABLE t removes the table definition itself plus its data, indexes, constraints and triggers. The table no longer exists until it is recreated.

DELETE FROM logs WHERE created_at < '2020-01-01';
TRUNCATE TABLE staging_events;
DROP TABLE obsolete_table;

Choose based on whether you need selective removal, speed, or removal of the object itself.

A database deadlock is a cycle where two or more transactions each hold a lock the other needs, so neither can proceed.

T1: UPDATE a; UPDATE b;
T2: UPDATE b; UPDATE a;  -- cycle

Most engines detect deadlocks by maintaining a wait-for graph and aborting a victim, which then rolls back and surfaces an error that the application should retry.

Prevention and mitigation:

• Access tables and rows in a consistent order across the application.
• Keep transactions short and avoid user interaction inside them.
• Touch rows in a deterministic order and use appropriate indexes so locks target fewer rows.
• Use lower isolation where acceptable, or optimistic concurrency with version columns.
• Retry on deadlock with backoff, since avoiding all cycles in a busy system is unrealistic.

Deadlocks differ from lock waits: a long wait is usually contention, while a deadlock is a genuine cycle. Monitor deadlock graphs and index scans to find hot spots.

Normalisation organises columns into tables to reduce redundancy and update anomalies. The common normal forms:

• 1NF: atomic values, no repeating groups, each row unique.
• 2NF: 1NF plus no partial dependency on part of a composite key.
• 3NF: 2NF plus no transitive dependency, so non-key attributes depend only on the key.
• BCNF: every determinant is a candidate key, a stricter version of 3NF.

Unnormalised: order(id, customer_name, customer_city, product, qty)
3NF: customer(id, name, city)
     product(id, name, price)
     order(id, customer_id)
     order_item(order_id, product_id, qty)

Each fact is stored once, so updating a customer's city touches one row. Normalisation improves integrity and simplifies writes, and it can cost joins on read. Denormalisation deliberately reintroduces redundancy, for example storing a total or a copied name, to speed reads, and must be maintained carefully with triggers or application logic.