// table goods: // ID | cnt_view // ----------- // 1 | 100 // 2 users started viewing the product with id = 1 simultaneously // and two parallel transactions were launched in our database // explain what the result will be? PostgreSQL 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() }

The provided code shows two parallel transactions reading the value of cnt_view for a product with id=1, increasing it by 1, and writing it back.

In PostgreSQL, the default transaction isolation level is Read Committed. This means each transaction only sees committed data at the start of the query.

Scenario:

1. Both transactions read cnt_view = 100.
2. Both increase cnt_view to 101.
3. Both attempt to write 101.

The problem is a lost update. The second transaction, upon commit, will encounter a lock error (serialization failure) or wait until the first transaction completes.

To prevent this, you should lock the row during reading, for example, using SELECT ... FOR UPDATE, so that the second transaction waits for the first to finish and then reads the updated value.

Example fix:

err = tx.QueryRowContext(ctx, "SELECT cnt_view FROM goods WHERE id = $1 FOR UPDATE", id).Scan(&cnt)

This guarantees sequential updates to the view counter without data loss.