Creates the store of the hermes_outbox table for createPollingOutbox from @chassisjs/hermes. It works with
usePartitionOrderingPolicy and useNoOrderingPolicy.
The claims use SELECT ... FOR UPDATE SKIP LOCKED (MySQL 8.0+), and all the times are computed with the clock of
MySQL. The claims run in READ COMMITTED transactions whatever the isolation level of the pool is, so that they do
not lock the gaps between the rows and do not block the inserts of the application. The other statements change the
rows of hermes_outbox by their primary key, which locks only those rows in REPEATABLE READ too. Still, use a
pool of createPollingPool, whose connections run in READ COMMITTED.
claimPartitions first finds the partitions with undelivered messages with a skip scan of an index (one index
lookup per partition), so the cost of a poll does not grow with the number of undelivered messages, but it grows
with the number of partitions that have them. It adds the new ones to hermes_outbox_partition and removes the free
partitions without undelivered messages. It reads the messages with plain SELECTs: in MySQL, a subquery of
INSERT ... SELECT or DELETE locks the rows it reads, so it would wait for the open transactions of the
application. It does it before the claim, in other statements, so it does not hold the locks of the partitions while
it claims them. The next poll adds a removed partition again when it gets new messages, so a message that commits
during the removal is not lost. The statements change rows found by their primary key (found first with a plain
SELECT when needed): an UPDATE that scans a secondary index waits for the rows that the application inserted and
did not commit yet, even in READ COMMITTED.
markFailed changes a message only when owner still has the claim of the message, or of its partition when the
message itself is not claimed (usePartitionOrderingPolicy).
markDelivered and the renewals try again when they wait for a lock of a row too long (innodb_lock_wait_timeout
of createPollingPool).
markDelivered does not check the claim on purpose: the message is already published, so it is delivered whoever
published it. Checking the claim would only leave a published message undelivered, to be published again.
Creates the store of the
hermes_outboxtable forcreatePollingOutboxfrom@chassisjs/hermes. It works withusePartitionOrderingPolicyanduseNoOrderingPolicy.The claims use
SELECT ... FOR UPDATE SKIP LOCKED(MySQL 8.0+), and all the times are computed with the clock of MySQL. The claims run inREAD COMMITTEDtransactions whatever the isolation level of the pool is, so that they do not lock the gaps between the rows and do not block the inserts of the application. The other statements change the rows ofhermes_outboxby their primary key, which locks only those rows inREPEATABLE READtoo. Still, use a pool of createPollingPool, whose connections run inREAD COMMITTED.claimPartitionsfirst finds the partitions with undelivered messages with a skip scan of an index (one index lookup per partition), so the cost of a poll does not grow with the number of undelivered messages, but it grows with the number of partitions that have them. It adds the new ones tohermes_outbox_partitionand removes the free partitions without undelivered messages. It reads the messages with plainSELECTs: in MySQL, a subquery ofINSERT ... SELECTorDELETElocks the rows it reads, so it would wait for the open transactions of the application. It does it before the claim, in other statements, so it does not hold the locks of the partitions while it claims them. The next poll adds a removed partition again when it gets new messages, so a message that commits during the removal is not lost. The statements change rows found by their primary key (found first with a plainSELECTwhen needed): anUPDATEthat scans a secondary index waits for the rows that the application inserted and did not commit yet, even inREAD COMMITTED.markFailedchanges a message only whenownerstill has the claim of the message, or of its partition when the message itself is not claimed (usePartitionOrderingPolicy).markDeliveredand the renewals try again when they wait for a lock of a row too long (innodb_lock_wait_timeoutof createPollingPool).markDelivereddoes not check the claim on purpose: the message is already published, so it is delivered whoever published it. Checking the claim would only leave a published message undelivered, to be published again.