Commit or roll back writes

← MySQL examples

Connection requirementsCopy Anchor Link

This recipe needs an existing listener using shared credentials. A per-user MySQL session is read-only. Database RBAC grants or row-level policies require that per-user model, so the writes below do not apply there.

Check credential requirements. Use the HTTP API for authorized writes when this listener is read-only. Current PostgreSQL database logins are also read-only.

Commit related writes together or discard them on an existing writable MySQL listener.

Before you run it: This does not provide writes through per-user credentials or PostgreSQL database logins. Use one statement per call.

First complete the client and orders setup, keeping its sequelize connection and Order model in scope. Run the commit and rollback snippets as separate blocks: each declares a variable named tx. The IDs o_2 and o_3 should be unused in the test table.

Follow the exampleCopy Anchor Link

1. Confirm shared-credential write access.
2. Pass the transaction object to every ORM call.
3. Run the commit example.
4. Run rollback and check that its row is absent.

Commit the writesCopy Anchor Link

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");

Roll back the writesCopy Anchor Link

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

Run the equivalent SQLCopy Anchor Link

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;

Understand the behaviorCopy Anchor Link

• Sessions start in autocommit: each statement commits separately.
• START TRANSACTION groups subsequent statements until COMMIT or ROLLBACK.
• Disabling autocommit opens transactions implicitly. Enabling it again commits the open transaction.

Important limits and checksCopy Anchor Link

• 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.

Expected resultCopy Anchor Link

Committed rows remain; rolled-back writes do not appear.

ContinueCopy Anchor Link

Continue to insert or update a primary key match.