SQL

Зміна даних і транзакції

INSERT, UPDATE, DELETE, ACID, ізоляція

Як додати дані через INSERT?

Перелічуй стовпці явно — тоді запит не зламається, якщо в таблицю додадуть нові. Можна вставити кілька рядків за раз. У PostgreSQL RETURNING одразу повертає створені значення, наприклад згенерований 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;

-- вставка результату іншого запиту
INSERT INTO accounts (user_id, currency, balance)
SELECT id, 'PLN', 0 FROM users WHERE email LIKE 'qa_%@test.com';

На співбесіді: INSERT додає рядки; я завжди перелічую стовпці явно, щоб запит не зламався від змін у таблиці, і можу вставити кілька рядків за раз. У PostgreSQL RETURNING одразу повертає згенерований id, що зручно для підготовки тестових даних: створив користувача і одразу маю його id для наступних кроків.

Як безпечно виконувати UPDATE і DELETE?

Головне правило: UPDATE і DELETE без WHERE змінюють усю таблицю. Звичка, що рятує:

Спочатку написати SELECT з тим самим WHERE і подивитися, які й скільки рядків потраплять під зміну.
Відкрити транзакцію.
Виконати зміну й перевірити кількість змінених рядків.
COMMIT — якщо все як очікувалося, ROLLBACK — якщо ні.

SELECT id, status FROM accounts WHERE user_id = 42;   -- 1. що зачепимо

BEGIN;                                                -- 2.
UPDATE accounts SET status = 'blocked' WHERE user_id = 42;
-- UPDATE 2   <- очікували 2 рахунки                    3.
COMMIT;                                               -- 4. або ROLLBACK;

На співбесіді: UPDATE і DELETE без WHERE змінюють усю таблицю, тому в мене залізна звичка: спочатку SELECT з тим самим WHERE, щоб побачити, які рядки зачеплю, потім зміна в транзакції, перевірка кількості змінених рядків і лише тоді COMMIT. Якщо цифра не та — ROLLBACK.

Чим відрізняються DELETE, TRUNCATE і DROP?

	DELETE	TRUNCATE	DROP
Що видаляє	Рядки (можна з WHERE)	Усі рядки	Таблицю повністю зі структурою
WHERE	Так	Ні	Ні
Швидкість	Повільно, по рядку	Швидко	Швидко
Тригери на рядки	Спрацьовують	Ні	Ні
Відкат	У транзакції — так	Залежить від СУБД: у PostgreSQL так, у MySQL ні	Залежить від СУБД
Лічильник id	Не скидається	MySQL скидає; PostgreSQL — лише з `RESTART IDENTITY`	Зникає разом з таблицею

На співбесіді: DELETE видаляє рядки за умовою, працює по рядку, запускає тригери і відкочується в транзакції. TRUNCATE швидко видаляє всі рядки без умови, і чи можна його відкотити, залежить від СУБД. DROP видаляє таблицю разом зі структурою. Для очищення тестових даних я використовую DELETE з точним фільтром, а TRUNCATE — лише на власному локальному середовищі.

Що таке транзакція і як QA її використовує?

Транзакція — група операцій, які виконуються як одне ціле: або всі, або жодна. Переказ — це списання з одного рахунку і зарахування на інший; якщо друге впало, перше теж має відкотитися.

BEGIN — почати
COMMIT — зафіксувати зміни
ROLLBACK — скасувати все з початку транзакції

Як QA використовує: безпечно змінювати дані вручну (є шлях назад); підготувати стан для перевірки і відкотити після; тестувати, що застосунок не лишає «половинчастих» операцій при збої.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 10;
UPDATE accounts SET balance = balance + 100 WHERE id = 11;
-- перевіряю, що обидва баланси правильні
SELECT id, balance FROM accounts WHERE id IN (10, 11);
COMMIT;   -- або ROLLBACK, якщо щось не так

На співбесіді: Транзакція — це група операцій, що виконуються як одне ціле: або всі, або жодна. Класичний приклад — переказ: списання і зарахування мають пройти разом. Як QA я використовую транзакції, щоб безпечно змінювати дані з можливістю відкату, і тестую, що застосунок не лишає половинчастих операцій, якщо на середині стається збій.

Що таке ACID?

Atomicity (атомарність) — транзакція виконується повністю або не виконується зовсім.
Consistency (узгодженість) — транзакція переводить базу з одного коректного стану в інший, не порушуючи обмежень: ключів, CHECK, унікальності.
Isolation (ізольованість) — паралельні транзакції не бачать проміжних станів одна одної (ступінь залежить від рівня ізоляції).
Durability (довговічність) — після COMMIT дані не зникнуть навіть при збої сервера.

На співбесіді: ACID — чотири гарантії транзакцій. Атомарність — все або нічого. Узгодженість — база переходить між коректними станами без порушення обмежень. Ізольованість — паралельні транзакції не бачать проміжних станів одна одної. Довговічність — зафіксоване після COMMIT не зникне навіть при збої. На прикладі переказу: гроші не можуть списатися без зарахування, і два паралельні перекази не можуть обидва витратити той самий залишок.

Що таке рівні ізоляції і які проблеми паралельного доступу вони вирішують?

Аномалії, що виникають, коли транзакції працюють одночасно:

Dirty read — читання незафіксованих змін іншої транзакції, які потім відкотять.
Non-repeatable read — повторне читання того самого рядка в межах транзакції дає інше значення.
Phantom read — повторний запит з тим самим фільтром повертає нові рядки.
Lost update — дві транзакції читають баланс 100, обидві віднімають 80 і записують 20. Одне списання «загубилося».

Рівні від слабшого до сильнішого: Read Uncommitted, Read Committed, Repeatable Read, Serializable. За замовчуванням у PostgreSQL — Read Committed, у MySQL InnoDB — Repeatable Read.

Захист від lost update: атомарний UPDATE … SET balance = balance - 80 WHERE balance >= 80, блокування рядка SELECT … FOR UPDATE або вищий рівень ізоляції.

BEGIN;
SELECT balance FROM accounts WHERE id = 10 FOR UPDATE;  -- блокує рядок
-- друга транзакція з FOR UPDATE чекатиме тут
UPDATE accounts SET balance = balance - 80 WHERE id = 10;
COMMIT;

На співбесіді: Коли транзакції працюють одночасно, бувають аномалії: брудне читання незафіксованих даних, неповторюване читання, фантомні рядки і втрачене оновлення. Рівні ізоляції — від Read Uncommitted до Serializable — по-різному від них захищають; у PostgreSQL за замовчуванням Read Committed. Як QA я тестую це паралельними запитами: наприклад, два одночасні списання з одного рахунку не повинні дати від'ємного балансу.

Як безпечно змінювати дані на спільному тестовому середовищі?

Працювати лише зі своїми даними: помічати їх маркером (префікс email qa_, тег, окремий тенант) і фільтрувати за ним.
Спочатку SELECT, потім зміна в транзакції, перевірка кількості рядків.
Не чіпати дані, від яких залежать колеги чи автотести; якщо треба — попередити в каналі команди.
Зберігати скрипти підготовки й очищення, щоб їх можна було повторити.
Ніколи не виконувати зміни на проді. Доступ до проду — лише читання і лише якщо дозволено.

На співбесіді: На спільному середовищі я змінюю лише власні дані, позначені маркером, наприклад префіксом в email, і фільтрую за ним. Спершу SELECT, потім зміна в транзакції з перевіркою кількості рядків. Скрипти підготовки й очищення зберігаю, щоб їх можна було повторити, і попереджаю команду, якщо зачіпаю спільні дані.

DELETE vs TRUNCATE vs DROP і обережність

DELETE видаляє рядки за умовою WHERE, записує кожну операцію в лог, запускає тригери й може бути відкочений у транзакції. TRUNCATE швидко очищає всю таблицю без умов і зазвичай скидає лічильник автоінкременту. DROP видаляє саму таблицю разом зі структурою, індексами і даними.

На спільному середовищі одна помилка зачіпає всю команду: DELETE чи UPDATE без WHERE змінить усі рядки. Тому спершу пишу SELECT з тією самою умовою й перевіряю, які саме рядки будуть змінені. Видаляю лише свої тестові дані, бажано позначені префіксом чи окремим акаунтом.

Транзакції допомагають: BEGIN, потім зміна, перевірка результату і ROLLBACK, якщо щось не так, або COMMIT, якщо все правильно. Варто пам'ятати, що в PostgreSQL TRUNCATE можна відкотити в транзакції, а в MySQL — ні, бо там це DDL-команда з неявним COMMIT. На продакшні без узгодження й бекапу не змінюю нічого.