ACID misollarda: PostgreSQLda amaliyot (5-qism)

Assalamu Alaykum bugun ACID ni postgres databazasida misollar bilan ko'rib chiqamiz.

Bu maqolani boshlashdan oldin bu seriyadagi boshqa maqolalarni o'qishni tavsiya qilaman :

Agenda

1. Tranzaksiya nima ?

2. ACID 1-qism (Atomicity)

3. ACID 2-qism (Isolation, Isolation levels)

4. ACID 3-qism (Consistency)

5. ACID 4-qism (Durability)

Unda misollarni boshladik , men misollarni dockerda run bo'layotgan postgresda amalga oshiraman boshqa databazaga oson almashtirish mumkin bo'lganligi va oson bo'lganligi uchun . Agarda sizda docker bo'lmasa ushbu linkda uni qanday o'rnatishni ko'rishingiz mumkin link.

ACID misollari

Dockerda postgresni ishga tushurish uchun buyruq :

docker run --name acid-example -d -e POSTGRES_PASSWORD=postgres postgres:latest

Va tepadagidek docker ps buyruqi orqali ishlayotgan docker containerlarini ko'rasiz.

Va boshqa terminaldan postgresning command shelliga kiramiz :

docker exec -it acid-example psql -U postgres

Birinchi bo'lin Atomicity va Consistencyni tekshirib ko'ramiz

1- bo'lib products jadval yaratib olamiz :

CREATE TABLE products (pid serial primary key, title text, price float, count int);

keyin sales jadvalini yaratamiz :

CREATE TABLE sales (sale_id serial primary key, product_id int, price float, quantity int); 

Ularni ichiga ishlashimiz uchun ma’lumotlar qo'shamiz :

INSERT INTO products (title, price, count) VALUES
('Laptop', 1200.50, 10),
('Smartphone', 699.99, 20),
('Tablet', 450.00, 15),
('Headphones', 79.99, 50),
('Smartwatch', 199.99, 30);
INSERT INTO sales (product_id, price, quantity) VALUES
(1, 1200.50, 2), 
(2, 699.99, 3), 
(3, 450.00, 1), 
(4, 79.99, 5), 
(5, 199.99, 2);

Atomicity va Consistency

1-Crash. Atomicityni crash bo'lganda tekshirish uchun tranzaksiya ochib sotuvni amalga oshiramiz, va tranzaksiya ishlayotgan payti uni commit qilmasdan yopamiz . Rasmda ko'rinib turganidek tranzaksiya bir butunlikda ishlamoqda, ya’ni crash bo'lsa ma’lumotlar databazaga yozilmadi.

begin transaction;
update products set count = count-15 where pid = 4;
exit; 
`

2-Success. Atomicityni tranzaksiya muvafaqiyatli bo'lganda tekshirish uchun tranzaksiya ochib,sotuvni amalga oshiramiz va uni commit qilamiz.Rasm ko'rib turganingizdek qiymatlar commitdan oldin va keyin 2 ta jadvalda ham o'zgargan va bu bizning tranzaksiya Atomicityga amal qilishini bildiradi. Bu esa bizni consistent databaza holatida turishga yordam beradi .

begin transaction;
update products set count = count-15 where pid = 5;
insert into sales (product_id, price, quantity) values (5,199.99,15);
select * from products;
select * from sales;
commit;

Isolation

Isolation level read committed

Bu holatda biz report uchun bir tranzaksiya ochamiz va unda ma’lumot yig'ayotgan paytimiz 2- tranzaksiya sotuvni amalga oshirib uni commit qilishga ulguradi. Rasmdan ko'rinib turganidek 1 queryni amalga oshirganimizda so'ng sotuv amalga oshib commit bo'lgan va 2 chi queryni qilganimzda bizdagi ma’lumotlar 1-chisidan boshqacha( qo'shimcha planshet sotilganini) ko'rsatmoqda. Bu inconsistent holat isolation orqali kelib chiqdi chunki isolation levelread committed da turipti .

begin transaction;
select product_id, count(product_id) from sales group by product_id;
select product_id, price, quantity from sales;
begin transaction;
insert into sales (product_id, price, quantity) values (3,459,5);
update products set count = count-5 where pid = 3;
commit;

Isolation level repeatable read

Tepadagi muammoni oldini olish uchun isolation leveli repeatable read bo'lgan tranzaksiya ishlatamiz. Bu bizga tranzaksiya ichida ma’lumotlar qayta o'qilganda 2 xil bo'lmasligiga yordam beradi. Rasmdan ko'rganingizdek sales jadvalga ma’lumot yozilgan faqat tranzaksiya ichida unga ruxsat bo'lmagan.

begin transaction isolation level repeatable read;
select product_id, count(product_id) from sales group by product_id;
select product_id, price, quantity from sales;
commit;

Durability

Durabilityni tekshirish uchun tranzaksiyani commit qilgan paytimizda postgres containerni o'chiramiz va qayta yoqib ma'lumot yozilganmi yoqmi tekshirib ko'ramiz. Rasmda ko'rinib turganidek ma’lumot saqlangan.

begin transaction;
insert into products ( title, price, count) values ('Nokia', 10, 1000);
commit;

Docker containerni to'xtatish uchun buyruq:

docker stop acid-example;

Xulosa

Mana ACID ni hamma qismlarini misollar bilan ko'rib chiqdik va tranzaksiya bizga uni taminlashiga amin bo'ldik.