Intermediate · 35 minutes · Guided lesson

Transactions and data integrity

Explain atomic changes, constraints and rollback boundaries.

By the end of this lesson

  • Explain transactions and data integrity and when it is useful
  • Perform a safe inspection or structured practice task
  • Verify the result and describe remaining uncertainty

Your environment: Use an unprivileged account and a disposable Linux lab. Commands and available utilities vary by distribution and version. Where a command is unavailable or would require production access, complete the scenario as an offline written exercise. Setup help

Understand the concept

A transaction groups operations so the database can apply them according to its transaction model. ACID is a common framework describing atomicity, consistency, isolation and durability. Constraints such as unique keys and foreign keys protect data invariants. Transaction behaviour and isolation levels vary by engine and storage engine.

Keep transactions short and understand what locks they may hold. Validate preconditions before updates and use a WHERE clause that identifies the intended rows. In a lab, inspect the affected row count and commit only after verification; rollback is not a substitute for a tested backup.

Apply a careful workflow

Constraints and transaction guarantees depend on engine and schema design. Confirm scope before consequential changes, keep a short record of expected and observed results, and consult local documentation when behaviour differs. Protect credentials and personal data in notes and logs.

Check your understanding

The goal of this lesson is not to memorise a single command. Explain why the proposed check is relevant, what its output can establish and what remains unknown. A successful command status is only one piece of evidence.

See it in context

Worked example

A bounded example to inspect in your own lab; adapt paths and options to your installed tools.

START TRANSACTION;
SELECT id, status FROM sample_orders WHERE id = 1001;
-- Practice only in a disposable database; review before any UPDATE
ROLLBACK;

Read each command and identify its target, inputs, expected evidence and possible failure modes. Do not run administrative or modifying commands on a production system.

Your turn

Practical lab

Describe a safe process for correcting one fictional row, including pre-check, transaction boundary, row-count verification and post-check.

Run commands in your own lab. This website displays instructions; the practice terminal below helps you rehearse the syntax.

1. Prepare your workspace

mkdir -p "$HOME/training4linux/extended-labs" && cd "$HOME/training4linux/extended-labs"
printf "%s\n" "Practice workspace for Transactions and data integrity" > lab-notes.txt

2. Complete the task

  1. Complete the scenario using the supplied synthetic information or a disposable local lab. Record what you observed and what you inferred.
  2. Compare your work against the success criteria and explain any differences.
  3. Write one verification step, one stop condition and a safe cleanup or recovery step.

3. Check your result

  • The plan scopes the row, checks the result and includes a rollback path before commit.
  • The work remains within the disposable workspace or an offline case study.
  • The report identifies at least one limitation or remaining uncertainty.
Need a hint?

Start with a read-only check, define the scope and choose the smallest next step that distinguishes competing explanations.

Reveal a worked solution

Compare this with your approach after trying the task. More than one valid solution may exist.

Use the success criteria as a review rubric. Record the reasoning and evidence behind your result rather than treating one exact command sequence as the only valid answer.

After the lab: Retain your notes if useful. Remove only files created in the disposable practice directory; no production system should be changed.

Common mistakes to avoid

  • A transaction cannot reverse an external side effect such as a sent email.
  • Do not treat a successful command exit as proof that the overall service or business outcome is correct.

Check your understanding

Knowledge check

Answer all 3 questions correctly to pass. Retry as often as you need; your best score is kept.

1. Which approach best fits transactions and data integrity?
2. What should you do before a consequential change?
3. What does a successful command exit status establish?

Your lesson progress

Not started · Best score: 0% · Attempts: 0

Practical completion is self-reported. Progress is linked to a private cookie in this browser and stored on the site. Clearing that cookie loses access to it. Manage or erase progress.

Keep the reference close

Further reading