Spanner 队列至少传递一次消息,专为跨多个接收器的并行处理而设计。当同一实体(例如客户账号、订单或设备)的多条消息发送到队列时,不同的工作器可以同时拉取和处理这些消息。即使一条消息先发送,网络延迟或重试也可能会减慢第一个工作器的速度,而第二个工作器会完成后面的消息。
如果整个队列中的每条消息都按严格的全局顺序处理,那么一个缓慢的工作器可能会导致整个队列停滞(这种现象称为队头阻塞)。不过,大多数应用都不需要让整个数据库中的每条消息都在一个全局队列中等待。相反,您只需要按顺序运行同一实体的任务,而不同实体的任务则并行运行。您可以根据工作负载,使用以下模式按实体顺序处理消息。
为顺序工作流链接消息
如果实体的后台作业由依赖的顺序步骤(例如 step-1,后跟 step-2 和 step-3)组成,并且外部请求不会并行向该工作流添加竞争任务,那么您希望每个步骤仅在上一步完成后才开始。
如需强制执行分步排序,请在初始阶段仅将第一步加入队列。当工作器完成当前步骤的处理后,它会确认当前消息 (DELETE ... ASSERT_ROWS_MODIFIED 1),并在同一读写事务中插入下一步的消息。
由于在同一事务中完成并确认当前步骤之前,系统不会创建下一步的消息,因此同一工作流的两个步骤绝不会同时存在于队列中,并且无法由并发工作器无序处理。
例如,当员工完成入职工作流的 step-1 并安排 step-2 时:
GoogleSQL
-- Inside the worker transaction after finishing step-1:
-- 1. Acknowledge the current step
DELETE FROM OnboardingQueue
WHERE CustomerId = 456 AND StepName = 'step-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Enqueue the next step in the sequence
INSERT INTO OnboardingQueue (CustomerId, StepName, DeliverTime, Payload)
VALUES (456, 'step-2', CURRENT_TIMESTAMP(), b'{"stage": "provision_resources"}');
PostgreSQL
-- Inside the worker transaction after finishing step-1:
-- 1. Acknowledge the current step
DELETE FROM onboardingqueue
WHERE customerid = 456 AND stepname = 'step-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Enqueue the next step in the sequence
INSERT INTO onboardingqueue (customerid, stepname, deliver_time, payload)
VALUES (456, 'step-2', CURRENT_TIMESTAMP, CAST('{"stage": "provision_resources"}' AS bytea));
使用有序工作表和唤醒队列
但是,当您需要特定实体的所有任务按提交顺序运行时,即使外部请求、网络钩子或用户操作在早期任务仍在处理时添加了新任务,此模式也支持符合单个拆分写入容量的工作负载。
如需按实体顺序处理并发任务,请扩展脏标志模式,方法是将有序任务存储在交错表中,并仅将队列用于轻量级唤醒通知。
首先,在跟踪有效声明的父实体表中交错有序工作表(使用提交时间戳)和唤醒队列。为避免在分块之间出现热点,请选择一个可均匀分布实体的主键(例如 UUID 或位反转序列):
GoogleSQL
CREATE SEQUENCE CustomerIdSeq OPTIONS (sequence_kind = 'bit_reversed_positive');
CREATE TABLE Customers (
CustomerId INT64 NOT NULL DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE CustomerIdSeq)),
CustomerName STRING(1024),
IsProcessing BOOL NOT NULL DEFAULT (FALSE),
ActiveClaimId STRING(36),
ProcessingExpiresAt TIMESTAMP,
) PRIMARY KEY (CustomerId);
CREATE TABLE CustomerWorkItems (
CustomerId INT64 NOT NULL,
CreatedAt TIMESTAMP NOT NULL DEFAULT (PENDING_COMMIT_TIMESTAMP())
OPTIONS (allow_commit_timestamp = true),
BatchIndex INT64 NOT NULL DEFAULT (0),
WorkType STRING(128) NOT NULL,
WorkPayload JSON NOT NULL,
) PRIMARY KEY (CustomerId, CreatedAt, BatchIndex),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
CREATE QUEUE CustomerDirtyQueue (
CustomerId INT64 NOT NULL,
MessageId STRING(36) NOT NULL,
Payload BYTES(MAX) NOT NULL,
) PRIMARY KEY (CustomerId, MessageId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
PostgreSQL
CREATE SEQUENCE customeridseq BIT_REVERSED_POSITIVE;
CREATE TABLE customers (
customerid bigint NOT NULL DEFAULT nextval('customeridseq'),
customername varchar(1024),
isprocessing boolean NOT NULL DEFAULT false,
activeclaimid varchar(36),
processingexpiresat timestamptz,
PRIMARY KEY (customerid)
);
CREATE TABLE customerworkitems (
customerid bigint NOT NULL,
createdat spanner.commit_timestamp NOT NULL DEFAULT spanner.pending_commit_timestamp(),
batchindex bigint NOT NULL DEFAULT 0,
worktype varchar(128) NOT NULL,
workpayload jsonb NOT NULL,
PRIMARY KEY (customerid, createdat, batchindex)
) INTERLEAVE IN PARENT customers ON DELETE CASCADE;
CREATE QUEUE customerdirtyqueue (
customerid bigint NOT NULL,
messageid varchar(36) NOT NULL,
payload bytea NOT NULL,
PRIMARY KEY (customerid, messageid)
) INTERLEAVE IN PARENT customers ON DELETE CASCADE;
有了此架构后,发送器和工作器会使用以下工作流进行协调:
- 插入有序任务和唤醒消息:在发送方事务中,将任务插入
CustomerWorkItems(使用PENDING_COMMIT_TIMESTAMP()和从零开始的BatchIndex对同一事务中的多个任务进行排序),并将载荷为空 (b'') 的单个唤醒消息插入CustomerDirtyQueue。 - 只需三个步骤,即可声明、处理和释放实体:
- 第 1 步(在事务 1 中声明实体):当工作器收到来自
RECEIVE_CustomerDirtyQueue的唤醒消息时,它会运行一个简短的读写事务:- 检查
Customers并确认传入的唤醒消息 (DELETE ... ASSERT_ROWS_MODIFIED 1)。如果另一个工作器已持有有效声明(IsProcessing = TRUE和ProcessingExpiresAt > CURRENT_TIMESTAMP()),则提交事务 1 并退出。活跃工作器会在释放其声明之前检查剩余任务。 - 否则,从
CustomerWorkItems中读取按CreatedAt ASC, BatchIndex ASC排序的最早一批任务(例如LIMIT 25),并删除所有剩余的过期后备消息(其MessageId为先前的ActiveClaimId)。 - 如果返回了任务,请在
Customers上设置IsProcessing = TRUE、ActiveClaimId和ProcessingExpiresAt(例如未来 10 秒),并将后备消息 (MessageId = ActiveClaimId) 插入到CustomerDirtyQueue中,并安排在ProcessingExpiresAt执行。如果没有剩余任务,请重置IsProcessing = FALSE、ActiveClaimId = NULL和ProcessingExpiresAt = NULL。
- 检查
- 第 2 步(在事务之外处理任务):事务 1 提交后,工作器会在应用代码中按顺序运行任务。如果某个任务失败,工作器会提前停止批处理,但仍会运行第 3 步,以便在重试失败的任务之前删除已完成的任务。
- 第 3 步(删除已完成的任务并在事务 2 中释放实体):工作器打开第二个简短的读写事务,使用
ASSERT_ROWS_MODIFIED 1从CustomerDirtyQueue中删除其回退消息 (ActiveClaimId),从CustomerWorkItems中删除已完成的任务,如果CustomerWorkItems中仍有更多任务,则插入新的唤醒消息,并重置Customers上的IsProcessing = FALSE、ActiveClaimId = NULL和ProcessingExpiresAt = NULL。
- 第 1 步(在事务 1 中声明实体):当工作器收到来自
此模式可保留严格的每个实体排序和高吞吐量,原因如下:
- 提交时间戳排序:将任务存储在以
(CustomerId, CreatedAt, BatchIndex)为键的CustomerWorkItems中,可按事务提交时间戳对每个任务进行排序,即使多个发送者同时写入也是如此。 - 单分块局部性:在
Customers中交错CustomerWorkItems和CustomerDirtyQueue,可将实体的声明状态、任务和唤醒消息保留在同一分块中。将CustomerDirtyQueue.Payload留空 (b'') 还可以最大限度地减少队列存储和内存中传送开销。 - 短期数据库锁定:在第 2 步中保持读写事务处于打开状态会锁定
Customers行并阻止并发发送者。将声明(事务 1)和完成(事务 2)拆分为两个短事务可确保一次只有一个工作器处理一个实体,而无需在应用代码运行时持有数据库锁。 - 崩溃恢复和隔离:立即确认事务 1 中的初始唤醒消息,并在
ProcessingExpiresAt安排后备消息 (ActiveClaimId),从而将固定的 10 秒RECEIVE消息租约替换为可配置的实体声明。如果工作器在第 2 步期间崩溃,则后备消息会成熟并唤醒另一个工作器。如果慢速工作器在声明过期后完成,并且另一个工作器已经接管,则在事务 2 中使用ASSERT_ROWS_MODIFIED 1删除ActiveClaimId会失败,并显示OUT_OF_RANGE。让该错误传播(或显式回滚)会在这些写入操作提交之前中止事务 2。
以下语句在单个发送方事务中插入两个有序任务和一条唤醒消息:
GoogleSQL
-- 1. Insert ordered tasks for the entity using a 0-based BatchIndex
INSERT INTO CustomerWorkItems (CustomerId, CreatedAt, BatchIndex, WorkType, WorkPayload)
VALUES
(456, PENDING_COMMIT_TIMESTAMP(), 0, 'ORDER_CREATED', JSON '{"order_id": 1001}'),
(456, PENDING_COMMIT_TIMESTAMP(), 1, 'PAYMENT_CAPTURED', JSON '{"order_id": 1001}');
-- 2. Send a single lightweight wake-up message to the interleaved queue
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'msg-uuid-1', CURRENT_TIMESTAMP(), b'');
PostgreSQL
-- 1. Insert ordered tasks for the entity using a 0-based batchindex
INSERT INTO customerworkitems (customerid, createdat, batchindex, worktype, workpayload)
VALUES
(456, spanner.pending_commit_timestamp(), 0, 'ORDER_CREATED', '{"order_id": 1001}'::jsonb),
(456, spanner.pending_commit_timestamp(), 1, 'PAYMENT_CAPTURED', '{"order_id": 1001}'::jsonb);
-- 2. Send a single lightweight wake-up message to the interleaved queue
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'msg-uuid-1', CURRENT_TIMESTAMP, CAST('' AS bytea));
以下 SQL 语句展示了工作器如何接收唤醒消息、在事务 1 中声明实体以及在事务 2 中释放实体:
GoogleSQL
-- Stream wake-up messages from the queue:
SELECT CustomerId, MessageId, Payload, DeliverTime, SpannerLeaseToken
FROM RECEIVE_CustomerDirtyQueue(max_duration=>'20m', max_batch_size=>10);
-- Transaction 1 (claim the entity):
-- 1. Check whether another worker holds an active claim on the entity
SELECT
IsProcessing,
ActiveClaimId,
IFNULL(ProcessingExpiresAt > CURRENT_TIMESTAMP(), FALSE) AS HasActiveClaim
FROM Customers
WHERE CustomerId = 456;
-- Always acknowledge the incoming wake-up message in Transaction 1.
-- Let OUT_OF_RANGE propagate to roll back the transaction so duplicate workers exit.
-- If IsProcessing = TRUE and HasActiveClaim = TRUE, commit after this DELETE and exit.
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'msg-uuid-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Only if no active claim exists, read the next ordered batch of work items
SELECT CreatedAt, BatchIndex, WorkType, WorkPayload
FROM CustomerWorkItems
WHERE CustomerId = 456
ORDER BY CreatedAt ASC, BatchIndex ASC
LIMIT 25;
-- 3. Always clean up any remaining expired fallback message
-- (when ActiveClaimId IS NOT NULL AND ActiveClaimId != 'msg-uuid-1'):
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'expired-claim-uuid';
-- 4. If step 2 returns work items, schedule a 10-second fallback message
-- and mark the entity as busy. (If step 2 returns no rows, clear any
-- expired claim fields on Customers instead.)
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'claim-uuid-2', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND), b'');
UPDATE Customers
SET IsProcessing = TRUE,
ActiveClaimId = 'claim-uuid-2',
ProcessingExpiresAt = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND)
WHERE CustomerId = 456;
-- Transaction 2 (after processing the batch in order outside a transaction):
-- 1. Verify the claim is still active by deleting the fallback message.
-- Let OUT_OF_RANGE propagate to roll back the transaction if another worker took over.
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
-- 2. Delete the completed work items
DELETE FROM CustomerWorkItems
WHERE CustomerId = 456
AND CreatedAt = TIMESTAMP '2026-05-01T12:00:00.123456Z'
AND BatchIndex IN (0, 1);
-- 3. Check whether any unprocessed work items remain:
SELECT 1 FROM CustomerWorkItems
WHERE CustomerId = 456
LIMIT 1;
-- Run this INSERT only if the preceding SELECT returned a row:
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'continuation-uuid-3', CURRENT_TIMESTAMP(), b'');
-- 4. Release the entity claim
UPDATE Customers
SET IsProcessing = FALSE, ActiveClaimId = NULL, ProcessingExpiresAt = NULL
WHERE CustomerId = 456 AND ActiveClaimId = 'claim-uuid-2';
PostgreSQL
-- Stream wake-up messages from the queue:
SELECT customerid, messageid, payload, deliver_time, spanner_lease_token
FROM spanner.receive_customerdirtyqueue(
max_batch_size=>10, priority=>NULL, max_duration=>'20m');
-- Transaction 1 (claim the entity):
-- 1. Check whether another worker holds an active claim on the entity
SELECT
isprocessing,
activeclaimid,
COALESCE(processingexpiresat > CURRENT_TIMESTAMP, false) AS hasactiveclaim
FROM customers
WHERE customerid = 456;
-- Always acknowledge the incoming wake-up message in Transaction 1.
-- Let OUT_OF_RANGE propagate to roll back the transaction so duplicate workers exit.
-- If isprocessing = true and hasactiveclaim = true, commit after this DELETE and exit.
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'msg-uuid-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Only if no active claim exists, read the next ordered batch of work items
SELECT createdat, batchindex, worktype, workpayload
FROM customerworkitems
WHERE customerid = 456
ORDER BY createdat ASC, batchindex ASC
LIMIT 25;
-- 3. Always clean up any remaining expired fallback message
-- (when activeclaimid IS NOT NULL AND activeclaimid != 'msg-uuid-1'):
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'expired-claim-uuid';
-- 4. If step 2 returns work items, schedule a 10-second fallback message
-- and mark the entity as busy. (If step 2 returns no rows, clear any
-- expired claim fields on customers instead.)
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'claim-uuid-2', CURRENT_TIMESTAMP + INTERVAL '10 SECOND', CAST('' AS bytea));
UPDATE customers
SET isprocessing = true,
activeclaimid = 'claim-uuid-2',
processingexpiresat = CURRENT_TIMESTAMP + INTERVAL '10 SECOND'
WHERE customerid = 456;
-- Transaction 2 (after processing the batch in order outside a transaction):
-- 1. Verify the claim is still active by deleting the fallback message.
-- Let OUT_OF_RANGE propagate to roll back the transaction if another worker took over.
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
-- 2. Delete the completed work items
DELETE FROM customerworkitems
WHERE customerid = 456
AND createdat = '2026-05-01T12:00:00.123456Z'::timestamptz
AND batchindex IN (0, 1);
-- 3. Check whether any unprocessed work items remain:
SELECT 1 FROM customerworkitems
WHERE customerid = 456
LIMIT 1;
-- Run this INSERT only if the preceding SELECT returned a row:
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'continuation-uuid-3', CURRENT_TIMESTAMP, CAST('' AS bytea));
-- 4. Release the entity claim
UPDATE customers
SET isprocessing = false, activeclaimid = NULL, processingexpiresat = NULL
WHERE customerid = 456 AND activeclaimid = 'claim-uuid-2';
通过时间批处理对突发唤醒消息进行去重
当实体在几秒钟内收到频繁的突发更新时,在每笔事务上发送单独的唤醒消息会产生不必要的队列写入和多余的唤醒传送。
如需在写入时合并唤醒消息,请将时间批处理与确定性 MessageId 结合使用:
- 将
MessageId设置为时间范围的存储桶 ID(例如,5 秒存储桶)。 - 将
DeliverTime向上舍入到下一个时间范围,再加上使用FARM_FINGERPRINT(或spanner.farm_fingerprint)计算得出的每个实体的偏移量。 - 使用 GoogleSQL 中的
INSERT OR IGNORE或 PostgreSQL 中的INSERT ... SELECT ... FROM ... WHERE NOT EXISTS插入唤醒消息。
在该 5 秒时间范围内,同一实体的每笔交易都会计算相同的 MessageId 和 DeliverTime。所有事务都会将其任务插入到 CustomerWorkItems 中,但只有窗口中的第一个事务会将唤醒消息写入 CustomerDirtyQueue。添加每个实体的偏移量会将传送时间分散在 5 秒的时间间隔内,这样不同的实体就不会在完全相同的秒数触发。
例如,以下发送方事务会将唤醒消息合并到 5 秒的时间段内:
GoogleSQL
-- 1. Insert the ordered work item
INSERT INTO CustomerWorkItems (CustomerId, CreatedAt, BatchIndex, WorkType, WorkPayload)
VALUES (456, PENDING_COMMIT_TIMESTAMP(), 0, 'METRIC_RECORDED', JSON '{"v": 42}');
-- 2. Coalesce wake-up messages into a 5-second window with a per-customer offset
INSERT OR IGNORE INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (
456,
CONCAT('bucket-', CAST(DIV(UNIX_SECONDS(CURRENT_TIMESTAMP()), 5) * 5 AS STRING)),
TIMESTAMP_SECONDS(
DIV(UNIX_SECONDS(CURRENT_TIMESTAMP()), 5) * 5 + 5
+ MOD(ABS(FARM_FINGERPRINT(CAST(456 AS STRING))), 5)
),
b''
);
PostgreSQL
-- 1. Insert the ordered work item
INSERT INTO customerworkitems (customerid, createdat, batchindex, worktype, workpayload)
VALUES (456, spanner.pending_commit_timestamp(), 0, 'METRIC_RECORDED', '{"v": 42}'::jsonb);
-- 2. Coalesce wake-up messages into a 5-second window with a per-customer offset
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
SELECT
456,
'bucket-' || bucket.start_sec::text,
to_timestamp(
bucket.start_sec + 5
+ (abs(spanner.farm_fingerprint(456::text)) % 5)
),
CAST('' AS bytea)
FROM (
SELECT (floor(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP))::bigint / 5) * 5 AS start_sec
) AS bucket
WHERE NOT EXISTS (
SELECT 1 FROM customerdirtyqueue
WHERE customerid = 456
AND messageid = 'bucket-' || bucket.start_sec::text
);
减少并发唤醒消息造成的锁争用
当同一忙碌实体的多个唤醒消息同时到期时,多个工作器可以同时启动事务 1,并争用同一 Customers 行上的读写锁。
为了减少热点实体上的锁争用,请在启动事务 1 之前执行无锁单次读取 (client.Single()),以检查 IsProcessing = TRUE 和 ProcessingExpiresAt > CURRENT_TIMESTAMP()。如果实体处于忙碌状态,则在运行事务 1 之前休眠一小段随机延迟时间(例如 10 到 50 毫秒),以重新检查 Customers 并确认重复的唤醒消息。
单次读取会检查实体的声明状态,而无需获取读写锁,并且随机延迟会错开后续读写事务,因此工作器不会在同一毫秒内争用 Customers 行。在删除唤醒消息之前,请务必在事务 1 中重新检查 IsProcessing,以便在活跃工作器在单次读取后立即完成时,不会在仍有新任务未处理的情况下删除唤醒消息。
例如,以下 Go 函数会在实体已忙碌时应用随机延迟:
func maybeJitterBeforeClaim(ctx context.Context, client *spanner.Client, customerID int64) error {
iter := client.Single().Query(ctx, spanner.Statement{
SQL: `SELECT IsProcessing AND IFNULL(ProcessingExpiresAt > CURRENT_TIMESTAMP(), FALSE) AS IsBusy
FROM Customers WHERE CustomerId = @cid`,
Params: map[string]interface{}{"cid": customerID},
})
defer iter.Stop()
row, err := iter.Next()
if errors.Is(err, iterator.Done) {
return nil
}
if err != nil {
return err
}
var isBusy bool
if err := row.Columns(&isBusy); err != nil {
return err
}
if isBusy {
time.Sleep(time.Duration(10+rand.Intn(40)) * time.Millisecond)
}
return nil
}
处理长时间运行的批处理和外部调用
如果一批任务的处理时间超过初始 10 秒 ProcessingExpiresAt 窗口,或者调用在数据库事务中止时不会回滚的外部 API(例如 Payment Gateway 或网络钩子),则您需要防止重复的外部操作和过早的声明到期。
如需保护外部调用并为慢速批次扩展实体声明,请执行以下操作:
- 将确定性幂等键传递给外部 API:组合
CustomerId、CreatedAt和BatchIndex(例如456:2026-05-01T12:00:00.123456Z:0),并在第 2 步中将该键传递给下游服务。 - 通过定期检测信号延长实体声明:在事务 1 中设置更长的初始
ProcessingExpiresAt和回退DeliverTime(例如 30 或 60 秒),或在声明到期之前运行后台检测信号事务(例如,在 10 秒的声明窗口中每 3 到 4 秒运行一次)。每个心跳事务都会使用ASSERT_ROWS_MODIFIED 1删除当前后备消息 (ActiveClaimId),插入一条具有更新后的未来DeliverTime的新后备消息,并更新Customers上的ActiveClaimId和ProcessingExpiresAt。在启动事务 2 之前停止心跳。
由于第 2 步在数据库事务之外运行,并且队列传送至少一次,因此在外部 API 调用后发生工作器崩溃会导致下一个工作器重试该任务。通过传递确定性幂等键,外部服务可以忽略重复请求。如需了解详情,请参阅事务性发件箱。
定期替换后备消息并延长 ProcessingExpiresAt 可在工作器正常运行时保持实体声明处于活跃状态,同时仍允许在工作器崩溃时由另一个工作器接管。
例如,以下心跳事务会将活跃实体声明延长 10 秒:
GoogleSQL
-- Run every 3 to 4 seconds on a 10-second claim window before starting Transaction 2:
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'claim-uuid-3', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND), b'');
UPDATE Customers
SET ActiveClaimId = 'claim-uuid-3',
ProcessingExpiresAt = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND)
WHERE CustomerId = 456 AND ActiveClaimId = 'claim-uuid-2';
PostgreSQL
-- Run every 3 to 4 seconds on a 10-second claim window before starting Transaction 2:
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'claim-uuid-3', CURRENT_TIMESTAMP + INTERVAL '10 SECOND', CAST('' AS bytea));
UPDATE customers
SET activeclaimid = 'claim-uuid-3',
processingexpiresat = CURRENT_TIMESTAMP + INTERVAL '10 SECOND'
WHERE customerid = 456 AND activeclaimid = 'claim-uuid-2';
通过分片和过时数据读取来扩缩高容量实体
当单个实体收到的写入操作超过一个 Spanner 分块可以处理的数量时,将每个任务写入单个 Customers 分块会创建写入热点,即使您仍需要按提交顺序处理该实体的任务也是如此。
如需在保留全局排序的同时扩缩高容量实体的写入吞吐量,请将写入操作分布在 N 个分片中,并使用过时数据读取通过定期清理工作器异步合并这些分片:
- 对工作表和重复信息删除表进行分片:创建一个具有
PRIMARY KEY (ShardId, CustomerId)(ShardId从0到N - 1)的CustomerShards父表,并交错一个以(ShardId, CustomerId, CreatedAt, BatchIndex)为键的分片工作表。将ShardId设为主键列会将写入操作分散到N个分片中。为防止在重试的事务收到新的PENDING_COMMIT_TIMESTAMP()时出现重复的任务行,还应使用 TTL 政策交错以(ShardId, CustomerId, BatchId)为键的批量去重表。 - 将每个生产者批次路由到一个分片,而不进行队列写入:在每个生产者事务中,根据唯一
BatchId(例如FARM_FINGERPRINT)的哈希计算ShardId,并插入到去重表和分片工作表中,而不插入到队列中。对BatchId进行哈希处理可将每个提供方事务保持在单个分片中,并消除每次写入队列的开销。 - 使用过时数据读取和进度光标按顺序扫描分片:在单独的
CustomerSweeperState表中维护一个活跃声明和一个(LastSweptAt, LastBatchIndex, LastShardId)光标,该表具有一个交错唤醒队列,其中包含每个实体的一条周期性扫描器消息。触发后,工作器会认领实体(使用与使用有序工作表和唤醒队列相同的认领和回退消息模式),使用按CreatedAt ASC, BatchIndex ASC, ShardId ASC排序的 10 秒确切过时数据读取,在游标之后查询所有N分片,按顺序处理批次,并提交一个完成事务,该事务会删除已处理的行、推进游标并安排下一次扫描。使用过时数据读取可避免在跨分片进行中的提供方写入时发生阻塞,而游标会跳过在过时窗口内处理和删除的行。
后续步骤
- 了解如何使用 Spanner 队列,包括最佳实践和监控。
- 探索更多 Spanner 队列场景和示例。
- 了解“正好一次”处理和“最多一次”确认。
- 使用队列的精细访问权限控制配置访问权限控制。