examples · mysql · 2 / 4

2. Transactions that commit and roll back

shared-credential listeners only

The writes on this page run only against a listener on the shared credential model. A per-user MySQL session is read-only and refuses every write, so if your tenant has any database RBAC grant or any row-level security policy - the condition that forces per-user login - none of this will run against it. Check which model you are on under credential models before you build on this, and write through the HTTP API or the PostgreSQL wire if you are not.

what this does

Runs both halves of a transaction contract over the wire: a block that commits and one that rolls back. Both were exercised against the shipping engine. The second is the one worth testing yourself, because a transaction layer that only pretends to work still looks correct until something has to be undone.

committing

const tx = await sequelize.transaction();
try {
  await Order.create(
    { id: "o_2", customer: "c_7", total_cents: 900, status: "open" },
    { transaction: tx },
  );
  await Order.update({ status: "paid" }, { where: { id: "o_2" }, transaction: tx });
  await tx.commit();
} catch (err) {
  await tx.rollback();
  throw err;
}

// After the commit the row is there, with status "paid".
const saved = await Order.findByPk("o_2");

rolling back

const tx = await sequelize.transaction();
await Order.create(
  { id: "o_3", customer: "c_9", total_cents: 100, status: "open" },
  { transaction: tx },
);
await tx.rollback();

// The important half of the test: the row is NOT there afterwards.
const gone = await Order.findByPk("o_3"); // null

the same thing as plain SQL

START TRANSACTION;
INSERT INTO orders (id, customer, total_cents, status) VALUES ('o_2', 'c_7', 900, 'open');
UPDATE orders SET status = 'paid' WHERE id = 'o_2';
COMMIT;

START TRANSACTION;
INSERT INTO orders (id, customer, total_cents, status) VALUES ('o_3', 'c_9', 100, 'open');
ROLLBACK;

how it works

A session starts in autocommit, which the handshake reports honestly: with nothing else said, every statement commits on its own.
START TRANSACTION opens a real engine transaction, and COMMIT / ROLLBACK end it for real.
Turning autocommit off is also a real session mode rather than an acknowledged no-op: the engine opens a transaction implicitly around your statements, and switching autocommit back on commits the open block, which is what a MySQL client expects.

common mistakes

Forgetting to pass the transaction. A statement issued without { transaction: tx } runs outside the block and will not be rolled back with it.
Batching statements to save a round trip. Multi-statement queries are refused. Send one statement at a time.
Assuming rollback was tested for you. Assert the row is gone, as above. That assertion is the whole value of the test.

MySQL is a registered trademark of Oracle Corporation and/or its affiliates. OriginChainDB is not affiliated with, endorsed by, or sponsored by Oracle Corporation. OriginChainDB implements a compatible wire protocol so that existing MySQL clients can connect to it; it does not distribute MySQL software.