Transaction Scoping, Retryable Errors, Idempotency, and Network Failure Handling

Learning outcomes

ServiceHub receives a payment request that inserts an invoice and writes an outbox row. Under contention, a deadlock occurs; under a different fault, the TCP connection disappears immediately after COMMIT was sent. A generic “retry three times” loop is dangerous because the two failures have different semantics. This lesson builds a transaction contract around unit of work, retry classification, idempotency, and ambiguous commit.

1. The transaction boundary belongs to the business operation

Autocommit is convenient for independent single statements, but a business action often spans several writes that must succeed or fail together. A MariaDB transaction gives atomicity for transactional engines such as InnoDB. It does not automatically make external HTTP calls, queues, files, or email atomic with the database. Keep the database transaction short and avoid waiting on slow network services while holding locks.

2. Build idempotent transaction tables

DROP DATABASE IF EXISTS servicehub19_l3;CREATE DATABASE servicehub19_l3 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub19_l3;CREATE TABLE requests (  request_key VARCHAR(80) PRIMARY KEY,  status ENUM('started','committed') NOT NULL,  result_invoice_id BIGINT NULL,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;CREATE TABLE invoices (  invoice_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  amount DECIMAL(18,4) NOT NULL,  request_key VARCHAR(80) NOT NULL UNIQUE,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;CREATE TABLE outbox (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  request_key VARCHAR(80) NOT NULL UNIQUE,  event_type VARCHAR(64) NOT NULL,  payload LONGTEXT NOT NULL,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;DROP USER IF EXISTS 'svc19_tx'@'127.0.0.1';CREATE USER 'svc19_tx'@'127.0.0.1' IDENTIFIED BY 'local-tx-lab';GRANT SELECT, INSERT, UPDATE ON servicehub19_l3.* TO 'svc19_tx'@'127.0.0.1';CREATE TABLE deadlock_items (  item_id INT PRIMARY KEY,  value_int INT NOT NULL) ENGINE=InnoDB;INSERT INTO deadlock_items VALUES (1,10),(2,20);

The unique request key is not merely a cache hint. It is durable database evidence that lets a retry ask, “Did this logical operation already create an invoice?” rather than blindly creating another one.

3. Implement the whole unit with commit/rollback discipline

async function createInvoiceOnce(conn, requestKey, customerId, amount) {  await conn.beginTransaction();  try {    await conn.query(      `INSERT INTO requests(request_key,status)       VALUES (?, 'started')`, [requestKey]    );    const r = await conn.query(      `INSERT INTO invoices(customer_id,amount,request_key)       VALUES (?,?,?)`, [customerId, amount, requestKey]    );    const invoiceId = Number(r.insertId);    await conn.query(      `INSERT INTO outbox(request_key,event_type,payload)       VALUES (?, 'invoice.created', ?)`,      [requestKey, JSON.stringify({ invoiceId })]    );    await conn.query(      `UPDATE requests SET status='committed', result_invoice_id=?       WHERE request_key=?`, [invoiceId, requestKey]    );    await conn.commit();    return { invoiceId, replay: false };  } catch (err) {    try { await conn.rollback(); } catch (_) {}    throw err;  }}

After an error, the safest default is to roll back and discard any assumptions about partial progress. A lock-wait timeout can have statement-vs-transaction nuances depending on configuration; application code should not continue composing business work on a transaction whose outcome it has not explicitly reasoned about.

4. Classify failures before retrying

Error numbers are useful diagnostics, but production code should use connector-documented error properties and test against the deployed connector/server pair. A broad “any SQL error is retryable” rule creates duplicate work and hides real defects.

5. Deadlock lab: two sessions, one cycle

START TRANSACTION;UPDATE deadlock_items SET value_int=value_int+1 WHERE item_id=1;-- Wait until Session B has locked item_id=2, then request it:UPDATE deadlock_items SET value_int=value_int+1 WHERE item_id=2;

START TRANSACTION;UPDATE deadlock_items SET value_int=value_int+1 WHERE item_id=2;-- Wait until Session A has locked item_id=1, then request it:UPDATE deadlock_items SET value_int=value_int+1 WHERE item_id=1;

One session should become a deadlock victim so InnoDB can break the cycle. Immediately inspect SHOW ENGINE INNODB STATUS for the latest detected deadlock. The error proves a cycle was resolved; it does not prove which query should be optimized without reading the lock/order evidence.

6. Bounded retry plus idempotency reconciliation

const mariadb = require('mariadb');const pool = mariadb.createPool({  host: '127.0.0.1', user: 'svc19_tx', password: process.env.DB_PASSWORD,  database: 'servicehub19_l3', connectionLimit: 4});const sleep = ms => new Promise(r => setTimeout(r, ms));async function createInvoice(pool, input) {  for (let attempt = 1; attempt <= 3; attempt++) {    let conn;    try {      conn = await pool.getConnection();      const existing = await conn.query(        `SELECT result_invoice_id FROM requests         WHERE request_key=? AND status='committed'`,        [input.requestKey]      );      if (existing.length) {        return { invoiceId: Number(existing[0].result_invoice_id), replay: true };      }      return await createInvoiceOnce(        conn, input.requestKey, input.customerId, input.amount      );    } catch (err) {      const retryable = err && (err.errno === 1213 || err.errno === 1205);      if (!retryable || attempt === 3) throw err;      await sleep(50 * attempt + Math.floor(Math.random() * 50));    } finally {      if (conn) conn.release();    }  }}

Backoff reduces immediate collision, but retry count and timing are workload decisions, not universal constants. More importantly, a retry recreates the whole transaction and rechecks durable idempotency evidence.

7. Ambiguous commit: the server may know while the client does not

Suppose the client sends COMMIT, MariaDB durably commits, and then the network breaks before the acknowledgment reaches the application. From the client’s perspective, “commit returned an error” does not prove rollback. Reissuing the business operation without an idempotency key can duplicate it. The repair is reconciliation: reconnect, query by the durable request key, and decide whether the logical operation already exists.

8. Reproducible verification and cleanup

SELECT request_key, COUNT(*) AS invoicesFROM servicehub19_l3.invoicesGROUP BY request_keyHAVING COUNT(*) <> 1;SELECT r.request_key, r.status, r.result_invoice_id,       i.invoice_id, o.event_idFROM servicehub19_l3.requests rLEFT JOIN servicehub19_l3.invoices i USING (request_key)LEFT JOIN servicehub19_l3.outbox o USING (request_key)ORDER BY r.created_at;DROP USER IF EXISTS 'svc19_tx'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub19_l3;

Production judgment and bridge to Lesson 4

Retries belong to known failure classes and idempotent transaction units, not generic middleware around every database call. Instrument attempt count, error code/SQLSTATE, transaction duration, deadlocks, lock waits and reconciliation outcomes. Lesson 4 moves upward to ORMs: transaction correctness can still be undermined when an abstraction quietly emits dozens of queries, OFFSET scans, or unindexed predicates.

Retry state machine: classify before repeating work

A retry loop should start from a state machine, not from a list of error strings. Before the transaction begins, reconnecting is generally straightforward. After the transaction has begun but before commit, a confirmed deadlock victim or lock-wait failure can be retryable if the complete unit of work is safe to run again. After the application sends COMMIT and loses the network before receiving the response, the outcome can be ambiguous: the server may have committed even though the client saw an exception. Blindly replaying the business operation can then create a duplicate side effect.

Idempotency moves that ambiguity into data design. Give externally retried commands a stable request key and enforce it with a unique constraint in the same transactional database that records the business effect. On a retry, read the existing result associated with that key instead of creating a second order, payment instruction, ticket, or message. The unique constraint is important because two concurrent workers can both observe “not found” before either inserts; application-only checking is not a concurrency guarantee.

• Retry the whole transaction, not only the final failed statement, after a deadlock has rolled work back.
• Use bounded attempts, jitter/backoff where appropriate, and an overall request deadline.
• Do not retry deterministic constraint or syntax errors as if they were transient.
• When commit outcome is ambiguous, reconcile through the idempotency/business key before issuing another side effect.
• Record retry count and terminal error class so a retry storm is observable.

Network failure handling should also destroy or revalidate a connection whose protocol state is uncertain. Returning such a connection to the pool can spread one request's failure into later requests. Connector fatal/error metadata and a deliberate pool policy are part of the transaction contract, not merely plumbing.

Authoritative references

Primary references are current MariaDB documentation or official connector source; verify the target server/connector version before relying on defaults or option behavior.

• MariaDB transactions
• MariaDB Connector/Node.js Promise API
• MariaDB InnoDB locking documentation
• MariaDB error codes