How did optimistic locking work in Postgres?

Optimistic locking in PostgreSQL is usually implemented using the Multi-Version Concurrency Control (MVCC) mechanism and a field that stores the version or timestamp.

The idea is that when reading data, no lock is placed, but during an update, it checks that the data has not changed since it was read. If it has changed, the operation is rolled back or retried.

In Go, this can be implemented as follows:

• When selecting data, read the version field (e.g., xmin or a user-defined version).
• When updating in SQL, write a condition WHERE id = ? AND version = ?.
• If the update affects 0 rows, it means the data has changed, and a conflict needs to be handled.

Example SQL:

UPDATE table SET data = $1, version = version + 1 WHERE id = $2 AND version = $3;

In Go code, it looks like this:

result, err := db.ExecContext(ctx, "UPDATE table SET data = $1, version = version + 1 WHERE id = $2 AND version = $3", newData, id, oldVersion)
rowsAffected, _ := result.RowsAffected()
if rowsAffected == 0 {
    // Handle conflict — retry or return an error
}

Thus, optimistic locking allows avoiding read locks and minimizing conflicts during concurrent updates.