Tranzaksiyalarni boshqarishda so‘rovlar yaratish va qayta ishlash

SQL muhitida tranzaksiyalani boshqarish.

SQL tilida tranzaksiyalami maxsus operatorlatyordamida boshqarish imkoniyati mavjud. Shulardan biri tranzaksiya parametrlarini o‟matish operatori bo'lib, uniyozilish formati quyidgicha:
::=
SET TRANSACTION transaction mode> [ { transaction mode> }... ]
transaction mode> ::=

|
| «diagnostics size>
«isolation level> ::=
ISOLATION LEVEL «level of isolation>
::=
READ UNCOMMITTED | READ COMMITTED i REPEATABLE READ | SERIALIZAB1 1
transaction access mode>
READ ONLY | READ WRITE
diagnostics size> ::=
DIAGNOSTICS SIZE
::= Bu yerda:

• 
  Agar himoya darajalari ko'rsatilmasa, himoya darajasi SERIALIZABLE deb tushumniladi.
  
• 
  Agar ruxsat tartibi READWRITE kalit so'zi bilan belgilansa, unda himoyalanish darajasi READUNCOMMITTED bo'lmasligi kerak.
  
• 
  Agar ruzsat tartibi va himoyalanish darajasi READUNCOMMITTED deb ko'rsatilsa, unda beriladigan ruxsat tartibi READONLY deb tushuniladi, aks hollarda ruxsat tartibi READWRITE bo'ladi.
  

Ko'pchilik hollarda tranzaksiyalaming bajarilish jarayonida MB jadvallari stmkturasi buzilishining oldini olish uchun tranzaksiyalarga faqat o'qish tartibini o'rnatish mumkin. Buning uchun quyidagi operator ishlatiladi:
SET TRANSACTION READ ONLY;
Bu operator tranzaksiya jarayoni boshlanishidan oldin ko'rsatiladi.
Masalan, EXEC SQL SET TRANSACTION READ ONLY;
Buyurtmalarni qabul qilgan sotuvchini aniqlamoqchmiz. Bu ikki so‟rovni bir- biridan farq qilishi uchun matn kiritish yo'li bilan tashkillashtirisimiz mumkin:
SELECT a.snum, sname, onum, ‟Highest on‟, odate FROM Salespeople a.
Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders с WHERE c.odate = b,odate)
UNION
SELECT a.snum, sname, onum, ‟Lowest on‟, odate FROM Salespeople a, Orders b WHERE a.snum b.snum AND b.amt = (SEi FCT MIN (amt)FROM Orders с WHERE c.odate ; b.odate);
10 Peel 30 High o 10/05/

10	Peel	30	Low o	10/05/
10	Peel	30	High o	10/06/
10	Serre	30	High o	10/03/
10	Serre	30	Low o	10/04/
10	Serre	30	Low o	10/06/

10 Axel 30 High o 10/04/
Shu paytgacha UNION kalit so‟zi yordamida birlashtirilgan so'rov natijalari qanday tartibda matnga chiqarilishi to'g‟risida gapirmadik. Birlashtirilgan natijalarni ORDER BY kalit so'zi yordamida tartiblashtirish mumkin. Yoqoridagi misolni tartub raqamlariga nisbatan tartiblashni ko'rib o'tamiz.
SELECT a.snum, sname, onum, ‟Highest on‟, odate FROM Salespeople
a. Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders с WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum. ‟Lowest on‟, odate F ROM Salespeople
a. Orders b WHERE a.snum = b.snu AND b.amt = (SELECT MIN (amt) FROM Orders с WHERE c.odate = b.odate) ORDER BY 3; Natija quyidagicha:

1007 Rifkin	3001	Lowest on	10/03/199
1002 Serres	3005	Highest on	10/03/199
1002 Serres	3007	lowest on	10/04/199
1001 Peel	3008	Highest on	10/05/199
1001 Peel	3008	Lowest on	10/05/199
1003 Axelrod	3009	Highest on	10/04/199
1002 Serres	3010	Lowest on	10/06/199
1001 Peel	3011	Highest on	10/06/199

Nazorat savollari

1. 
   SQL muhitida tranzaksiyaning vazifasi nimadan iborat?
   
2. 
   TCL uchun muhim jarayon qaysi?
   
3. 
   Commit nima vazifani bajaradi? Misol keltiring.
   
4. 
   Tranzaksiyalarni boshqarishni tushuntiring?
   
5. 
   Rollback uchun misol keltiring?