Changing data and transactions

INSERT, UPDATE, DELETE, ACID, isolation

How do you add data with INSERT?

List the columns explicitly, then the query won't break if new ones are added to the table. You can insert several rows at once. In PostgreSQL RETURNING immediately returns the created values, for example a generated id.

INSERT INTO users (email, name, country)
VALUES ('qa_1@test.com', 'QA One', 'PL'),
       ('qa_2@test.com', 'QA Two', 'UA')
RETURNING id, email;

-- inserting the result of another query
INSERT INTO accounts (user_id, currency, balance)
SELECT id, 'PLN', 0 FROM users WHERE email LIKE 'qa_%@test.com';

In an interview: INSERT adds rows; I always list the columns explicitly so the query doesn't break from changes in the table, and I can insert several rows at once. In PostgreSQL RETURNING immediately returns the generated id, which is handy for preparing test data: I created a user and at once have their id for the next steps.

How do you run UPDATE and DELETE safely?

The main rule: UPDATE and DELETE without WHERE change the whole table. A habit that saves you:

First write a SELECT with the same WHERE and look at which rows and how many will be changed.
Open a transaction.
Run the change and check the number of changed rows.
COMMIT if all is as expected, ROLLBACK if not.

SELECT id, status FROM accounts WHERE user_id = 42;   -- 1. what we will touch

BEGIN;                                                -- 2.
UPDATE accounts SET status = 'blocked' WHERE user_id = 42;
-- UPDATE 2   <- we expected 2 accounts                 3.
COMMIT;                                               -- 4. or ROLLBACK;

In an interview: UPDATE and DELETE without WHERE change the whole table, so I have an iron habit: first a SELECT with the same WHERE to see which rows I'll touch, then the change in a transaction, a check of the number of changed rows and only then COMMIT. If the number is wrong, ROLLBACK.

How do DELETE, TRUNCATE and DROP differ?

	DELETE	TRUNCATE	DROP
What it removes	Rows (can use WHERE)	All rows	The whole table with its structure
WHERE	Yes	No	No
Speed	Slow, row by row	Fast	Fast
Row triggers	Fire	No	No
Rollback	In a transaction, yes	Depends on the DBMS: yes in PostgreSQL, no in MySQL	Depends on the DBMS
The id counter	Not reset	MySQL resets it; PostgreSQL only with `RESTART IDENTITY`	Disappears with the table

In an interview: DELETE removes rows by a condition, works row by row, fires triggers and rolls back in a transaction. TRUNCATE quickly removes all rows without a condition, and whether it can be rolled back depends on the DBMS. DROP removes the table together with its structure. For cleaning up test data I use DELETE with a precise filter, and TRUNCATE only on my own local environment.

What is a transaction and how does QA use it?

A transaction is a group of operations that are executed as a whole: either all or none. A transfer is a debit from one account and a credit to another; if the second one failed, the first must also roll back.

BEGIN: start
COMMIT: save the changes
ROLLBACK: cancel everything from the start of the transaction

How QA uses it: to change data manually in a safe way (there is a way back); to prepare a state for verification and roll back afterwards; to test that the application doesn't leave "half-finished" operations on failure.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 10;
UPDATE accounts SET balance = balance + 100 WHERE id = 11;
-- I check that both balances are correct
SELECT id, balance FROM accounts WHERE id IN (10, 11);
COMMIT;   -- or ROLLBACK if something is wrong

In an interview: A transaction is a group of operations executed as a whole: either all or none. A classic example is a transfer: the debit and the credit must go through together. As QA I use transactions to change data safely with the ability to roll back, and I test that the application doesn't leave half-finished operations if a failure happens in the middle.

What is ACID?

Atomicity: a transaction is executed completely or not at all.
Consistency: a transaction moves the database from one valid state to another without violating constraints: keys, CHECK, uniqueness.
Isolation: parallel transactions don't see each other's intermediate states (the degree depends on the isolation level).
Durability: after COMMIT the data won't disappear even on a server failure.

In an interview: ACID is four guarantees of transactions. Atomicity: all or nothing. Consistency: the database moves between valid states without violating constraints. Isolation: parallel transactions don't see each other's intermediate states. Durability: what was committed with COMMIT won't disappear even on a failure. Using a transfer as the example: money can't be debited without being credited, and two parallel transfers can't both spend the same balance.

What are isolation levels and which concurrency problems do they solve?

Anomalies that arise when transactions work at the same time:

Dirty read: reading uncommitted changes of another transaction, which are then rolled back.
Non-repeatable read: reading the same row again within a transaction gives a different value.
Phantom read: a repeated query with the same filter returns new rows.
Lost update: two transactions read a balance of 100, both subtract 80 and write 20. One debit got "lost".

The levels from weaker to stronger: Read Uncommitted, Read Committed, Repeatable Read, Serializable. By default PostgreSQL uses Read Committed, and MySQL InnoDB uses Repeatable Read.

Protection against a lost update: an atomic UPDATE … SET balance = balance - 80 WHERE balance >= 80, row locking with SELECT … FOR UPDATE, or a higher isolation level.

BEGIN;
SELECT balance FROM accounts WHERE id = 10 FOR UPDATE;  -- locks the row
-- a second transaction with FOR UPDATE will wait here
UPDATE accounts SET balance = balance - 80 WHERE id = 10;
COMMIT;

In an interview: When transactions run at the same time, anomalies occur: a dirty read of uncommitted data, a non-repeatable read, phantom rows and a lost update. Isolation levels, from Read Uncommitted to Serializable, protect against them to different degrees; in PostgreSQL the default is Read Committed. As QA I test this with parallel requests: for example, two simultaneous debits from one account must not produce a negative balance.

How do you safely change data on a shared test environment?

Work only with your own data: mark it with a marker (an email prefix qa_, a tag, a separate tenant) and filter by it.
First SELECT, then the change in a transaction, a check of the row count.
Don't touch data that colleagues or automated tests depend on; if you have to, warn in the team channel.
Keep preparation and cleanup scripts so that they can be repeated.
Never make changes in production. Production access is read-only and only if allowed.

In an interview: On a shared environment I change only my own data, marked with a marker, for example a prefix in the email, and filter by it. First a SELECT, then the change in a transaction with a check of the row count. I keep the preparation and cleanup scripts so they can be repeated, and I warn the team if I touch shared data.

DELETE vs TRUNCATE vs DROP and caution

DELETE removes rows by a WHERE condition, writes each operation to the log, fires triggers and can be rolled back in a transaction. TRUNCATE quickly empties the whole table without conditions and usually resets the autoincrement counter. DROP removes the table itself together with its structure, indexes and data.

On a shared environment one mistake affects the whole team: a DELETE or UPDATE without WHERE changes all rows. So first I write a SELECT with the same condition and check which rows exactly will be changed. I delete only my own test data, preferably marked with a prefix or a separate account.

Transactions help: BEGIN, then the change, checking the result and ROLLBACK if something is wrong, or COMMIT if all is right. It is worth remembering that in PostgreSQL TRUNCATE can be rolled back in a transaction, but in MySQL it can't, because there it is a DDL command with an implicit COMMIT. In production I change nothing without approval and a backup.