xiry.

Development · Tip

Safe Manual SQL Updates Using Transactions

A workflow to safely update or delete database records using transactions.

From the Xiry library · Updated 3 October 2026

This workflow uses explicit SQL transaction commands to test manual updates and deletions before making them permanent. By wrapping data modifications between START TRANSACTION and COMMIT, you can inspect affected rows with a SELECT query and safely ROLLBACK if something goes wrong.

How it works

When making manual database modifications:

  1. Open a transaction before running any data modifications: `START TRANSACTION;`
  2. Run your query (e.g., `UPDATE users SET role = 'admin' WHERE id = 1;`).
  3. Verify the updated rows with a `SELECT` query.
  4. If you made a mistake or affected unexpected rows, revert everything completely: `ROLLBACK;`
  5. Once verified and satisfied with the result, permanently save the changes: `COMMIT;`

What it's for

  • Prevent accidental mass updates or deletions caused by a missing WHERE clause
  • Inspect modified database records before permanently committing them to disk
  • Safely execute one-off manual data fixes on production or staging environments

Key points

  • Begin an explicit transaction session with START TRANSACTION
  • Execute modifying queries such as UPDATE or DELETE
  • Inspect changed rows using SELECT queries within the uncommitted transaction
  • Discard all uncommitted modifications using ROLLBACK if errors occur
  • Persist changes permanently to the database using COMMIT once verified

Pricing

Free

Details

Type
Tip
Category
Development
Topic
Database Management
Platforms
Command line
For
Developers, database administrators, and data engineers
Tags
sql, database, transactions, mysql, devops, tips

Why it was saved

  • @meech_ward, 23 December 2023: Prevents catastrophic database errors (such as running an UPDATE or DELETE without a WHERE clause) by allowing you to inspect changes and revert them before committing.

Related

  • Supabase Stripe Wrapper

    PostgreSQL foreign data wrapper to query and write Stripe data via SQL

  • Add Apple Docs to Cursor Composer

    Index official Apple documentation as reference docs inside Cursor's AI composer

  • Antimetal

    Autonomous production engineering platform that diagnoses, triages, and fixes runtime issues

  • Cloudflare $5/mo Plan Inclusions

    Resource breakdown of Cloudflare's $5/month Workers Paid plan limits and allowances

  • Coolify

    Open-source, self-hosted platform alternative to Heroku, Vercel, and Railway

  • CrowdSec

    Open-source collaborative security engine and intrusion prevention system using crowdsourced threat data.

Updated