// We have a table with a product that has a view counter // table goods: // ID | cnt_view // ----------- // 1 | 100 // 2 users started viewing the product with id = 1 simultaneously // and we have two parallel transactions in the database // what will be the result? postgress func IncrementView(ctx context.Context, db *sql.DB, id int64) error { tx, err := db.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() var cnt int err = tx.QueryRowContext(ctx, "SELECT cnt_view FROM goods WHERE id = $1", id, ).Scan(&cnt) if err != nil { return err } cnt++ _, err = tx.ExecContext(ctx, "UPDATE goods SET cnt_view = $1 WHERE id = $2", cnt, id, ) if err != nil { return err } return tx.Commit() }

In PostgreSQL, when two parallel transactions read and update the same view count value, a race condition can occur.

What happens:

1. Both transactions start and read the current value cnt_view = 100.
2. Both increase the local variable cnt to 101.
3. Both try to write cnt_view = 101 back to the table.

As a result, despite two reads, the counter only increases by 1, not 2.

This happens because transactions are not synchronized and do not use locks or atomic operations.

To avoid this problem, you can:

• Use an UPDATE statement with an increment directly, without prior reading:

UPDATE goods SET cnt_view = cnt_view + 1 WHERE id = $1;

• Or use row locking (SELECT ... FOR UPDATE) before reading to ensure sequential access.

• Or use a serializable transaction isolation level, but this may reduce performance.

Example of a corrected function:

func IncrementView(ctx context.Context, db *sql.DB, id int64) error {
    _, err := db.ExecContext(ctx, "UPDATE goods SET cnt_view = cnt_view + 1 WHERE id = $1", id)
    return err
}

Thus, the counter will be correctly incremented during parallel requests.