A circular buffer keeps only the newest fixed number of values. Once every slot is occupied, each write replaces the oldest value instead of adding another row. That makes it useful for compact lists such as recent events, status samples, or the latest comments when retaining older entries is unnecessary.
MySQL can implement this structure with one preallocated InnoDB table. The key is to give every slot a monotonically increasing sequence number, always replace the smallest sequence number, and read the rows in descending sequence order.
The Table
The following example keeps exactly ten values:
CREATE TABLE recent_items (
slot_id TINYINT UNSIGNED NOT NULL,
sequence_no BIGINT UNSIGNED NOT NULL,
payload VARCHAR(255) NULL,
written_at TIMESTAMP(6) NULL,
PRIMARY KEY (slot_id),
UNIQUE KEY uq_recent_items_sequence (sequence_no)
) ENGINE = InnoDB; Initialize the slots once. A NULL payload identifies a slot that has not received a real value yet:
INSERT INTO recent_items (slot_id, sequence_no, payload) VALUES
(0, 0, NULL),
(1, 1, NULL),
(2, 2, NULL),
(3, 3, NULL),
(4, 4, NULL),
(5, 5, NULL),
(6, 6, NULL),
(7, 7, NULL),
(8, 8, NULL),
(9, 9, NULL); The primary key gives each physical slot a stable identity. The unique index on sequence_no both preserves the ordering invariant and lets MySQL find the oldest slot without sorting the complete table.
Why Adding the Capacity Works
Initially, the ten sequence numbers form the consecutive range 0 through 9. Replacing the minimum value 0 with 0 + 10 changes that range to 1 through 10. Repeating the operation changes it to 2 through 11, then 3 through 12, and so on.
After every write, the table therefore still contains ten distinct consecutive sequence numbers. The largest number identifies the newest value, while the smallest identifies the next slot to overwrite. No separate read or write pointer is necessary.
The Fast Single-Writer Path
When one process writes to the buffer, or writers are serialized by the application, one statement replaces the oldest slot:
UPDATE recent_items
SET payload = 'new value',
written_at = CURRENT_TIMESTAMP(6),
sequence_no = sequence_no + 10
ORDER BY sequence_no
LIMIT 1; The constant 10 is the fixed capacity used during initialization. MySQL permits ORDER BY and LIMIT on a single-table UPDATE, so the selection and replacement happen in one statement.
Concurrent Writers
For parallel writers, make ownership of a slot explicit. Begin a transaction, lock the oldest currently available slot, update that exact row, and commit:
START TRANSACTION;
SELECT slot_id
FROM recent_items
ORDER BY sequence_no
LIMIT 1
FOR UPDATE SKIP LOCKED;
UPDATE recent_items
SET payload = ?,
written_at = CURRENT_TIMESTAMP(6),
sequence_no = sequence_no + 10
WHERE slot_id = ?;
COMMIT; The application passes the slot_id returned by the locking read into the prepared UPDATE. FOR UPDATE reserves that slot until commit. SKIP LOCKED lets another writer choose the next-oldest unlocked slot rather than wait for the first transaction.
If all ten slots are locked, the SELECT returns no row. Roll back and retry with bounded backoff. Keep the transaction short: obtain the value before starting it, and do not perform network calls while holding the row lock. MySQL also warns that SKIP LOCKED returns an inconsistent view and is unsafe for statement-based replication; it is appropriate here precisely because this is a queue-like reservation step.
Read the Buffer
Read populated slots from newest to oldest:
SELECT slot_id, payload, written_at
FROM recent_items
WHERE payload IS NOT NULL
ORDER BY sequence_no DESC; The result contains at most ten rows without a wraparound UNION. During concurrent writes, an ordinary consistent read may show the state before an uncommitted replacement; after commit, the new ordering becomes visible as one atomic change.
Changing the Capacity
The increment and the number of physical slots are one invariant. Do not insert an eleventh slot while writers still add ten. To resize the buffer, pause writers, create and seed a replacement table with the new capacity, copy the newest values in order, and swap the tables. This is usually safer than trying to rewrite live sequence numbers.
BIGINT UNSIGNED leaves a very large sequence range. Even so, monitor the maximum value if the buffer is written at an exceptional rate. Renumber only while writes are stopped and the complete table is locked.
A Ring Buffer Is Not a General Queue
This design intentionally overwrites unread data. It has no acknowledgement, retry count, visibility timeout, or dead-letter state, and concurrent sequence order reflects slot reservation rather than necessarily the order in which transactions commit. Those properties are acceptable for a bounded recent-values cache, but not for jobs that must be processed exactly once or retained until acknowledged.
For a work queue, store one row per job and claim pending rows with a short SELECT ... FOR UPDATE SKIP LOCKED transaction. Delete or mark each row only after the worker completes its work. If durable messaging semantics are central to the system, a dedicated message broker is generally a better fit.