Databases and SQL I · Lesson 09 / 12

Transactions and decisions

Explain BEGIN, COMMIT and ROLLBACK and test failure handling.

Let's discover together

1
A transaction groups changes. BEGIN starts, COMMIT commits and ROLLBACK undoes the open transaction. A return and another record can form one unit of work. Query before and after to check intent.
2
An SQLite constraint failure may undo only the failed statement and leave the transaction open. Do not assume every error rolls back earlier statements. Our runner catches failures and rolls back if needed; the test verifies this.
3
The example shows a provisional change and restored state. Compare with COMMIT. The in-memory database still disappears on exit: committing and persisting to a file are distinct decisions.
4
Multiple users also require concurrency, locking and business rules. This kit does not implement a concurrent lending service. Explain its limits before adapting it to real use.

Your data workbench

Our model: kits.id ← loans.kit_id. One material can have many loans; names may repeat. We use fictional data only.

Predict before opening the reference. It shows verified output from the original example, not execution of your text. Run your changes with the kit on your computer.

-- Transactions and decisions
BEGIN;
UPDATE loans SET returned='2026-01-03' WHERE id=3;
SELECT id, returned FROM loans WHERE id=3;
ROLLBACK;
SELECT id, returned FROM loans WHERE id=3;

Compare with reference output

[
  {
    "columns": [
      "id",
      "returned"
    ],
    "rows": [
      [
        3,
        "2026-01-03"
      ]
    ]
  },
  {
    "columns": [
      "id",
      "returned"
    ],
    "rows": [
      [
        3,
        null
      ]
    ]
  }
]

Run the command in the extracted folder’s terminal. Use python3 on macOS/Ubuntu or py if that is your Windows launcher. Every run starts in memory; read README.md for saving files and recovery practice.

python lab.py run lesson.sql

My files and GitHub

The editor saves your .sql draft in this browser. Download it and replace lesson.sql in the kit; for the final project, availability.sql. The ZIP remains the original template. GitHub publishing sends only the confirmed file, without executing SQL or sending a database.

Official links and next step

Official documentation

✦

YOUR MISSION

Explain BEGIN, COMMIT and ROLLBACK and test failure handling.

Take it with you

Run the failure test. Explain why stock returns to 3 when a later statement violates the FK.

At your own pace

My files and GitHub

Every lesson can become your own record: code, notes or a practice plan. Saving a file to GitHub is optional and does not change your XP.

Student guide

The editor and downloads open when this lesson is available to your account. Check access below or return to the track map.

Checking your access to this lesson…

Your path, step by step

Download activity materials

Download the lesson example and a workbook for your notes. The ZIP includes a guide to organizing activities in one or more repositories. Publishing is optional; eligible accounts can also use the GitHub upload button.

Your file for this lesson

Edit the example or record what you learned. This space does not run code or grade answers. The draft is saved only in this browser, separately per account; you choose when to download or upload to GitHub.

The example may be only a snippet or outline. Changing the extension does not make it a complete program. The activity ZIP contains starting material; the edited-file button downloads your text.

My code or notesFile format

Your text will be saved in this browser when you edit it. On a shared computer, clear your drafts when finished.

Direct upload accepts up to 8 KiB of text in one file. Use downloads and the manual guide for projects with multiple files.

Transactions and decisions

Let's discover together

Your data workbench

Official links and next step

Now it's your turn

Read, predict and check

Take it with you

My files and GitHub

Your file for this lesson

Save to my GitHub

Rullstpetbot