C, PHP, VB, .NET

Дневникът на Филип Петров


* Заключване на данните при трансакция

Публикувано на 18 април 2009 в раздел Бази от Данни.

Синхронизацията на данните е изключително важна. За да демонстрираме това нека покажем първо един пример. Нека проверим колко пари има в акаунт с id = 1:

mysql> USE banks;
Database changed

mysql> SELECT amount FROM accounts
WHERE id = 1;
+--------+
| amount |
+--------+
| 306.38 |
+--------+
1 row in set (0.00 sec)

Сега нека напишем заявка UPDATE, с която искаме да изтеглим 500 лева, но така, че ако искаме да няма такава наличност, то заявката да не се изпълни:

mysql> UPDATE accounts 
SET amount = amount - 500 
WHERE id = 1 AND amount >= 500; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT amount FROM accounts
WHERE id = 1;
+--------+
| amount |
+--------+
| 306.38 |
+--------+
1 row in set (0.00 sec)

Нека сега стартираме две връзки към базата от данни и да започнем трансакции. С тези трансакции ние ще се опитаме да изтеглим два пъти по 250 лева от акаунт с id = 1:

Read Lock 1

Виждаме, че втората връзка не върна резултат - тя стои в "спящ" (idle) режим. Това е така, защото сървъра знае, че в този момент друга трансакция все още не е приключила, тоест резултатът от нея все още не е записан. Така втората връзка все още не "вижда", че вече сумата в акаунта е намалена и ако вземе 250 лева, то банката ще изгуби пари. Затова при стартиране на трансакция се прави т.нар. "заключване" (lock).

Нека сега приключим първата трансакция:

readlock2

Виждаме, че това се отрази моментално във втората връзка. Както очаквахме там UPDATE не се направи, защото в акаунта вече няма достатъчно пари (те бяха взети от първата връзка).

Същото заключване на трансакции важи и за INSERT заявки. Нека създадем една примерна елементарна база от данни:

mysql> CREATE DATABASE locks;
Query OK, 1 row affected (0.00 sec)

mysql> USE locks;
Database changed

mysql> CREATE TABLE test(
                     val INT
       ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO test
       VALUES (1), (2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

Нека сега стартираме две трансакции, с които искаме да прочетем най-голямата стойност в колоната val и съответно добавят нова стойност с едно по-голяма от втората:

Transaction insert lock

Виждаме, че отново втората трансакция изчаква завършването на първата. В момента, в който извършим COMMIT в първата трансакция втората също ще изпълни заявката си:

Transaction insert lock 2

Сега ще видим, че се е получило точно това, което искахме - първата трансакция е добавила val = 3, а втората val = 4:

Transaction insert lock 3

Ако заключването на връзката не съществуваше, то и двете трансакции щяха да вмъкнат стойност "3" и щяхме да имаме повтарящи се редове.

От тези примери трябва да си извадим важна бележка - важно е да приключваме трансакциите си възможно най-бързо. Ако ние се "бавим", то други трансакции ще трябва да ни изчакват. В MySQL има стандартен timeout от няколко секунди (естествено може да бъде настройван чрез my.cnf/my.ini) за изчакване - ако дадена заявка е в режим "изчакване" и този timeout бъде достигнат, то заявката ще пропадне.

Заключването на връзките важи за заявки UPDATE, INSERT и DELETE. По подразбиране то не е валидно за заявки от тип SELECT. MySQL обаче ни предоставя възможност да го правим ръчно:

SELECT <rows> FROM <table> FOR UPDATE;

В последния пример указваме, че данните, които са върнати от SELECT заявката, ще бъдат заключени. По този начин ако някоя друга трансакция се опита да чете или променя данните, то тя ще трябва да изчака изпълнението на първата започната.

По-слабо заключване е "LOCK IN SHARE MODE", с което позволяваме на други трансакции да четат, но не и да променят данните. Чрез такова заключване сме сигурни, че прочетените данни ще бъдат най-новите налични в системата и никоя друга трансакция няма да ги промени междувременно:

SELECT <rows> FROM <table> LOCK IN SHARE MODE;

С последния пример при отваряне на втора връзка всички видове заявки ще бъдат блокирани в idle режим докато първата трансакция не завърши.

 



5 коментара


  1. Това със заключването на данните при SELECT не ми стана много ясно. Ето какво опитвам:

    1.Без заключване
    BEGIN;
    SELECT FROM

    -в другия клиент мога да чета последно commit-натите данни, мога и да ги променям

    2.BEGIN;
    SELECT FROM FOR UPDATE;

    -в другия клиент мога да чета последно commit-натите данни, но не мога да ги променям, докато не си завърша транзакцията в първия клиент

    3. SELECT FROM LOCK IN SHARE MODE; - абсолютно същото като FOR UPDATE;

    Може ли да разясните с примери за случаите 2 и 3 - кога и какво може да се прави с данните?

  2. * горните заявки са SELECT columns FROM table, но не излизат понеже са в триъгълни скоби

  3. При SELECT...FOR UPDATE данните ще бъдат заключени и за други SELECT заявки в другия клиент. При SELECT...LOCK IN SHARED MODE ще бъдат заключени само за UPDATE, INSERT и DELETE.

  4. Да, това го разбрах и от статията, но при мен не работеше. Мисля, че разбрах защо, но не съм сигурен. Ако в единия клиент напиша SELECT columns FROM table FOR UPDATE, трябва и в другия клиент SELECT-а ми да съдържа FOR UPDATE, така ли е?

Добави коментар

Адресът на електронната поща няма да се публикува


*