Why Physical Primary Keys and Optimistic Locks Are Essential for Reliable DB Design

Relational database tables consist of rows and columns, each row uniquely identified by a primary key. Primary keys can be physical (e.g., auto‑increment ID or UUID) or logical (any unique column). Using the physical primary key as a foreign key is recommended because it remains stable, whereas logical keys may become duplicated as business rules evolve.

Physical Primary Key as Foreign Key

Every table typically uses an auto‑increment ID as its physical primary key. Foreign key relationships should reference this ID rather than a logical key, ensuring referential integrity even when logical keys change.

Using Optimistic Lock to Update Records Based on Prior State

In scenarios like task handover, only the first transaction should succeed. Optimistic locking can be implemented by adding a version column that increments on each update. An update succeeds only if the current version matches the expected value, otherwise the transaction must retry.

SELECT * FROM t FOR UPDATE

This pessimistic‑lock query locks rows until the transaction commits. Optimistic locking avoids such locks, improving performance.

UPDATE studentVersion SET ver = ?, name = ? WHERE id = ? AND ver = ?

Updating Independent State Records Without Locks

When updating records whose state is independent of previous values (e.g., VM start/stop), locks are unnecessary. The version check can be omitted.

UPDATE studentVersion SET name = ? WHERE id = ?

Summary

When designing relational databases, add an auto‑increment or UUID ID column as the physical primary key and a numeric version column for optimistic locking. Use the physical key for foreign‑key relationships, employ optimistic locks for concurrent updates to improve performance, and skip locking when record states are independent.