SQL Tranzaksiyalar: COMMIT va ROLLBACK

Tranzaksiya — bir butun bo'lishi kerak bo'lgan amallar to'plami.

ACID tamoyillari

• Atomicity: Hamma amal bajariladi yoki hech biri
• Consistency: Ma'lumotlar yaxlit holatda qoladi
• Isolation: Tranzaksiyalar bir-biriga ta'sir qilmaydi
• Durability: Bajarilgan amallar saqlanib qoladi

Oddiy tranzaksiya

BEGIN;

UPDATE hisoblar SET balans = balans - 100000 WHERE id = 1;
UPDATE hisoblar SET balans = balans + 100000 WHERE id = 2;

COMMIT;   -- muvaffaqiyatli yakunlash

ROLLBACK

BEGIN;

UPDATE hisoblar SET balans = balans - 100000 WHERE id = 1;

-- Xato bo'lsa
ROLLBACK;   -- barcha o'zgarishlarni bekor qilish

SAVEPOINT

BEGIN;

INSERT INTO buyurtmalar VALUES (1, 'Mahsulot A');
SAVEPOINT nuqta1;

INSERT INTO buyurtmalar VALUES (2, 'Mahsulot B');

-- Faqat oxirgi amalni bekor qilish
ROLLBACK TO SAVEPOINT nuqta1;

COMMIT;

Python da tranzaksiya

import psycopg2

conn = psycopg2.connect(...)
conn.autocommit = False

try:
    cur = conn.cursor()
    cur.execute("UPDATE hisoblar SET balans = balans - 100000 WHERE id = 1")
    cur.execute("UPDATE hisoblar SET balans = balans + 100000 WHERE id = 2")
    conn.commit()
    print('Muvaffaqiyatli')
except Exception as e:
    conn.rollback()
    print(f'Xato: {e}')
finally:
    conn.close()