Tranzaksiyalar va MVCC

Pul o'tkazmasi ikki amaldan iborat: birida pul kamayadi, ikkinchisida ko'payadi. Ikkinchisi bajarilmasa - pul yo'qoladi. Tranzaksiya aynan shuni oldini oladi.

CREATE TABLE hisob (
    id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    egasi   text NOT NULL,
    balans  numeric(10,2) NOT NULL CHECK (balans >= 0)
);

INSERT INTO hisob (egasi, balans) VALUES
    ('Husanboy', 500000.00),
    ('Malika',   120000.00),
    ('Nodira',    80000.00);

ACID #

Tranzaksiya to'rtta kafolat beradi:

BEGIN, COMMIT, ROLLBACK #

BEGIN;

UPDATE hisob SET balans = balans - 50000 WHERE egasi = 'Husanboy';
UPDATE hisob SET balans = balans + 50000 WHERE egasi = 'Malika';

COMMIT;

SELECT egasi, balans FROM hisob ORDER BY id;

BEGIN
UPDATE 1
UPDATE 1
COMMIT
  egasi   |  balans
----------+-----------
 Husanboy | 450000.00
 Malika   | 170000.00
 Nodira   |  80000.00
(3 rows)

Ikkala UPDATE birga saqlandi. Endi ROLLBACK ni ko'ramiz:

BEGIN;

UPDATE hisob SET balans = 0 WHERE egasi = 'Husanboy';

ROLLBACK;

SELECT egasi, balans FROM hisob ORDER BY id;

BEGIN
UPDATE 1
ROLLBACK
  egasi   |  balans
----------+-----------
 Husanboy | 500000.00
 Malika   | 120000.00
 Nodira   |  80000.00
(3 rows)

ROLLBACK dan keyin hech narsa o'zgarmadi - go'yo UPDATE umuman bo'lmagan.

Xato bo'lsa nima bo'ladi #

PostgreSQL da muhim xususiyat bor: tranzaksiya ichida xato bo'lsa, u butunlay to'xtaydi.

BEGIN;

UPDATE hisob SET balans = balans - 100000 WHERE egasi = 'Malika';

UPDATE hisob SET balans = balans - 999999 WHERE egasi = 'Nodira';

UPDATE hisob SET balans = balans + 100000 WHERE egasi = 'Husanboy';

COMMIT;

SELECT egasi, balans FROM hisob ORDER BY id;

BEGIN
UPDATE 1
ERROR:  new row for relation "hisob" violates check constraint "hisob_balans_check"
DETAIL:  Failing row contains (3, Nodira, -919999.00).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
  egasi   |  balans
----------+-----------
 Husanboy | 500000.00
 Malika   | 120000.00
 Nodira   |  80000.00
(3 rows)

Ikkinchi UPDATE CHECK (balans >= 0) cheklovini buzdi. Shundan keyin uchinchi buyruq ham bajarilmadi, va COMMIT aslida ROLLBACK ga aylandi.

ERROR:  current transaction is aborted, commands
        ignored until end of transaction block

SAVEPOINT #

SAVEPOINT - tranzaksiya ichidagi oraliq nuqta:

BEGIN;

UPDATE hisob SET balans = balans - 10000 WHERE egasi = 'Husanboy';

SAVEPOINT nuqta1;

UPDATE hisob SET balans = balans - 999999 WHERE egasi = 'Nodira';

ROLLBACK TO SAVEPOINT nuqta1;

UPDATE hisob SET balans = balans + 10000 WHERE egasi = 'Malika';

COMMIT;

SELECT egasi, balans FROM hisob ORDER BY id;

BEGIN
UPDATE 1
SAVEPOINT
ERROR:  new row for relation "hisob" violates check constraint "hisob_balans_check"
DETAIL:  Failing row contains (3, Nodira, -919999.00).
ROLLBACK
UPDATE 1
COMMIT
  egasi   |  balans
----------+-----------
 Husanboy | 490000.00
 Malika   | 130000.00
 Nodira   |  80000.00
(3 rows)

Xatoli buyruq bekor bo'ldi, lekin undan oldingi va keyingi ishlar saqlanib qoldi.

MVCC - ko'p versiyali boshqaruv #

PostgreSQL da UPDATE qatorni joyida o'zgartirmaydi. U yangi versiya yozadi va eskisini "eskirgan" deb belgilaydi.

Bu mexanizm MVCC (Multi-Version Concurrency Control) deb ataladi.

Har qatorda yashirin ustunlar bor. UPDATE dan oldin va keyin ularni solishtiring:

SELECT xmin, xmax, egasi, balans FROM hisob WHERE egasi = 'Nodira';

 xmin | xmax | egasi  |  balans
------+------+--------+----------
  742 |    0 | Nodira | 80000.00

UPDATE hisob SET balans = balans + 1000 WHERE egasi = 'Nodira';
UPDATE 1

SELECT xmin, xmax, egasi, balans FROM hisob WHERE egasi = 'Nodira';

 xmin | xmax | egasi  |  balans
------+------+--------+----------
  743 |    0 | Nodira | 81000.00

Ikkinchi so'rovda xmin boshqacha - bu boshqa qator versiyasi. Eskisi hali diskda yotibdi, lekin endi hech kim uni ko'rmaydi.

Ajratish darajalari #

Ikki tranzaksiya bir vaqtda ishlaganda bir-birini qanchalik ko'rishi - shu daraja bilan boshqariladi:

Uch atamaning ma'nosi:

SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed
(1 row)

Darajani bitta tranzaksiya uchun o'zgartirish mumkin:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT sum(balans) AS jami_balans FROM hisob;

COMMIT;

BEGIN
 jami_balans
-------------
   700000.00
(1 row)

COMMIT

REPEATABLE READ da bu SELECT tranzaksiya ichida necha marta takrorlansa ham, bir xil javob beradi - boshqalar shu orada yozgan bo'lsa ham.

Qulflar #

UPDATE qatorni qulflaydi: ikkinchi tranzaksiya shu qatorni o'zgartirmoqchi bo'lsa, birinchisi tugagunicha kutadi.

Navbatni o'zingiz boshqarmoqchi bo'lsangiz:

BEGIN;

SELECT id, egasi, balans FROM hisob
WHERE egasi = 'Husanboy'
FOR UPDATE;

COMMIT;

BEGIN
 id |  egasi   |  balans
----+----------+-----------
  1 | Husanboy | 500000.00
(1 row)

COMMIT

SKIP LOCKED navbat jadvallari uchun juda qulay: bir necha ishchi jarayon bitta jadvaldan vazifa olayotganda, hech biri boshqasini kutmaydi va bir vazifa ikki marta olinmaydi.

ERROR:  deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 567

Tranzaksiya qancha davom etishi kerak #

Qisqa. Iloji boricha qisqa.

Sabab: ochiq tranzaksiya qulflarni ushlab turadi va VACUUM ga eskirgan versiyalarni tozalashga to'sqinlik qiladi.

Xulosa #

• Tranzaksiya - yo hammasi, yo hech nima kafolati.
• BEGIN boshlaydi, COMMIT saqlaydi, ROLLBACK bekor qiladi.
• PostgreSQL da xatodan keyin tranzaksiya butunlay to'xtaydi.
• Shunda COMMIT aslida ROLLBACK ga aylanadi.
• SAVEPOINT xatoli qismni bekor qilib, qolganini saqlab qolish imkonini beradi.
• MVCC: UPDATE qatorni joyida emas, yangi versiya qilib yozadi.
• Shuning uchun o'qish yozishni to'smaydi, yozish o'qishni to'smaydi.
• Odatiy daraja - READ COMMITTED; qat'iyroq kafolat uchun REPEATABLE READ bor.
• FOR UPDATE SKIP LOCKED navbat jadvallari uchun eng qulay yechim.
• Deadlock dan qochish uchun qatorlarni doim bir xil tartibda qulflang.

Keyingi bo'limda sxemalar, rollar va qator darajasidagi xavfsizlikni ko'ramiz.