Locks, outbox tables and inbox records close different failure windows, so they complement each other rather than compete. A database lock serializes competing access to local state. An outbox commits a business change and the intent to publish an event in one local transaction. An inbox, or processed-message record, makes a redelivered message harmless to local state. None of the three makes an effect happen exactly once across independent systems, because a database, a message broker and an external API each have their own commit point.
This article assumes PostgreSQL as the local database, a broker that delivers at least once, and message handlers that can safely run more than once. Other databases and brokers change the details, but not the boundaries described here.
As an Amazon Associate I earn from qualifying purchases.
Start with the boundary you are protecting
An ACID transaction is local to one service. An operation that spans services is therefore a series of local transactions, and its combined consistency is usually eventual. A saga coordinates that series, typically with compensating steps when a later step fails. Event-driven collaboration can keep services consistent without a distributed transaction, but the programming model is harder: you must design for partial progress, duplicate messages and out-of-order arrival.
Recommended Free Tools
Before choosing a mechanism, name the boundary it has to hold. Table 1 compares the three.
#1 Best Overall
| Mechanism | Boundary it protects | Failure window it closes | What it does not guarantee |
|---|---|---|---|
Row lock (SELECT ... FOR UPDATE) |
Concurrent writers to the same rows in one database | Two transactions changing the same state at the same time | Anything outside that database and transaction; it can block or deadlock |
| Advisory lock | An application-named resource, agreed on by cooperating code | Concurrent code paths that all take the same lock | Protection for any code path that does not acquire it |
| Outbox | Database commit and the intent to publish | An event published for rolled-back state, or an event lost after commit | Exactly-once delivery to the broker; the relay can publish duplicates |
| Inbox (processed-message record) | Repeated receipt of a message and its local business effect | Re-applying a local effect when a message is redelivered | External side effects outside the transaction; marker expiry |
Database locks protect invariants inside one database
PostgreSQL 18 documentation, in its Explicit Locking section, describes row-level locks as blocking conflicting writers and lockers on the same rows until the transaction ends. SELECT ... FOR UPDATE takes such a lock without first changing the selected row. This is PostgreSQL behavior; other databases and distributed lock services may differ.
A row lock is not a distributed lease. It exists only for the life of one transaction on one database, and it says nothing to a service on the other side of a message.
Row locks for a local invariant
Consider reserving stock. Two orders for the last unit must not both succeed, so the reservation reads and updates the stock row under a lock:
BEGIN;
SELECT quantity FROM stock WHERE sku = 'SKU-1042' FOR UPDATE;
-- check quantity, then:
UPDATE stock SET quantity = quantity - 1 WHERE sku = 'SKU-1042';
COMMIT;A second transaction waits at its SELECT until the first commits or rolls back, then reads the updated quantity. Keep the check and the update inside the same transaction; a lock taken after the check protects nothing.
Advisory locks work only if every path takes them
PostgreSQL advisory locks have meanings that the application defines. The server does not require every client to take them, so an advisory lock guards only the code that acquires it. Session-level advisory locks are held until they are released or the session ends. Transaction-level advisory locks, such as those taken with pg_advisory_xact_lock, are released automatically when the transaction ends, which makes them easier to reason about with short transactions.
Rank #2
Deadlocks and waiting
Locks add contention and deadlock risk. PostgreSQL 18 documentation, in the same Explicit Locking section, states:
“PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.”
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
You cannot predict which transaction becomes the victim, so application code must treat that abort as retryable. Two habits reduce the damage. First, acquire multiple locks in a consistent order; for example, sort SKUs ascending before reserving several of them. Second, keep transactions short. Do not hold a transaction open across a network call to another service, or the lock lasts as long as that call.
SKIP LOCKED for claiming queue-like rows
For a queue-like table, SELECT ... FOR UPDATE SKIP LOCKED lets several workers each claim different rows without waiting on one another. The PostgreSQL 18 documentation warns that this produces an inconsistent view of the data and is not suitable for general-purpose querying. Use it to claim work, not to produce reports or totals.
How do you atomically update the database and send messages to a broker?
The core problem is the dual write. A service that updates its database and then publishes a message has two commit points, and a crash can fall between them. Table 2 shows the three possible orderings.
Rank #3
| Ordering | What goes wrong |
|---|---|
| Publish, then commit the database change | If the transaction rolls back, or the service crashes before commit, consumers hold an event for state that never committed. |
| Commit, then publish directly | If the service crashes after commit and before sending, the event can be lost. |
| Insert the event into an outbox table in the same transaction, then relay it | Business state and publication intent commit together. The relay can still publish a message more than once, so downstream handling must absorb repeats. |
Write the event in the same transaction
The transactional outbox pattern description on microservices.io states the solution this way:
Free tools Windows power users keep installed
One-click scans. No signup required.
“The solution is for the service that sends the message to first store the message in the database as part of the transaction that updates the business entities.”
A separate relay process then forwards committed outbox entries to the broker. This avoids a traditional two-phase commit between the database and the broker. A minimal PostgreSQL schema looks like this:
CREATE TABLE outbox (
id bigserial PRIMARY KEY,
aggregate_id text NOT NULL,
event_type text NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
published_at timestamptz
);The order insert and the outbox insert run in the same transaction and commit or roll back together.
Choose a relay: polling or transaction-log tailing
The pattern description names two common relay styles. Table 3 summarizes them.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Rank #4
| Aspect | Polling publisher | Transaction-log tailing |
|---|---|---|
| How it finds new entries | Queries pending outbox records | Reads committed outbox changes from a database log or change stream |
| Database dependence | Works with SQL databases, per the pattern description | Depends on database-specific facilities |
| Ordering | Preserving order can be difficult | Not stated in the pattern description |
| Duplicate handling | Consumers must tolerate duplicates | Consumers must tolerate duplicates |
Duplicates are expected, not exceptional
If the relay publishes a row and crashes before recording that it did, the row is published again after recovery. The relay cannot close this window on its own. Make the publish-then-mark step recoverable, and make the repeat harmless downstream.
Ordering has to be designed, not assumed
Ordering is a requirement in some applications, not a guarantee every broker and relay provides. Work through these steps for each stream:
- Identify the ordering key, usually the aggregate or entity whose events must stay in sequence, such as
order_id. - Define how the relay preserves that key. If several relay workers claim rows with
SKIP LOCKED, one worker can publish a later row while another is still publishing an earlier row for the same key. Give each key a single worker, or accept reordering. - Check whether retries can reorder events. A failed publish retried after later events have gone out breaks the sequence.
- Decide what consumers do when an event arrives out of order, because the relay cannot promise order in every case.
How does a message consumer handle duplicate messages correctly?
At-least-once delivery means a handler can run more than once for the same message. The idempotent-consumer approach records each processed message in the same database transaction as the business change. A uniqueness conflict on that record identifies a duplicate, which can then be acknowledged without applying the effect again.
CREATE TABLE processed_messages (
subscriber_id text NOT NULL,
message_id text NOT NULL,
processed_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (subscriber_id, message_id)
);A handler built on this table follows five steps:
- Begin a database transaction.
- Run
INSERT INTO processed_messages (subscriber_id, message_id) VALUES ($1, $2) ON CONFLICT DO NOTHING. - If zero rows were inserted, the message is a duplicate. Commit and acknowledge the broker without applying the effect.
- Otherwise, apply the business change, including any locks it needs, and commit.
- Acknowledge the broker only after that commit succeeds.
Idempotent by construction, or deduplicated
Some updates are safe to repeat. Setting a projection to the message’s authoritative current value produces the same result on every run. Applying a delta does not. Subtracting a 20-unit debit from a balance subtracts it again on each run, so a delta handler needs the processed-message record or another invariant-preserving strategy.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsScope and retention of the processed-message record
- Scope: key the record on subscriber and message together. Two subscribers may each need to process the same message, and a key on the message identifier alone would make the second one skip it.
- Retention: keep records longer than the longest period in which a message can be redelivered. Once a record is purged, a late retry looks like a new message and applies its effect again.
- Naming: “inbox” is used for receiving-side persistence, but schemas and retention policies vary. The idempotent-consumer approach describes the mechanism, not one universal specification.
Composing the patterns: one order, five commit points
Consider an order service that publishes OrderPlaced, and an inventory service that reserves stock and triggers a payment. Table 4 shows where each boundary sits and what remains open after each stage.
| Stage | Commit point | Protected by | Still open after this stage |
|---|---|---|---|
| 1. Order placed | Order service database commit | Outbox row committed with the order | Relay has not run; the event is pending |
| 2. Relay publishes | Broker accepts the message; relay marks the row | Relay retry and consumer deduplication | A duplicate publication is possible |
| 3. Reservation applied | Inventory database commit | Processed-message record and row locks in one transaction | The broker has not been acknowledged, so redelivery is possible |
| 4. Acknowledgment | Broker acknowledgment after commit | A redelivery finds the processed-message record | Nothing for this boundary |
| 5. Payment or notification | External system | An idempotency key on that external call | Not covered by locks, outbox or inbox |
Stage 5 is the boundary most designs underestimate. An external payment or email call sits outside the database transaction, so the three mechanisms above cannot make it run once. Give that call its own idempotency key, supplied by the caller and honored by the external system.
Retry design without promising exactly-once effects
Retries cause most of the real damage, because each crash point leaves a different state behind. Table 5 lists the important ones.
Quick Recap
| Crash point | What persists | Behavior on retry |
|---|---|---|
| Consumer crashes before its transaction commits | Nothing: no processed-message record and no business change | The message is processed normally on redelivery |
| Consumer crashes after commit, before broker acknowledgment | The processed-message record and the business change | The redelivery hits the record, is acknowledged, and the effect is not repeated |
| Consumer calls an external API, then crashes before commit | The external effect; no local record of it | The call repeats unless the external API deduplicates on a key |
| Relay publishes, then crashes before marking the row | The message at the broker; an unpublished row | The relay republishes; the consumer must absorb the duplicate |
| Service crashes before its business transaction commits | No business row and no outbox row | Nothing is published; the original caller must retry the request |
Rules for retry loops
- Retry only failures that can succeed on a second attempt, such as deadlock aborts and serialization failures. Do not retry validation errors.
- Bound retries with backoff. After a fixed number of attempts, move the message to a dead-letter path and record it as a poison message for inspection.
- Alert on the age of the oldest unpublished outbox row. A stalled relay grows the table quietly and delays every event behind it.
- Repair stuck records by replaying them through the same idempotent path, not by editing business state by hand.
- Never retry a non-idempotent external call without a key.
Questions to answer before you choose
- Which boundary does this step protect: database concurrency, database-to-broker handoff, or broker-to-consumer retry?
- What survives a crash at each commit point in Table 5?
- Is the lock enforced by the database, or does it depend on every caller cooperating?
- What ordering does the key need, and what do the relay and the consumer actually guarantee?
- What does each mechanism cost in contention, relay lag, outbox growth and processed-message storage?
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →
