SQL негіздері

7. Транзакциялар

Транзакция деп деректер қоймасында толық фиксацияланатын немесе толық қайтарылатын әрекеттердің жиынтығын айтады. Кейде транзакция деп SQL-операторларының тобын емес, SQL-операторларының барлығын фиксациялап немесе керісінше қайтарып жіберуге кететін уақыт интервалын айтады.

Осылайша, ақшаны бір шоттан екінші шотқа аударым операциясы бір транзакциядан тұру қажет. Әйтпесе, бірінші SQL-оператор ақшаны басқа шотқа аударатын болса, ал оларды шоттан түсіретін екіншісі келеңсіз жағдай арқасында соңына дейін орындалмай қалу жағдайын тудыру мүмкін.

Транзакцияны фиксациялау SQL-операторымен немесе әрбір SQL-оператордың аяқталуын орындаумен жүзеге асуы мүмкін. Екінші жағдайда автокоммит тәртібінде орындалуы мүмкін. Әдетте, SQL-операторының интерактивті тәртіпте орындалуы автокоммитті қолданады. Жиі құрудың қосарланған орталарында жұмысты деректер қоймаларымен инкапсуляциялайтын класстар, қалыпты жағдайда автокоммит тәртібін ұсынады.

Келесі сұлба транзакцияларды қолдану принципін көрсетеді:

Транзакцияны қолдану принципі

Жаңа транзакция деректер қоймасымен жұмыстың әрбір жаңа сеансының басталуымен басталады. Сонан соң барлық орындалатын SQL-операторлар COMMIT WORK немесе ROLLBACK WORK операторлары орындалмайынша бір транзакцияға кіреді.

COMMIT WORK операторы деректер қоймасында өзгерістерді орындап, ағымды транзакцияны аяқтайды. Кейде COMMIT WORK операторы транзакцияны орындайды деп те айтады.

ROLLBACK WORK операторы ағымды транзакцияда орындалған барлық SQL-операторлардың әрекетін қайтарып, транзакцияны қайтарады.

Логика бойынша транзакция өзара байланысқан операциялардың орындалуын қамтамасыздандырады. Осылайша, егер транзакцияларды өзара байланыспаған операторлар тізбегінен тұратындай «өте үлкен» ететін болсақ, онда транзакцияны автоматты қайтаратын кез келген істен шығу, "қысқа" транзакцияларда табысты аяқталуы мүмкін әрекеттердің қайтарылуына әкеледі.

Өзгерістерді автоматты фиксациялау

Көптеген коммерциялық ДҚБЖ (СУБД) өзгерістерді автоматты фиксациялау тәртібін қосуға мүмкіндік береді - автокоммит.

Бұл тәртіпті қосу үшін (барлық ДҚБЖ-лармен емес) SET AUTOCOMMIT ON қолдануға болады, ал тәртіпті қайтару үшін - SET AUTOCOMMIT OFF қолданылады.

Транзакцияларды параллельді қолданудағы қиындықтар

Транзакцияларды параллельді қолдануда келесідей қиындықтар кездесуі мүмкін:

қайталанбайтын оқу (non-repeatable read);
"лас" оқу (dirty read) – қайтарылған транзакциямен жазылған деректерді оқу;
жоғалған жаңарту (lost update);
фантомдық кірістіру (phantom insert).

Берілген қиындықтар мүмкін болатын жағдайларды қарастыру.

Қайталанбайтын оқу

Болжамға сәйкес, әртүрлі қосымшалармен ашылған және келесідей орындалған SQL-операторлары бар екі транзакция бар делік:

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;

2-транзакцияда f2 алаңының мәні алынады, сонан соң1-транзакцияда f2 алаңының мәні өзгертіледі. f2 алаңынан мәнді қайта алу жағдайында 1-транзакцияда басқа нәтиже алынады ( 7.1-сурет). Бұл жағдай деректердің бөлшектеп өзгерту және деректер қоймасына қайта жазу кезінде әсіресе жағымсыз.

7.1-сурет.Қайталанбайтын оқу

"Лас" оқу

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

ROLLBACK WORK;

SELECT f2 FROM tbl1 WHERE f1=1;

1-транзакцияда f1 алаңының мәні өзгертіледі, сонан соң 2-транзакцияда f2 алаңының мәні алынады. Осыдан кейін 1-транзакцияның қайтарылуы орындалады. Нәтижесінде 2-транзакциямен алынған нәтиже деректер қоймада сақталған мәннен өзгеше болады.

Жоғалған жаңарту

Транзакция 1

Транзакция 2

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=20 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=25 WHERE f1=1;

1-транзакцияда f1 алаңының мәні өзгертіледі, ал сонан соң 2-транзакцияда бұл алаң мәні тағы да өзгертіледі. Нәтижесінде бірінші транзакциямен орындалған өзгерту жоғалған болып шығады.

Фантомдық кірістіру

Транзакция 1

Транзакция 2

INSERT INTO tbl1 (f1,f2) VALUES (15,20);

SELECT SUM(f2) FROM tbl1;

2-транзакция f2 алаңының барлық мәндерін қолданатын SQL-оператор орындалады. Сонан соң 1-транзакцияда жаңа жол кірістіріледі де, 2-транзакцияда SQL-операторының қайта орындалуы басқа нәтиже береді. Мұндай жағдай фантомдық кірістіру болып табылады да, қайталанбайтын оқудың жеке түрі болып келеді. Мұнда, егер орындалатын SQL-оператор f2 алаңының барлық мәндерін алмай, тек кестенің бір жолының мәнін алатын болса (WHERE предикаты қолданылады), онда INSERT операторының орындалуы фантомдық кірістіру жағдайына әкелмейтін болады.

Изоляция деңгейлері

SQL-92 стандарты берілген келеңсіз жағдайлардың алдын алатын изоляция деңгейлерін анықтайды.

Изоляция деңгейлерінің келесідей төрт түрі енгізілген:

SERIALIZABLE – тізбекті орындалу (қалыпты жағдайда қолданылады). Бұл деңгей жоғарыда көрсетілген барлық келеңсіз жағдайлардың алдын алуын қамтамасыздандырады, алайда, сәйкесінше, параллелизмнің ең төмен деңгейі байқалады;
REPEATABLE READ – қайталанатын оқу. Бұл деңгейде"фантомдық кірістіру" келеңсіз жағдайына әкелетін INSERT операторларының орындалуы мүмкін. Бұл деңгейді егер орындалатын SQL-операторына жаңа жолдарды қосу әсер етпесе өолданған жөн;
READ COMMITTED – фиксацияланған оқу . Бұл деңгей бірдей сұратуларға әртүрлі нәтиже алу қажет болған жағдайда қолданған жөн, алайда тек деректерде өзгертулерді орындаған транзакцияны фиксациялағаннан кейін;

READ UNCOMMITTED – фиксацияланбаған оқу . Мұнда транзакцияны фиксациялаусыз бірдей сұратулар үшін әртүрлі нәтиже алуға болады.

Келесі кестеде изоляция деңгейлері формалды түрде келтірілген.

Изоляция деңгейі	Предотвращение конфликтной ситуации
Изоляция деңгейі	Қайталанбайтын оқу (non-repeatable read)	"лас" оқу (dirty read)	Жоғалған жаңарту (lost update)	Фантомдық кірістіру (phantom insert)
SERIALIZABLE	+	+	+	+
REPEATABLE READ	+	+	+	-
READ COMMITTED	-	+	+	-
READ UNCOMMITTED	-