Answer.

Background of the Question

With the advent of multi-user databases, the challenge arose to restrict simultaneous modification of the same data. This led to the development of various locking mechanisms (lock management) that prevent parallel changes and help maintain data integrity.

Problem

Without access control, concurrent operations can lead to data corruption or loss: for example, two transactions update the same row simultaneously, and the changes of one transaction are lost. Insufficiently strict locks result in concurrency issues (race conditions), while excessive locking leads to performance losses (deadlocks, contention).

Solution

Modern DBMSs offer lock levels (row-level, page-level, table-level) and different modes (shared, exclusive, update). Programmers can manage this through transaction isolation settings and explicit lock commands (for example, SELECT ... FOR UPDATE).

Example code:

-- Locking a row until the transaction is complete
BEGIN TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET name = 'New Name' WHERE id = 1;
COMMIT;

Key features:

Lock granularity (row, page, table)
Explicit and implicit locks (through transactions and specific instructions)
Influence of isolation level on locking scheme

Trick Questions.

What is the difference between a read lock (shared lock) and a write lock (exclusive lock)?

A shared lock allows multiple transactions to read data simultaneously but prevents modification. An exclusive lock allows only one transaction to modify data, while all others are denied access.

Can a SELECT statement cause a lock?

Typically, SELECT does not cause locks, but if using SELECT ... FOR UPDATE or at a high isolation level (e.g., SERIALIZABLE), the DBMS may block rows.

Example code:

SELECT * FROM products WHERE id = 10 FOR UPDATE;

Do locks always protect against "lost updates"?

No, if the lock or isolation level is improperly chosen, "lost update" can occur—when changes from one transaction are lost due to another. It is crucial to carefully select concurrency control mechanisms.

Common Mistakes and Anti-Patterns

Incorrect choice of isolation level (too low or too high)
Insufficient explicit locks in critical queries
Overuse of global (table-level) locks leading to performance degradation
Failure to release locks due to unfinished transactions

Real-life Example

Negative Case

In the analytics department, two programs are simultaneously updating order statuses. To speed things up, the level was intentionally set to READ UNCOMMITTED to avoid locking rows. This resulted in conflicts and "lost" updates, damaging some data.

Pros:

Good speed at first

Cons:

Loss/corruption of key data
Difficulty in recovery

Positive Case

In the sales department, critical areas were wrapped in TRANSACTION + SELECT ... FOR UPDATE. Reading and updating operations were delineated at the row level.