Views and transactions

Why it matters

Two problems appear as soon as a database is used by real people. First, the same long query gets copied into ten reports and slowly drifts apart. Second, some changes take several statements, and if the third one fails, the first two have already happened. Views solve the first problem by giving a query a name. Transactions solve the second by making several statements succeed or fail as one.

Views: a saved query with a name

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE deleted_at IS NULL;

Now you can query it like a table:

SELECT name FROM active_customers WHERE email LIKE '%@example.com';

A normal view stores no data. Every time you query it, the database runs the saved SELECT against the current tables, so it is always up to date.

Views are useful for:

• Reuse: define "active customer" or "monthly revenue" once, and every report uses the same logic.
• Simplicity: hide a five-table join behind a simple name for analysts or a BI tool.
• Security: grant a team access to a view that leaves out salary or personal columns, instead of the whole table (in engines with user permissions, such as PostgreSQL and SQL Server).

Change or remove a view with DROP VIEW active_customers; and create it again (PostgreSQL also has CREATE OR REPLACE VIEW). Dropping a view never deletes table data.

Some engines also offer materialized views, which store the result and must be refreshed. They trade freshness for speed on expensive queries. SQLite does not have them.

In SQLite you cannot INSERT into or UPDATE a view directly (unless you add special INSTEAD OF triggers). Other engines allow it for simple views. Either way, treat views mainly as a way to read.

Transactions: all or nothing

Moving money between two accounts takes two updates:

UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;

If the system crashes between them, 25 has disappeared. A transaction groups them:

BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

Until COMMIT, the changes are pending. If anything goes wrong, ROLLBACK; undoes everything since BEGIN, and the database is exactly as it was. Other users never see the halfway state where one account has been debited but the other not yet credited.

SQLite and PostgreSQL write BEGIN (or BEGIN TRANSACTION); MySQL also accepts START TRANSACTION; SQL Server uses BEGIN TRANSACTION. Outside an explicit transaction, most databases autocommit: each statement is its own transaction and is saved immediately.

ACID in plain words

Transactions are described by four properties:

• Atomic: all of the statements happen, or none do.
• Consistent: constraints hold before and after; a transaction that would break a rule is refused.
• Isolated: concurrent transactions do not see each other's half-finished work. Engines offer isolation levels that trade strictness for speed; that is a topic for later courses.
• Durable: once committed, the change survives a crash or power cut.

Using transactions as a safety net

Transactions are also a habit for manual changes, as promised in the INSERT/UPDATE/DELETE lesson:

BEGIN;
DELETE FROM sessions WHERE expires_at < '2024-01-01';
SELECT COUNT(*) FROM sessions;   -- check the result
-- happy?  COMMIT;   not happy?  ROLLBACK;

Keep transactions short. While one is open, it can hold locks that make other users wait, so do not start one and walk away. Also note that in some engines (MySQL, Oracle) schema statements like CREATE TABLE commit automatically, so they cannot be rolled back. PostgreSQL and SQLite can roll them back.

Putting them together

A typical small task: apply a change safely, then give the team a view to check it.

BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

CREATE VIEW account_summary AS
SELECT owner,
       balance,
       CASE WHEN balance < 50 THEN 'low' ELSE 'ok' END AS status
FROM accounts;

SELECT * FROM account_summary;

Because the view is computed on demand, any later transfer shows up in it automatically.

Common mistakes

• Forgetting COMMIT. Your changes may be discarded when the connection closes, and locks are held meanwhile.
• Assuming a view stores a snapshot. A normal view always reflects current data.
• Using SELECT * in a view that others depend on; later column changes can surprise them. List the columns.
• Long-running transactions that block other users.
• Expecting ROLLBACK to undo schema changes in engines that autocommit DDL.

Quick recap

• CREATE VIEW name AS SELECT ... names a query; it stores no data and is always current.
• Views give reuse, simpler access and controlled exposure of columns.
• BEGIN ... COMMIT makes several statements one atomic unit; ROLLBACK cancels it.
• ACID: atomic, consistent, isolated, durable.
• Keep transactions short and use them around risky manual changes.

Try it

Open the Do tab and make a transfer inside a transaction, then build a view that summarises the accounts.