Prevent race conditions with `Ecto.Query.lock/2`

Two users buying the last item simultaneously can cause overselling. Use PostgreSQL row locks with lock: "FOR UPDATE" to prevent concurrent updates from reading stale data.

# ✅ RACE-CONDITION SAFE - Lock the row first
defmodule Shop.Inventory do
  import Ecto.Query

  def deduct_stock(product_id, quantity) do
    Repo.transaction(fn ->
      product =
        from(p in Product, where: p.id == ^product_id, lock: "FOR UPDATE")
        |> Repo.one!()

      if product.stock >= quantity do
        product
        |> Ecto.Changeset.change(stock: product.stock - quantity)
        |> Repo.update!()
      else
        Repo.rollback(:insufficient_stock)
      end
    end)
  end
end

# ❌ RACE CONDITION - Two processes read stock = 1 simultaneously
defmodule Shop.Inventory do
  import Ecto.Query

  def deduct_stock(product_id, quantity) do
    Repo.transaction(fn ->
      product = Repo.get!(Product, product_id)

      if product.stock >= quantity do
        product
        |> Ecto.Changeset.change(stock: product.stock - quantity)
        |> Repo.update!()
      else
        Repo.rollback(:insufficient_stock)
      end
    end)
  end
end

Without lock: "FOR UPDATE", this race condition happens:

Timeline with no lock:

Time  | Process A                | Process B
------|--------------------------|---------------------------
T1    | Read stock = 1           |
T2    |                          | Read stock = 1
T3    | Check: 1 >= 1 ✓          |
T4    |                          | Check: 1 >= 1 ✓
T5    | Update stock = 0         |
T6    |                          | Update stock = -1 💥

Both processes read stock = 1, both pass validation, both update. Final stock: -1. You just oversold.

Timeline with lock:

Time  | Process A                | Process B
------|--------------------------|---------------------------
T1    | Lock + Read stock = 1    |
T2    |                          | ⏳ Waits for lock...
T3    | Check: 1 >= 1 ✓          |
T4    | Update stock = 0         |
T5    | Commit (releases lock)   |
T6    |                          | Lock + Read stock = 0
T7    |                          | Check: 0 >= 1 ✗
T8    |                          | Rollback

Process B waits for Process A to finish. When B finally reads, it gets the updated stock = 0 and correctly fails the validation.

How FOR UPDATE works

lock: "FOR UPDATE" tells PostgreSQL: “I’m going to modify this row, don’t let anyone else read or modify it until I’m done.”

# Row-level lock with Ecto
from(p in Product, where: p.id == ^id, lock: "FOR UPDATE")
|> Repo.one!()

# Multiple rows
from(p in Product, where: p.category_id == ^category_id, lock: "FOR UPDATE")
|> Repo.all()

The lock is released when the transaction commits or rolls back. Other processes attempting to lock the same row will wait until the lock is released.

Lock types in PostgreSQL

PostgreSQL supports different lock modes:

# FOR UPDATE - Exclusive lock for updates
from(p in Product, lock: "FOR UPDATE")

# FOR UPDATE NOWAIT - Fail immediately if locked
from(p in Product, lock: "FOR UPDATE NOWAIT")
|> Repo.one()
# Returns nil if row is locked by another transaction

# FOR SHARE - Allow concurrent reads, prevent updates
from(p in Product, lock: "FOR SHARE")

Use NOWAIT when you want to fail fast instead of waiting:

defmodule Shop.Inventory do
  import Ecto.Query

  def try_deduct_stock(product_id, quantity) do
    Repo.transaction(fn ->
      case from(p in Product, where: p.id == ^product_id, lock: "FOR UPDATE NOWAIT")
           |> Repo.one() do
        nil ->
          Repo.rollback(:product_locked)

        product when product.stock >= quantity ->
          product
          |> Ecto.Changeset.change(stock: product.stock - quantity)
          |> Repo.update!()

        _product ->
          Repo.rollback(:insufficient_stock)
      end
    end)
  end
end

This returns :product_locked immediately if another transaction is processing the same product, rather than waiting.

Real-world scenarios

Seat reservation system:

defmodule Booking.Reservations do
  import Ecto.Query

  def reserve_seat(event_id, seat_number, user_id) do
    Repo.transaction(fn ->
      seat =
        from(s in Seat,
          where: s.event_id == ^event_id and s.number == ^seat_number,
          lock: "FOR UPDATE"
        )
        |> Repo.one!()

      if is_nil(seat.reserved_by) do
        seat
        |> Ecto.Changeset.change(reserved_by: user_id, reserved_at: DateTime.utc_now())
        |> Repo.update!()
      else
        Repo.rollback(:seat_already_reserved)
      end
    end)
  end
end

Account balance transfers:

defmodule Banking.Transfers do
  import Ecto.Query

  def transfer(from_account_id, to_account_id, amount) do
    Repo.transaction(fn ->
      accounts =
        from(a in Account,
          where: a.id in ^[from_account_id, to_account_id],
          lock: "FOR UPDATE",
          order_by: a.id
        )
        |> Repo.all()

      [from_account, to_account] = accounts

      if from_account.balance >= amount do
        from_account
        |> Ecto.Changeset.change(balance: from_account.balance - amount)
        |> Repo.update!()

        to_account
        |> Ecto.Changeset.change(balance: to_account.balance + amount)
        |> Repo.update!()
      else
        Repo.rollback(:insufficient_funds)
      end
    end)
  end
end

Pro tip: When locking multiple rows, always lock them in a consistent order (like by ID) to prevent deadlocks.

Performance considerations

Row locks block concurrent access, so keep transactions short:

# ✅ GOOD - Fast transaction
def deduct_stock(product_id, quantity) do
  Repo.transaction(fn ->
    product = from(p in Product, where: p.id == ^product_id, lock: "FOR UPDATE") |> Repo.one!()
    # Quick validation and update
    update_product_stock(product, quantity)
  end)
end

# ❌ BAD - Slow transaction holds lock too long
def deduct_stock(product_id, quantity) do
  Repo.transaction(fn ->
    product = from(p in Product, where: p.id == ^product_id, lock: "FOR UPDATE") |> Repo.one!()
    # Expensive operations while holding lock
    send_confirmation_email(product)      # External API call!
    update_analytics(product)             # Slow query!
    update_product_stock(product, quantity)
  end)
end

Move expensive operations outside the transaction or use background jobs.

Use lock: "FOR UPDATE" inside transactions when you need to read and then modify data based on that read. It prevents the classic read-check-update race condition that leads to data corruption in concurrent systems.