Rеja Axborot tizimlari va ma'lumotlar bazasi

Tranzaksiyalarni boshqarish. Tranzaksiyalarni boshqarish uchun quyidagi buyruqlardan foydalaniladi:
COMMIT - O'zgarishlarni saqlaydi

ROLLBACK - O'zgarishlarni qaytaradi (bekor qilish)

SAVEPOINT - Tranzaksiyalar guruhi orqaga qaytarishi mumkin bo'lgan nuqtani yaratadi

SET TRANSACTION - Tranzaksiya nomini joylashtiradi.
Tranzaktsiyalarni boshqarish buyruqlari faqat DML buyruqlari uchun ishlatiladi: INSERT, UPDATE, DELETE. Ularni jadval yaratish, o'zgartirish yoki yo'q qilish paytida ishlatib bo'lmaydi.
Misollar:

Ishni boshlashdan oldin, avtomatik tranzaktsiyalarni bajarilishini o'chirish uchun quyidagi buyruqni bajaring:

mysql> SET autocommit=0;
Aytaylik, bizda developers jadvali quyidagi yozuvlarni o'z ichiga oladi:
+----+-------------------+-----------+------------+--------+

| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |

+----+-------------------+-----------+------------+--------+

| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |

| 3 | Andrei Komarov | C++ | 3 | 2500 |

| 4 | Konstantin Geiko | C# | 2 | 2000 |

| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |

| 7 | Ivan Ivanov | C# | 1 | 900 |

| 8 | Ludmila Geiko | UI/UX | 2 | 1800 |

+----+-------------------+-----------+------------+--------+

Quyidagi buyruq bilan C ++ dasturchilarining barchasini o'chirib tashlang:

mysql> DELETE FROM developers

WHERE SPECIALTY = 'C++';

mysql> COMMIT;
Ushbu so'rovni bajarish natijasida bizning jadval quyidagi yozuvlarni o'z ichiga oladi:

+----+-------------------+-----------+------------+--------+

| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |

+----+-------------------+-----------+------------+--------+

| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |

| 4 | Konstantin Geiko | C# | 2 | 2000 |

| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |

| 7 | Ivan Ivanov | C# | 1 | 900 |

| 8 | Ludmila Geiko | UI/UX | 2 | 1800 |

+----+-------------------+-----------+------------+--------+
Endi ROLLBACK buyrug'ini bajarishga harakat qilaylik:

mysql> ROLLBACK;

Ushbu buyruqni bajargandan so'ng bizning jadvalimizda quyidagi ma'lumotlar mavjud:
+----+-------------------+-----------+------------+--------+

| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |

+----+-------------------+-----------+------------+--------+

| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |

| 3 | Andrei Komarov | C++ | 3 | 2500 |

| 4 | Konstantin Geiko | C# | 2 | 2000 |

| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |

| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |

| 7 | Ivan Ivanov | C# | 1 | 900 |

+----+-------------------+-----------+------------+--------+
Ko'rib turganimizdek, C ++ dasturchisining yozuvi yana jadvalda.
Endi SAVEPOINT bilan shug'ullanishga harakat qilaylik.

Birinchidan, quyidagi so'rov yordamida saqlash nuqtasini yarataylik:

mysql> SAVEPOINT SP1;
Endi quyidagi so'rovlarni bajaramiz:
mysql> DELETE FROM developers WHERE ID = 7;

Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM developers WHERE ID = 6;

Query OK, 1 row affected (0.02 sec)

mysql> DELETE FROM developers WHERE ID = 5;

Query OK, 1 row affected (0.00 sec)

Ayni paytda bizning jadvalimizda quyidagi yozuvlar mavjud:
+----+-------------------+-----------+------------+--------+

| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |

+----+-------------------+-----------+------------+--------+

| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |

| 3 | Andrei Komarov | C++ | 3 | 2500 |

| 4 | Konstantin Geiko | C# | 2 | 2000 |

+----+-------------------+-----------+------------+--------+

Endi SP1 saqlash nuqtasiga quyidagi buyruq bilan qaytamiz:
mysql> ROLLBACK TO SP1;
Ushbu so'rovni bajargandan so'ng, jadvalimiz quyidagi yozuvlarni saqlaydi:
+----+-------------------+-----------+------------+--------+

| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |

+----+-------------------+-----------+------------+--------+

| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |

| 3 | Andrei Komarov | C++ | 3 | 2500 |

| 4 | Konstantin Geiko | C# | 2 | 2000 |

| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |

| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |

| 7 | Ivan Ivanov | C# | 1 | 900 |

+----+-------------------+-----------+------------+--------+

Ko'rib turganimizdek, biz SP1 saqlash nuqtasini yaratishda jadval holatiga qaytdik.

Endi biz ushbu saqlash nuqtasiga muhtoj bo'lmay turib, uni ozod qilishimiz mumkin:
mysql> RELEASE SAVEPOINT SP1;
Va nihoyat, ma'lumotlar bazasi tranzaksiyasini boshlash uchun ishlatiladigan SET TRANSACTION buyrug'ini ko'rib chiqamiz. Bu buyruq bizga tranzaksiyaning xususiyatlarini aniqlashga imkon beradi.

Masalan, agar biz tranzaksiya faqat o'qish uchun ekanligini ko'rsatmoqchi bo'lsak, unda biz quyidagi so'rovdan foydalanishimiz kerak.

SET TRANSACTION READ ONLY;

Agar biz tranzaksiyalar ma'lumotlarni yoza olishini istasak, unda so'rov quyidagicha bo'ladi:

SET TRANSACTION READ WRITE;