commit 16b9ff784bda6409ded13a8cfb3d0ce1b0c759b0
parent 7edc32c25b781cab5937bfa7fe97ca6665c0eb49
Author: Antoine A <>
Date: Tue, 13 Oct 2026 11:17:13 +0200
common: lock for commit ordered ids
Diffstat:
4 files changed, 85 insertions(+), 72 deletions(-)
diff --git a/adapters/taler-cyclos/db/cyclos-procedures.sql b/adapters/taler-cyclos/db/cyclos-procedures.sql
@@ -99,6 +99,8 @@ END IF;
-- Insert new incoming transaction
out_valued_at = in_valued_at;
+-- Lock for revenue id commit ordering
+LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO tx_in (
transfer_id,
tx_id,
@@ -132,6 +134,8 @@ ELSIF in_type IS NOT NULL THEN
WHERE (
tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve'
) OR authorization_pub = local_authorization_pub;
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
-- Insert new incoming talerable transaction
INSERT INTO taler_in (
tx_in_id,
@@ -180,6 +184,8 @@ IF FOUND THEN
RETURN;
END IF;
+-- Lock for revenue and incoming history id commit ordering
+LOCK TABLE tx_out IN SHARE ROW EXCLUSIVE MODE;
-- Insert new outgoing transaction
INSERT INTO tx_out (
transfer_id,
@@ -320,6 +326,7 @@ IF out_wtid_reuse THEN
END IF;
-- Insert an initiated outgoing transaction
out_initiated_at = in_now;
+LOCK TABLE transfer IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO initiated (
amount,
subject,
@@ -456,24 +463,22 @@ IF out_reserve_pub_reuse THEN
END IF;
IF in_recurrent THEN
- -- Finalize one pending right now
- WITH moved_tx AS (
- DELETE FROM pending_recurrent_in
- WHERE tx_in_id = (
- SELECT tx_in_id
- FROM pending_recurrent_in
- JOIN tx_in USING (tx_in_id)
- WHERE authorization_pub = in_authorization_pub
- ORDER BY registered_at ASC
- LIMIT 1
- )
- RETURNING tx_in_id
+ -- Finalize one pending right now. Lock history only if there is a row to publish.
+ DELETE FROM pending_recurrent_in
+ WHERE tx_in_id = (
+ SELECT tx_in_id
+ FROM pending_recurrent_in
+ JOIN tx_in USING (tx_in_id)
+ WHERE authorization_pub = in_authorization_pub
+ ORDER BY registered_at ASC
+ LIMIT 1
)
- INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
- SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig
- FROM moved_tx
- RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id;
+ RETURNING tx_in_id INTO talerable_tx;
IF talerable_tx IS NOT NULL THEN
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
+ INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
+ VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig)
+ RETURNING taler_in_id INTO local_taler_in_id;
PERFORM pg_notify('taler_in', local_taler_in_id::text);
END IF;
ELSE
diff --git a/adapters/taler-magnet-bank/db/magnet-bank-procedures.sql b/adapters/taler-magnet-bank/db/magnet-bank-procedures.sql
@@ -97,6 +97,8 @@ END IF;
-- Insert new incoming transaction
out_valued_at = in_valued_at;
+-- Lock for revenue id commit ordering
+LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO tx_in (
magnet_code,
amount,
@@ -128,6 +130,8 @@ ELSIF in_type IS NOT NULL THEN
WHERE (
tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve'
) OR authorization_pub = local_authorization_pub;
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
-- Insert new incoming talerable transaction
INSERT INTO taler_in (
tx_in_id,
@@ -175,6 +179,8 @@ IF FOUND THEN
RETURN;
END IF;
+-- Lock for revenue and incoming history id commit ordering
+LOCK TABLE tx_out IN SHARE ROW EXCLUSIVE MODE;
-- Insert new outgoing transaction
INSERT INTO tx_out (
magnet_code,
@@ -508,24 +514,23 @@ IF out_reserve_pub_reuse THEN
END IF;
IF in_recurrent THEN
- -- Finalize one pending right now
- WITH moved_tx AS (
- DELETE FROM pending_recurrent_in
- WHERE tx_in_id = (
- SELECT tx_in_id
- FROM pending_recurrent_in
- JOIN tx_in USING (tx_in_id)
- WHERE authorization_pub = in_authorization_pub
- ORDER BY registered_at ASC
- LIMIT 1
- )
- RETURNING tx_in_id
+ -- Finalize one pending right now. Lock history only if there is a row to publish.
+ DELETE FROM pending_recurrent_in
+ WHERE tx_in_id = (
+ SELECT tx_in_id
+ FROM pending_recurrent_in
+ JOIN tx_in USING (tx_in_id)
+ WHERE authorization_pub = in_authorization_pub
+ ORDER BY registered_at ASC
+ LIMIT 1
)
- INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
- SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig
- FROM moved_tx
- RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id;
+ RETURNING tx_in_id INTO talerable_tx;
IF talerable_tx IS NOT NULL THEN
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
+ INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
+ VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig)
+ RETURNING taler_in_id INTO local_taler_in_id;
PERFORM pg_notify('taler_in', local_taler_in_id::text);
END IF;
ELSE
diff --git a/adapters/taler-wise/db/wise-procedures.sql b/adapters/taler-wise/db/wise-procedures.sql
@@ -100,6 +100,8 @@ END IF;
-- Insert new incoming transaction
out_valued_at = in_valued_at;
+-- Lock for revenue id commit ordering
+LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO tx_in (
balance_id,
wise_ref,
@@ -133,6 +135,8 @@ ELSIF in_type IS NOT NULL AND in_debit_payto IS NOT NULL THEN
WHERE (
tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve'
) OR authorization_pub = local_authorization_pub;
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
-- Insert new incoming talerable transaction
INSERT INTO taler_in (
tx_in_id,
@@ -198,31 +202,26 @@ IF out_reserve_pub_reuse THEN
END IF;
IF in_recurrent THEN
- -- Finalize one pending right now
- WITH pending AS (
- SELECT tx_in_id
- FROM pending_recurrent_in
- JOIN tx_in USING (tx_in_id)
- WHERE authorization_pub = in_authorization_pub
- ORDER BY valued_at ASC
- LIMIT 1
- ), moved_tx AS (
- DELETE FROM pending_recurrent_in
- USING tx_in, pending
- WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id
- AND pending_recurrent_in.tx_in_id = pending.tx_in_id
- RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id
- ), inserted AS (
- INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
- SELECT tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig
- FROM moved_tx
- RETURNING tx_in_id, taler_in_id
- )
- SELECT inserted.tx_in_id, inserted.taler_in_id, moved_tx.balance_id
- INTO talerable_tx, local_taler_in_id, local_balance_id
- FROM inserted
- JOIN moved_tx USING (tx_in_id);
+ -- Finalize one pending right now. Lock history only if there is a row to publish.
+ DELETE FROM pending_recurrent_in
+ USING tx_in
+ WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id
+ AND pending_recurrent_in.tx_in_id = (
+ SELECT pending.tx_in_id
+ FROM pending_recurrent_in AS pending
+ JOIN tx_in USING (tx_in_id)
+ WHERE authorization_pub = in_authorization_pub
+ ORDER BY valued_at ASC
+ LIMIT 1
+ )
+ RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id
+ INTO talerable_tx, local_balance_id;
IF talerable_tx IS NOT NULL THEN
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
+ INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
+ VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig)
+ RETURNING taler_in_id INTO local_taler_in_id;
PERFORM pg_notify('taler_in', local_balance_id || ' ' || local_taler_in_id);
END IF;
END IF;
diff --git a/common/taler-api/db/taler-api-procedures.sql b/common/taler-api/db/taler-api-procedures.sql
@@ -90,6 +90,8 @@ INSERT INTO tx_out (
in_credit_payto,
in_now
) RETURNING tx_out_id INTO local_tx_id;
+-- Lock for transfer id commit ordering
+LOCK TABLE transfer IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO transfer (
tx_out_id,
exchange_base_url,
@@ -158,6 +160,8 @@ END IF;
-- Register incoming transaction
out_created_at=in_now;
+-- Lock for revenue id commit ordering
+LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO tx_in (
amount,
debit_payto,
@@ -179,6 +183,7 @@ ELSE
WHERE (
tx_in_id IS NULL AND account_pub = in_account_pub AND in_type=type AND type='reserve'
) OR authorization_pub = local_authorization_pub;
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO taler_in (
tx_in_id,
type,
@@ -242,24 +247,23 @@ IF out_reserve_pub_reuse THEN
END IF;
IF in_recurrent THEN
- -- Finalize one pending right now
- WITH moved_tx AS (
- DELETE FROM pending_recurrent_in
- WHERE tx_in_id = (
- SELECT tx_in_id
- FROM pending_recurrent_in
- JOIN tx_in USING (tx_in_id)
- WHERE authorization_pub = in_authorization_pub
- ORDER BY created_at ASC
- LIMIT 1
- )
- RETURNING tx_in_id
+ -- Finalize one pending right now.
+ DELETE FROM pending_recurrent_in
+ WHERE tx_in_id = (
+ SELECT tx_in_id
+ FROM pending_recurrent_in
+ JOIN tx_in USING (tx_in_id)
+ WHERE authorization_pub = in_authorization_pub
+ ORDER BY created_at ASC
+ LIMIT 1
)
- INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig)
- SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig
- FROM moved_tx
- RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id;
+ RETURNING tx_in_id INTO talerable_tx;
IF talerable_tx IS NOT NULL THEN
+ -- Lock for incoming history id commit ordering
+ LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
+ INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig)
+ VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig)
+ RETURNING taler_in_id INTO local_taler_in_id;
PERFORM pg_notify('incoming_tx', local_taler_in_id::text);
END IF;
ELSE
