Едновременни транзакции

Накратко: Когато две транзакции четат и после записват едни и същи данни, едната може да затрие промяната на другата (изгубена промяна); спира се с една заявка UPDATE с условие или със заключване на реда.

Проблем

Последните пакети кафе

Кафето на зърна е 15 пакета. Касата в магазина и онлайн магазинът продават по един пакет в една и съща секунда. И двете програми правят едно и също: прочитат наличността, вадят 1 и записват новото число.

Пусни стъпките една по една. Всяка заявка се изпълнява наистина, върху обща база:

Касата · Онлайнбаза: магазин

Касата
```
SELECT stock FROM products WHERE id = 17;
```
Касата в магазина чете наличността на кафето: 15.
Онлайн
```
SELECT stock FROM products WHERE id = 17;
```
В същата секунда онлайн поръчка чете същото: 15.
Касата
```
UPDATE products SET stock = 14 WHERE id = 17;
```
Касата продава 1 пакет и записва 15 − 1 = 14.
Онлайн
```
UPDATE products SET stock = 14 WHERE id = 17;
```
Онлайн поръчката също продава 1 и също записва 14. Тя не знае за касата.
Касата
```
SELECT stock FROM products WHERE id = 17;
```
Продадени са 2 пакета, а наличността е паднала с 1. Едната промяна е изгубена.

Идеята

Изгубена промяна

Това е изгубена промяна (lost update). Всяка заявка поотделно е вярна. Грешката е в паузата между четенето и записа: през нея другата програма е записала нещо, което после е презаписано.

Най-простата защита е да няма пауза. Нека базата сама прочете и запише, в една заявка:

Касата · Онлайнбаза: магазин

Касата
```
UPDATE products SET stock = stock - 1
WHERE id = 17 AND stock >= 1;
```
Касата казва на базата „намали с 1, ако има“. Четенето и записът са в една заявка.
Онлайн
```
UPDATE products SET stock = stock - 1
WHERE id = 17 AND stock >= 1;
```
Онлайн поръчката прави същото. Базата изпълнява двете една след друга, всяка върху истинската текуща стойност.
Касата
```
SELECT stock FROM products WHERE id = 17;
```
15 − 2 = 13. Нищо не е изгубено.

Условието stock >= 1 пази и от продажба на нещо, което го няма. Колко реда е променила заявката, казва на програмата дали е продала:

Касата · Онлайнбаза: магазин

Касата
```
UPDATE products SET stock = stock - 5
WHERE id = 23 AND stock >= 5;
```
Комплектът ножове: има 5. Касата продава 5 наведнъж (подарък за фирма): променен е 1 ред.
Онлайн
```
UPDATE products SET stock = stock - 5
WHERE id = 23 AND stock >= 5;
```
Онлайн поръчката иска 5. Условието вече не е вярно: 0 реда са променени. Програмата казва на клиента „няма наличност“, вместо да продаде несъществуващи ножове.
Онлайн
```
SELECT stock FROM products WHERE id = 23;
```
Наличността не е паднала под нулата.

Пробвай

Три пакета, ако ги има

Напиши една заявка, която продава 3 пакета кафе (продукт 17) само ако има поне 3.

SQLбаза: магазин

SELECT id, stock FROM products WHERE id = 17;

Решение

UPDATE products SET stock = stock - 3
WHERE id = 17 AND stock >= 3;

Идеята

Нива на изолация

Не всичко се събира в една заявка. Затова базите пазят транзакциите една от друга, на нива. Колкото по-високо е нивото, толкова по-малко странни неща виждаш, но толкова повече се чака:

Ниво	Мръсно четене	Неповторяемо четене	Фантоми
READ UNCOMMITTED	възможно	възможно	възможни
READ COMMITTED	не	възможно	възможни
REPEATABLE READ	не	не	възможни
SERIALIZABLE	не	не	не

Мръсно четене: виждаш промяна, която другата транзакция още не е потвърдила (и може да отмени).
Неповторяемо четене: четеш един ред два пъти в транзакцията и получаваш различно, защото някой го е променил между двете.
Фантом: повтаряш заявка с условие и излизат нови редове, които някой е добавил.

PostgreSQL по подразбиране е на READ COMMITTED, MySQL (InnoDB) на REPEATABLE READ. SQLite е най-строг: в един момент пише само една транзакция, а останалите чакат.

По-точно, за любопитните

Таблицата е от стандарта. Отделните бази правят повече, отколкото тя изисква: в PostgreSQL READ UNCOMMITTED се държи като READ COMMITTED, а REPEATABLE READ не допуска и фантоми.

Идеята

Заключване и deadlock

За да изпълни обещанията, базата заключва: транзакция, която променя ред, го държи, докато не каже COMMIT или ROLLBACK. Който иска същия ред, чака.

Понякога двама чакат един друг: първата транзакция държи кафето и иска чая, втората държи чая и иска кафето. Това е deadlock. Базата го забелязва и прекъсва едната с грешка. Програмата трябва да е готова да я опита пак.

Как се намалява: кратки транзакции (без чакане на човек по средата), и всички програми да заключват в един и същи ред, например по нарастващ id на продукта.

По-точно, за любопитните

В PostgreSQL и MySQL SELECT … FOR UPDATE заключва прочетените редове още при четенето, затова „прочети, сметни, запиши“ в една транзакция става безопасно. SQLite няма FOR UPDATE. Там BEGIN IMMEDIATE заключва за писане цялата база още в началото на транзакцията.

Тест

Провери се

1. Защо UPDATE products SET stock = stock - 1 не губи продажби, а „прочети, сметни в програмата, запиши“ губи?Защото четенето и записът стават в една заявка, върху текущата стойностЗащото ползва WHEREЗащото е по-кратко

2. Какво е „мръсно четене“ (dirty read)?Четене на данни, които друга транзакция още не е потвърдилаЧетене на стари данни от кешаЧетене без WHERE

3. Две транзакции чакат една друга: първата държи ред 1 и иска ред 2, втората обратно. Какво прави базата?Чака вечноПрекъсва едната с грешка (deadlock), а другата продължаваИзпълнява ги наведнъж

Създаден от Иво Гергов като част от кампанията на Evo Tech за достъпно обучение по програмиране ·

Едновременни транзакции

Бисквитки