Databases

Database programs like SQLite3 and PostgreSQL have built-in strategies for handling locking to ensure data consistency and prevent race conditions when multiple transactions are accessing the same data concurrently. Here’s an overview of how each handles locking:

SQLite3

SQLite3 uses a combination of different locking mechanisms to manage concurrent access to the database:

Locking Modes:

• SHARED Lock: Allows multiple readers but no writers. When a transaction starts reading data, it acquires a shared lock.
• RESERVED Lock: Acquired when a transaction intends to write data. It allows other readers but prevents new writers.
• PENDING Lock: Indicates that a transaction is about to write data. It prevents new readers from acquiring a shared lock.
• EXCLUSIVE Lock: Acquired when a transaction is writing data. It prevents all other transactions from reading or writing.

Concurrency Control: WAL (Write-Ahead Logging): A mode that allows readers and writers to operate concurrently by writing changes to a separate log file before applying them to the main database file.

Example in SQLite3:

BEGIN TRANSACTION;
SELECT * FROM articles WHERE id = 1; -- Acquires a SHARED lock
UPDATE articles SET title = 'New Title' WHERE id = 1; -- Acquires an EXCLUSIVE lock
COMMIT;

PostgreSQL

PostgreSQL uses a more sophisticated approach to handle locking and concurrency control:

Locking Levels:

• Row-Level Locks: Locks individual rows to allow high concurrency. Types include FOR UPDATE and FOR SHARE.
• Table-Level Locks: Locks entire tables. Types include ACCESS SHARE, ROW SHARE, EXCLUSIVE, etc.

MVCC (Multi-Version Concurrency Control): PostgreSQL uses MVCC to manage concurrent transactions. Each transaction sees a snapshot of the database at a particular point in time, allowing readers and writers to operate without blocking each other.

Deadlock Detection: PostgreSQL has built-in deadlock detection to identify and resolve deadlocks by aborting one of the conflicting transactions.

Example in PostgreSQL:

BEGIN;
SELECT * FROM articles WHERE id = 1 FOR UPDATE; -- Acquires a row-level lock
UPDATE articles SET title = 'New Title' WHERE id = 1;
COMMIT;

Summary

• SQLite3: Uses different locking modes (SHARED, RESERVED, PENDING, EXCLUSIVE) and supports WAL for better concurrency.
• PostgreSQL: Uses row-level and table-level locks, MVCC for high concurrency, and has built-in deadlock detection.

Both SQLite3 and PostgreSQL provide robust mechanisms for handling locking to ensure data consistency and prevent race conditions in concurrent environments.