Queue (SQL)#
Note
Not part of core. Install it separately:
composer require kinetis/queue-sql
Adds MySQL or Postgres as a backend for Queue, storing jobs in a database you already run. Switching to it changes configuration, not application code.
QUEUE_CONNECTION=sql
DB_CONNECTION=mysql # or "pgsql"
DB_HOST=127.0.0.1
DB_NAME=app
DB_USER=app
DB_PASSWORD=secret
vendor/bin/kinetis queue:work --queue=high,default
pop() reserves a job with SELECT ... FOR UPDATE SKIP LOCKED, so the
server must be MySQL 8.0+, MariaDB 10.6+ or Postgres 9.5+. An older
server rejects the clause, and pop() fails.
Configuring#
The DB_* keys are the ones kinetis/database-bridge reads (see
Database). QUEUE_VISIBILITY_TIMEOUT_SECONDS, described below,
is the one key this package adds.
Create the table#
kinetis/queue-sql ships one Migrations stub per dialect for the
kinetis_queue_jobs table:
vendor/kinetis/queue-sql/resources/migrations/create_kinetis_queue_jobs_table.mysql.php.stub
vendor/kinetis/queue-sql/resources/migrations/create_kinetis_queue_jobs_table.pgsql.php.stub
Copy the one matching your database and run vendor/bin/kinetis migrate,
as Tables packages need describes; with the queue on a named
connection, the stub goes in that connection’s directory.
SQL mechanisms describes the columns the backend
runs on.
Enqueueing inside a transaction#
Queue’s QueueInterface::push() is not enlisted in a transaction
the caller has open: it runs its INSERT on the queue’s own connection,
so the job is enqueued whether or not the surrounding transaction
commits. Kinetis\QueueSql\SqlQueue::pushOn() puts the row on a
transaction you already hold, so the job and the writes it belongs to
land together:
use Kinetis\Persistence\Contract\SqlTransaction;
$this->transactions->transaction($this->db, function (SqlTransaction $tx) use ($orderId): void {
$tx->execute('UPDATE orders SET status = ? WHERE id = ?', ['paid', $orderId]);
$this->queue->pushOn($tx, new SendReceipt($orderId));
});
The row becomes visible and durable only if that transaction commits. A
throw before the commit rolls it back with the caller’s other work, and a
COMMIT that fails leaves the outcome unknown — see Database’s
“When a write’s outcome is unknown”. Push telemetry closes when the
INSERT statement completes, so the span reports the enqueue statement
rather than the later commit.
pushOn() runs that one statement and nothing else: it never commits,
rolls back, nests a transaction, or falls back to the connection the
queue was built with. Addressing the database that holds
kinetis_queue_jobs is therefore the caller’s job —
the queue’s connection name picks the connection behind push() and
does not redirect a transaction you supply.
The signature belongs to this package, not to QueueInterface, so a
caller needs the SqlQueue itself rather than the QueueInterface the
container binds. SqlQueueFactory::fromConfig() returns that class.
Build it once and register that one object under both ids:
use Kinetis\Queue\QueueInterface;
use Kinetis\QueueSql\SqlQueue;
use Kinetis\QueueSql\SqlQueueFactory;
$queue = SqlQueueFactory::fromConfig($config);
$app->instance(SqlQueue::class, $queue);
$app->instance(QueueInterface::class, $queue);
$app->onDispose($queue->dispose(...));
Ordinary QueueInterface consumers and pushOn() callers then share
one backend instance and its one connection pool. Binding only
SqlQueue::class leaves the default QueueInterface binding in place,
and it builds a second SqlQueue with a pool of its own.
The factory opened that connection, so the queue owns it and the
onDispose() line is what closes it when the worker ends — the same
registration the default QueueInterface binding makes for the backend
it builds. See Appendix: Queue Contracts’s “Connection ownership”, which
also covers a SqlQueue constructed directly around a link the
application already has.
Appendix: Queue Contracts’s “Multiple backends” shows the same registration
where several backends run side by side. pushOn() takes any
Database SqlTransaction. An ORM transaction session
passes its SessionTransaction as the callback’s second argument —
Enqueueing a job with the
session shows it, and what a
job can carry from entities the session inserts.
Handing off to another backend atomically#
pushOn() is atomic only for a job that runs from the SQL queue. A
transactional outbox for work that belongs on Redis, SQS or RabbitMQ, or
that is a broadcast, uses a relay job: inside the transaction,
pushOn() a SQL job whose handler performs the step no transaction can
cover. The SQL row commits or rolls back with the business write; the SQL
queue’s retries then carry the hand-off through.
use Kinetis\Queue\Job;
use Kinetis\Queue\QueueInterface;
final readonly class ForwardInvoice implements Job
{
public function __construct(public string $invoiceId) {}
public function handle(QueueInterface $queue): void
{
$queue->push(new GenerateInvoicePdf($this->invoiceId));
}
}
$this->transactions->transaction($this->db, function (SqlTransaction $tx) use ($invoiceId): void {
$tx->execute('UPDATE invoices SET status = ? WHERE id = ?', ['issued', $invoiceId]);
$this->queue->pushOn($tx, new ForwardInvoice($invoiceId), maxAttempts: 10);
});
handle() resolves QueueInterface from the container, so the relay
reaches Redis, SQS or RabbitMQ only if that is the bound backend. Do not
register the SQL queue under QueueInterface::class here, as the section
above does: the relay would push back into the SQL queue. Build it on a
named connection and register it under SqlQueue::class alone;
QueueInterface stays on the downstream backend, the behavior “Binding
only SqlQueue::class leaves the default QueueInterface binding in
place” describes above:
QUEUE_CONNECTION=redis
QUEUE_LEDGER_CONNECTION=sql
DB_LEDGER_CONNECTION=pgsql
DB_LEDGER_HOST=10.0.0.6
$queue = SqlQueueFactory::fromConfig($config, 'ledger');
$app->instance(SqlQueue::class, $queue);
$app->onDispose($queue->dispose(...));
Appendix: Queue Contracts’s “Multiple backends” covers the keys and
QUEUE_CONNECTION_NAME. A SQL queue worker must run, naming that
connection: vendor/bin/kinetis queue:work --connection=ledger.
Set a retry cap.
QUEUE_MAX_ATTEMPTSdefaults to0, so a relay job that fails once, because the downstream backend is down, is given up on, leaving only a line from the defaulterror_log()logger. PassmaxAttemptstopushOn(), and keep the final failure: install Failed Jobs to record it for retry, or make it visible with a durable logger or aJobFailedPermanentlylistener — see Queue’s Retries and failures.Use one database. The transaction must address the database that holds
kinetis_queue_jobs. With the SQL queue on a named connection, the writes belong on that same database: an ORM entity declares it with#[Entity(connection: 'ledger')]and runs throughOrmFactoryRegistry::factory('ledger')->transaction()(ORM’s “Entities on other connections”). A session ondefaultcannot enlist a queue on another database.Make the downstream step idempotent. Delivery stays at least once across the hop: the relay can run again, including after the downstream
push()had an unknown outcome, so the downstream job may be enqueued twice. Idempotency belongs to that job — see Queue’s A job can run more than once. A relayed broadcast arrives at least one SQL poll interval after the commit and can fan out twice. Many applications prefer a direct broadcast after the commit and accept losing the occasional real-time hint.Do not re-push after an unknown
COMMIT. The queue row shares the business write’s fate, which is what the pattern buys. Establish what the database holds first — see Database’s When a write’s outcome is unknown. A secondpushOn()after a commit that did land enqueues a second relay.
Visibility timeout#
QUEUE_VISIBILITY_TIMEOUT_SECONDS (default 300, at least 1) is how
long a reserved job belongs to its worker. When a worker dies before
settling a job, the job becomes poppable again once its reservation is
older than this, with its attempt count increased. Constructing
SqlQueue directly takes the same value as its visibilityTimeoutSeconds
argument.
queue:work renews the reservation automatically while the job runs, at
half this window, so the setting sizes how long a crashed worker’s row
waits to come back rather than how long a job may take. A handler that
never yields to the event loop cannot be renewed, and neither can one
whose worker has died. Should a reservation lapse anyway, the first
worker’s late settlement is rejected rather than touching the new
reservation, and the worker reports it as a lost settlement (see
Queue’s “When a settlement is lost”).
Reservation times are written and compared with each worker’s own clock, not the database’s — a renewal included — so keep worker clocks synchronized: skew makes a reservation expire early or late by the difference.
Clearing a queue#
SqlQueue declares ClearableQueueInterface (see Queue’s
“Clearing is a separate capability”). Clearing deletes the queue’s
unreserved rows, delayed ones included, and reports how many. A
reservation past its timeout is left in place, since its worker may
still be running the job.
Delays and retries#
A delayed job becomes available to the first pop() after its delay,
measured with the pushing and popping hosts’ clocks, so it runs late
while every worker is busy. Retries follow Queue: maxAttempts,
QUEUE_MAX_ATTEMPTS and QUEUE_RETRY_BASE_DELAY_SECONDS. A delayed
retry moves the row’s own available_at, so it waits exactly as a
delayed push does and needs no schema change.
Stored times#
available_at, reserved_at and created_at hold UTC wall-clock times
(Y-m-d H:i:s, no offset) taken from the worker’s clock, whatever PHP’s
date.timezone. Workers configured with different time zones therefore
agree on what is due or expired, and a daylight-saving change in PHP’s
zone moves no cutoff. The migration stubs use DATETIME on MySQL and
TIMESTAMP (without time zone) on Postgres; both store the values as
written. MySQL’s own TIMESTAMP type would interpret them in the
connection’s session time_zone and, in the default strict SQL mode,
reject a value that falls in that zone’s skipped daylight-saving hour.
SqlQueue compares these columns only with cutoffs it computes in PHP.
Hand-written SQL that compares them with NOW() or CURRENT_TIMESTAMP
is correct only while the connection’s session time zone is UTC, because
both functions return the session’s local time. Compare with
UTC_TIMESTAMP() on MySQL and NOW() AT TIME ZONE 'UTC' on Postgres,
which are correct in any session time zone.
Tables created from a stub released before 1.6.9 have MySQL TIMESTAMP
columns. They keep working while the connection’s session time zone has
no daylight saving, such as UTC. Otherwise convert them. MySQL converts
each value in the session time zone of the ALTER, so run it with the
time zone the queue’s connection uses:
ALTER TABLE kinetis_queue_jobs
MODIFY available_at DATETIME NOT NULL,
MODIFY reserved_at DATETIME NULL,
MODIFY created_at DATETIME NOT NULL;
Releases up to 1.6.8 wrote these columns in PHP’s own time zone. An
application upgrading from one with a non-UTC date.timezone sees the
times of rows already in the table shift once by that zone’s offset: a
delayed job becomes due earlier or later by the offset, and a reserved
job can be reclaimed early. Drain the queue, or deploy while it is idle.
Named connections#
QUEUE_CONNECTION_NAME=reports
QUEUE_REPORTS_CONNECTION=sql
DB_REPORTS_CONNECTION=mysql
DB_REPORTS_HOST=127.0.0.1
A connection’s name scopes its selector, its DB_* keys and
QUEUE_VISIBILITY_TIMEOUT_SECONDS; default reads the plain keys.
QUEUE_CONNECTION_NAME makes reports the bound QueueInterface, and
queue:work --connection=reports runs it either way. See Queue’s
“Named connections” and Configuration.
See also#
Queue — jobs, workers, retries and delivery guarantees.
Appendix: Queue Contracts — reservation fencing and delivery contracts.
Database — connecting to MySQL and Postgres.
Migrations — running the table migration.