APPENDIX · P15 · POSTGRESQL · SYNCHRONIZATION · FOR SLIDES 18–19
← Back to Slide 18
P15. Transactional outbox: pre-delivery event
-- веб-база подготовлена; после Reset: level 13, outbox пуст
BEGIN;
UPDATE player_profiles SET level = level + 1 WHERE player_id = 1042;
INSERT INTO outbox (topic, payload)
VALUES ('profile.changed', '{"player_id": 1042}');
COMMIT;
-- событие ЕЩЁ не доставлено в Redis
SELECT id, topic, payload FROM outbox
WHERE sent_at IS NULL ORDER BY id LIMIT 100;
SELECT count(*) AS unsent FROM outbox WHERE sent_at IS NULL;
-- worker: DEL в Redis; sent_at только после успеха
What the result should be
After Reset: 1042 | 14, 2001 | 7
id 1 | profile.changed | {"player_id":1042}
unsent = 1 (pending delivery)
Worker: DEL player:1042:profile
in Redis; after success—the `sent_at` timestamp.
Failure before the timestamp: retry; DEL is idempotent.
A retry of the Run without a Reset will add the event.
dual write
Outbox
Processor
Commit in PostgreSQL, then DEL in Redis: anything that falls between them—the cache has expired before the TTL, and no one knows.
The event is written in the same transaction as the change: either both, or neither.
Reserves an event (lease/short transaction), performs a DEL, and sets `sent_at` only after success; if there is a failure, it retries.
There is no mutual commit between PostgreSQL and Redis. Until the event is marked as committed in PostgreSQL, the worker may repeat the DEL command. Lag monitoring and PostgreSQL consistency are required.