Ma`ruza-12. Tranzaksiyalarni boshqarish. So`rovlar yaratish va qayta ishlash

MySQL kodi

• Aloqabankning Ipakyolibank_accounts mijozi crediti MySQL kodi.
• UPDATE Ipakyolibank_accounts

SET balance = balance + 650000

WHERE account_No = 1065656;

Account_No	Name	Tel	Address	Balance
1065656	Gulomov Alisher	[phone]	Buxoro sh	2500000+650000
1065785	Gulomov Bunyod	[phone]	Toshkent sh. Sergeli 6	1560000
1152476	Burxonov Ganisher	[phone]	Andijon sh.	0

Tranzaktsiya jurnaliga yozish

• INSERT INTO journal

VALUES (1002345, ‘Abdullaev Bobir’, ’26-11-2021’, 932656, 1065656, 650000);

journal

Transact_ID	Name of sender	Data	Sender	Reciever	Amount
932656	Abdullaev Bobir	26-11-2021	932656	1065656	650000
934556	Abdullaev Bobir	27-11-2021	932656	1065656	10000
938940	Abdullaev Bobir	28-11-2021	932656	1065656	100000

Tranzaktsiyani tugatish

• COMMIT WORK;
• yoki ROLLBACK TRANSACTION

Tranzaksiya

Transaktsiya xolatlari

• Tizim ishlamay qolsa yoki tranzaksiya davomida har qanday o'qish/yozish muammolari yuzaga kelsa, tizim avvalgi holatiga qaytishi kerak.
• Masalan:
• Elisning hisobidan $100 miqdorini ayirib tashash.
• Bobning hisobiga $100 qo'shish.
• Agar tizim Elisning hisobidan yechib olingandan so'ng, Bobning hisobidagi summani qo'sha olmasa, Elisning hisobidagi operatsiyani orqaga qaytarish.

Izolatsiyalash

• Agar (Mac va Elis o'rtasida) tranzaksiya amalga oshirilsa, Elis va Bob o'rtasidagi tranzaksiyaga hech qanday ta'sir qilmasligi kerak.
• Har ikkala operatsiya ham alohida bo'lishi kerak.

Tranzaktsiya kodi

BEGIN TRANSACTION TRAN1

UPDATE DimProduct

SET StockLevel = 4700

WHERE ProductKey = 213

INSERT INTO Sales (ProductKey, OrderQuantity, UnitPrice, SalesAmount)

VALUES(213, 300, 48.0673, 48.0673 * 300)

COMMIT TRANSACTION TRAN1

Tranzaktsiya

START TRANSACTION or BEGIN;

UPDATE bankaccounts SET funds=funds-100 WHERE accountno='ACC1';

UPDATE bankaccounts SET funds=funds+100 WHERE accountno='ACC2';

COMMIT;

START TRANSACTION;

UPDATE bankaccounts SET funds=funds-100 WHERE accountno='ACC1';

UPDATE bankaccounts SET funds=funds+100 WHERE accountno='ACC2';

ROLLBACK;

START TRANSACTION;

UPDATE accounts

SET balance = balance - 1000

WHERE id = 1;

UPDATE accounts

SET balance = balance + 1000

WHERE id = 2;

COMMIT;

Tranzaktsiya sintaksisi

• START TRANSACTION [WITH CONSISTENT SNAPSHOT]
• BEGIN [WORK]
• COMMIT [WORK] [AND [NO] CHAIN] [[NO]
• RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET autocommit = { 0 | 1 }
• SAVEPOINT identifierROLLBACK [WORK] TO [SAVEPOINT] identifierRELEASE SAVEPOINT identifier