Race Condition in Gin with Cockroachdb

A race condition in a Gin application using CockroachDB typically arises when multiple concurrent requests read and write the same database rows without appropriate isolation or synchronization. Because CockroachDB provides strong serializable isolation by default, many anomalies are prevented at the SQL level, but application-level races can still manifest through non-atomic operations and improper handling of transaction retries.

Consider an endpoint that reads a row, computes a new value based on that read, and then writes the updated value back. If two requests perform this sequence concurrently, both may read the same initial value, compute updates, and write back results, causing one update to be lost. This pattern is common in counters, inventory reservations, or balance adjustments. In Gin, handlers are invoked concurrently per request, so without explicit transaction sequencing or optimistic concurrency control, the interleaving of reads and writes creates a classic time-of-check-to-time-of-use (TOCTOU) race.

With CockroachDB, the SERIALIZABLE isolation level usually prevents dirty reads and write skew by aborting conflicting transactions. However, if the application does not implement retry logic for transaction aborts, clients may see errors while the race still manifests as failed transactions rather than corrupted data. Additionally, if the application uses lower isolation levels or executes multiple round-trips between reads and writes outside a single transaction, the database cannot coordinate the ordering, and the race becomes observable in the application state.

Another vector involves non-atomic increments or updates. Handlers that perform separate GET and POST steps without a single SQL statement like UPDATE accounts SET balance = balance + $1 WHERE id = $2 expose the operation to races. CockroachDB will serialize conflicting writes at the key level, but without a single-statement update, the application must handle transaction retries correctly. In Gin, failing to parse CockroachDB-induced transaction retry errors and respond appropriately can lead to confusing client behavior that resembles a race condition.

Input validation and binding in Gin can also contribute. If request binding occurs between a read and a write within handler logic, an attacker might manipulate timing or trigger repeated resubmissions, increasing collision probability. Therefore, the combination of concurrent Gin handlers, multi-step business logic, and CockroachDB’s serializable guarantees places responsibility on developers to structure transactions as single, retry-aware units of work.

To eliminate race conditions when Gin interacts with CockroachDB, structure all state-changing operations as single, retry-aware database transactions. Use explicit SQL transactions with serializable isolation and handle transaction retry errors by re-running the transaction block. Below are concrete examples using the pgx driver with CockroachDB and the Gin framework.

Atomic increment with retry logic

Instead of reading and then updating, perform the update in one SQL statement. Wrap it in a transaction with retry logic to handle CockroachDB serializable aborts:

package main

import (
	"context"
	"fmt"
	"net/http"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	pool, err := pgxpool.New(context.Background(), "postgresql://localhost:26257/defaultdb?sslmode=require")
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	r := gin.Default()
	r.PUT("/accounts/:id/deposit", func(c *gin.Context) {
		accountID := c.Param("id")
		var amount float64
		if err := c.BindJSON(&amount); err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
			return
		}

		const maxAttempts = 5
		for attempt := 0; attempt < maxAttempts; attempt++ {
			ctx, cancel := context.WithTimeout(c, 10*time.Second)
			execErr := pool.BeginTxFunc(ctx, pgx.TxOptions{
				IsoLevel: pgx.Serializable,
			}, func(tx pgx.Tx) error {
				var currentBalance float64
				row := tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", accountID)
				if err := row.Scan(¤tBalance); err != nil {
					return err
				}
				newBalance := currentBalance + amount
				_, execErr := tx.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = $2", newBalance, accountID)
				return execErr
			})
			cancel()

			if execErr == nil {
				c.JSON(http.StatusOK, gin.H{"balance": newBalance})
				return
			}

			// Check if error is a serializable retry; in practice inspect error codes/patterns
			// For illustration, we retry on any error; tighten this in production.
			if attempt == maxAttempts-1 {
				c.JSON(http.StatusInternalServerError, gin.H{"error": execErr.Error()})
				return
			}
			time.Sleep(time.Duration(attempt+1) * 50 * time.Millisecond)
		}
	})

	http.ListenAndServe(":8080", r)
}

Conditional update to prevent lost updates

Use a version column or a conditional WHERE clause to ensure the state hasn't changed between read and write. This pattern works well when you cannot collapse operations into a single statement:

// Assume accounts table has a version integer column.
// Gin handler with optimistic concurrency control
r.POST("/transfer", func(c *gin.Context) {
	var req struct {
		FromID   string  `json:"from_id"`
		ToID     string  `json:"to_id"`
		Amount   float64 `json:"amount"`
		ExpectedVersion int64 `json:"expected_version"`
	}
	if err := c.BindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	const maxAttempts = 5
	var err error
	for attempt := 0; attempt < maxAttempts; attempt++ {
		ctx, cancel := context.WithTimeout(c, 10*time.Second)
		err = pool.BeginTxFunc(ctx, pgx.TxOptions{
			IsoLevel: pgx.Serializable,
		}, func(tx pgx.Tx) error {
			var fromVersion int64
			var fromBalance float64
			row := tx.QueryRow(ctx, "SELECT balance, version FROM accounts WHERE id = $1", req.FromID)
			if err := row.Scan(&fromBalance, &fromVersion); err != nil {
				return err
			}
			if fromVersion != req.ExpectedVersion {
				return fmt.Errorf("version mismatch")
			}
			if _, execErr := tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1, version = version + 1 WHERE id = $2 AND version = $3", req.Amount, req.FromID, req.ExpectedVersion); execErr != nil {
				return execErr
			}
			// Similar conditional update for ToID omitted for brevity
			return nil
		})
		cancel()

		if err == nil {
			c.JSON(http.StatusOK, gin.H{"status": "ok"})
			return
		}
		if attempt == maxAttempts-1 {
			c.JSON(http.StatusConflict, gin.H{"error": "concurrency conflict"})
			return
		}
		time.Sleep(time.Duration(attempt+1) * 50 * time.Millisecond)
	}
})

Key remediation practices

• Use a single SQL statement for read-modify-write when possible; CockroachDB’s serializable isolation will serialize conflicting writes safely.
• Always implement retry loops for transaction aborts caused by serializable isolation violations; do not treat these as fatal errors without retry.
• Leverage SELECT … FOR UPDATE for rows that will be updated within the same transaction to lock them explicitly within the serializable schedule.
• Include versioning or conditional WHERE clauses for operations that cannot be reduced to a single statement, enabling optimistic concurrency control.
• Validate and bind input before entering transaction logic to minimize the window of non-atomic operations, but ensure the entire handler logic that depends on consistent reads is inside the transaction.