Skip to main content

Transactions

The transactions the crate opens for a delivery: the row lock form's claim, and in transactional mode the one a handler writes through, at the table's level.

In transactional mode, the transaction a handler writes through in each form, and what commits outside itRow lockLeaseAdvisory lockClaim's txDelivery's txDelivery's txBEGIN, claimFOR UPDATESKIP LOCKEDSAVEPOINTright afterthe claimHandlerwritesthrough Txack: COMMITwith thesettlement;else ROLLBACKTO SAVEPOINTClaim stampsthe lease,COMMITBEGIN at thetable'slevelHandlerwritesthrough Txack: settleif the leaseholds, COMMIT;lost: allrolled backLock the key,take the rowBEGIN onthe lock'sconnectionHandlerwritesthrough Txack: settle,COMMIT;else rollback, thensettleUnlockthe key
The diagram as text
  1. Claim's tx: BEGIN, claim FOR UPDATE SKIP LOCKED; SAVEPOINT right after the claim; Handler writes through Tx; ack: COMMIT with the settlement; else ROLLBACK TO SAVEPOINT
  2. Delivery's tx: BEGIN at the table's level; Handler writes through Tx; ack: settle if the lease holds, COMMIT; lost: all rolled back
  3. Delivery's tx: BEGIN on the lock's connection; Handler writes through Tx; ack: settle, COMMIT; else roll back, then settle
  4. Row lock: BEGIN, claim FOR UPDATE SKIP LOCKED; SAVEPOINT right after the claim; Handler writes through Tx; ack: COMMIT with the settlement; else ROLLBACK TO SAVEPOINT
  5. Lease: Claim stamps the lease, COMMIT; BEGIN at the table's level; Handler writes through Tx; ack: settle if the lease holds, COMMIT; lost: all rolled back
  6. Advisory lock: Lock the key, take the row; BEGIN on the lock's connection; Handler writes through Tx; ack: settle, COMMIT; else roll back, then settle; Unlock the key
  7. BEGIN, claim FOR UPDATE SKIP LOCKED → SAVEPOINT right after the claim
  8. SAVEPOINT right after the claim → Handler writes through Tx
  9. Handler writes through Tx → ack: COMMIT with the settlement; else ROLLBACK TO SAVEPOINT
  10. Claim stamps the lease, COMMIT → BEGIN at the table's level
  11. BEGIN at the table's level → Handler writes through Tx
  12. Handler writes through Tx → ack: settle if the lease holds, COMMIT; lost: all rolled back
  13. Lock the key, take the row → BEGIN on the lock's connection
  14. BEGIN on the lock's connection → Handler writes through Tx
  15. Handler writes through Tx → ack: settle, COMMIT; else roll back, then settle
  16. ack: settle, COMMIT; else roll back, then settle → Unlock the key

A subscription mounted with .transactional() lends its handler the delivery's transaction as Tx. Acknowledgement commits the handler's writes together with the row's settlement, so a handler's results and the end of its job are one commit. Every other outcome rolls the handler's writes back first, then settles the row as it would outside transactional mode. A row the handler inserts into a queue table through Tx commits with the acknowledgement too. A write through the pool, and a publish through the broker, commit on their own.

The row lock form already holds a transaction, the claim's, so its handler writes inside it. The savepoint the subscription sets after the claim, SAVEPOINT ruststream_claim, is what lets a retry discard the handler's writes with ROLLBACK TO SAVEPOINT ruststream_claim and still count the attempt in the same commit. The lease and advisory lock forms commit their claims at once, so they open a transaction for the delivery after it. In the lease form the acknowledgement takes effect only while the row still holds the delivery's lease: a lost lease rolls the whole transaction back, the handler's writes with it, and the settlement fails with LeaseLost.

Each delivery in transactional mode holds one connection for its transaction, in every form. A batch handler cannot be mounted with .transactional(): the step serves single deliveries.

Isolation and modes​

DatabaseWithout a declarationLevels a table may declare
PostgreSQLBEGIN, the server's defaultread_committed, repeatable_read, serializable
MySQL, MariaDBREAD COMMITTEDall four
SQLiteBEGIN, deferredmodes: deferred, immediate, exclusive

A table declares a level with isolation = .., or a SQLite mode with mode = .., and the crate opens the row lock form's claim and every transaction of transactional mode at it. PostgreSQL runs READ UNCOMMITTED as READ COMMITTED, so its dialect does not open it, and a table that declares it does not compile against a PostgreSQL broker. SQLite runs every transaction serializable: its mode decides when a transaction takes the database's write lock, at its first write or at its start. On SQLite a delivery's transaction holds that lock until the delivery settles, and keeps every other writer out meanwhile, claims included.

MySQL and MariaDB open at READ COMMITTED because their default, REPEATABLE READ, locks the gaps between the rows a locking read scans: a claim held for a handler would block every insert into the table until it ends. A row lock table on PostgreSQL at repeatable_read or serializable fails claims with serialization errors when claims and settlements of its rows run at once, so read_committed is the practical level for that form there. A lease table in transactional mode at either level stops at startup on PostgreSQL: such a transaction reads the row as its first statement found it, and would not see the lease its keeper extended since.

The crate drives these transactions itself rather than through sqlx's transaction type: a transaction dropped while open closes its connection, and the server rolls it back. The levels are the databases' own: PostgreSQL, MySQL, SQLite.