Küsimus

Anonüümne (09.12.2025 19:13)

Kuidas vältida konkurentsiprobleeme teise tabeli andmete kontrollimisel?

Vastus (11.12.2025 20:39):

Kui andmebaasioperatsiooni lepingus on näiteks kirjas eeltingimus, et uus kauba seisund peab olema aktiivne, siis selle eeltingimuse täidetust saab kontrollida operatsiooni käigus. Eelistada tuleks EXISTS alampäringut, kus on määratud FOR SHARE (PostgreSQL korral) või FOR UPDATE (Oracle korral). Selline lukk takistab kontrollitava seisundi samaaegset mitteaktiivseks muutmist teise transaktsiooni poolt.

CREATE TABLE Kauba_seisundi_liik (kauba_seisundi_liik_kood SMALLINT,

nimetus VARCHAR(50) NOT NULL,

on_aktiivne BOOLEAN NOT NULL DEFAULT TRUE,

CONSTRAINT pk_kauba_seisundi_liik PRIMARY KEY (kauba_seisundi_liik_kood),

CONSTRAINT ak_kauba_seisundi_liik_nimetus UNIQUE (nimetus));

INSERT INTO Kauba_seisundi_liik (kauba_seisundi_liik_kood, nimetus)

VALUES (1, 'Ootel'), (2,'Aktiivne');

Miks kasutada PostgreSQL korral FOR SHARE?

PostgreSQL pakub erinevaid rea tasemel lukke, mis on järjestatud kõige vähem piiravast kõige rohkem piiravani: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE. Lukkude valikul tuleks lähtuda põhimõttest: vali nii tugev lukk kui on ülesande lahendamiseks minimaalselt vajalik. Liiga tugeva luku valimine piirab asjatult teiste kasutajate samaaegset tööd.

FOR SHARE on jagatud lukk. See lubab teistel transaktsioonidel panna samale reale (kontrollimiseks) samuti FOR SHARE luku, kuid ei luba panna reale muutmiseks või kustutamiseks vajalikku eksklusiivset lukku. See tagab hea samaaegsuse: mitu erinevat tehingut saavad korraga kontrollida, kas seisund on aktiivne, ilma üksteist blokeerimata. Samas on välistatud oht, et keegi muudaks selle seisundi kontrollimise ajal mitteaktiivseks.

Tehing 1

START TRANSACTION;

SELECT * FROM Kauba_seisundi_liik  WHERE kauba_seisundi_liik_kood=1 AND on_aktiivne=TRUE FOR SHARE; --lause täidetakse

Tehing 2

START TRANSACTION;

SELECT * FROM Kauba_seisundi_liik WHERE kauba_seisundi_liik_kood=1 AND on_aktiivne=TRUE FOR SHARE; --lause täidetakse

Tehing 3

START TRANSACTION;

UPDATE Kauba_seisundi_liik SET on_aktiivne=FALSE WHERE kauba_seisundi_liik_kood=1; --kuna reale ei saa panna eksklusiivset lukku, siis jääb ootele

Seevastu FOR UPDATE lukk on liiga piirav. See ei luba panna reale teisi FOR UPDATE (ega ka FOR SHARE) lukke. Kui üks tehing on pannud seisundi reale kontrollimiseks FOR UPDATE luku, siis jäävad kõik teised tehingud, mis tahavad seda seisundit isegi vaid kontrollida, ootele kuni luku pannud tehing lõpeb ja lukk eemaldatakse. Kui kasutaksime liiga tugevat FOR UPDATE lukku, saaks korraga kontrolli teostada vaid üks kasutaja, teised peaksid ootama (tekkiks "pudelikael").

Tehing 1

START TRANSACTION;

SELECT * FROM Kauba_seisundi_liik  WHERE kauba_seisundi_liik_kood=1 AND on_aktiivne=TRUE FOR UPDATE; --lause täidetakse

Tehing 2

START TRANSACTION;

SELECT * FROM Kauba_seisundi_liik  WHERE kauba_seisundi_liik_kood=1 AND on_aktiivne=TRUE FOR UPDATE; --kuna reale ei saa panna eksklusiivset lukku, siis jääb ootele

Tehing 3

START TRANSACTION;

UPDATE Kauba_seisundi_liik SET on_aktiivne=FALSE WHERE kauba_seisundi_liik_kood=1; --kuna reale ei saa panna eksklusiivset lukku, siis jääb ootele

Probleem lööb tõsisemalt välja siis, kui korraga soovib selliseid kontrolle teha palju kasutajaid. Sellisel juhul hakkaksid nad üksteise järgi ootama ja süsteemi jõudlus langeks. Kasutan selle demonstreerimiseks pgbench utiliiti. Siin on selle utiliidi tutvustus ja siin dokumentatsioon. Teen seda viisil, et loon ise andmebaasi ning lasen pgbenchil käivitada selle andmebaasi põhjal enda koostatud tehinguid.

Lõin andmebaasi pood, kus käivitasin sellised laused:

CREATE TABLE Kauba_seisundi_liik (kauba_seisundi_liik_kood SMALLINT,

nimetus VARCHAR(50) NOT NULL,

on_aktiivne BOOLEAN NOT NULL DEFAULT TRUE,

CONSTRAINT pk_kauba_seisundi_liik PRIMARY KEY (kauba_seisundi_liik_kood),

CONSTRAINT ak_kauba_seisundi_liik_nimetus UNIQUE (nimetus));

INSERT INTO Kauba_seisundi_liik (kauba_seisundi_liik_kood, nimetus)

VALUES (1, 'Ootel'), (2,'Aktiivne');

CREATE TABLE Kaup (kaup_id INTEGER GENERATED ALWAYS AS IDENTITY,

kauba_seisundi_liik_kood SMALLINT NOT NULL DEFAULT 1,

hind SMALLINT NOT NULL,

CONSTRAINT pk_kaup PRIMARY KEY (kaup_id));

ANALYZE;

Lõin andmebaasist pood kaks ühesugust koopiat, käivitades createdb utiliiti nii, et see looks uue andmebaasi tehes koopia olemasolevast etteantud nimega andmebaasist.

createdb -T pood pood1

createdb -T pood pood2

Lõin kaks skriptifiali skript1.sql ja skript2.sql, mille paigutasin WinSCP programmi abil enda serveri kodukataloogi.

Falis skript1.sql oli tekst:

INSERT INTO Kaup (hind)

SELECT random(5, 2500) AS hind

WHERE EXISTS (SELECT *

FROM Kauba_seisundi_liik

WHERE kauba_seisundi_liik_kood=1

AND on_aktiivne=TRUE FOR SHARE);

Falis skript2.sql oli tekst:

INSERT INTO Kaup (hind)

SELECT random(5, 2500) AS hind

WHERE EXISTS (SELECT *

FROM Kauba_seisundi_liik

WHERE kauba_seisundi_liik_kood=1

AND on_aktiivne=TRUE FOR UPDATE);

Need skriptid lisavad tabelisse uusi kaupu, kusjuures kontrollivad selle juures, kas vaikimisi seisund 1 (ootel) on aktiivne. Seejärel kasutasin pgbench utiliiti, et käivitada ühte skriptidest ühe andmebaasi ning teist teise andmebaasi põhjal. Käivitan seda transaktsiooni simuleerides 10-t kasutajat, kasutades selleks serveri 2 protsessori lõime ja pommitades andmebaasi täpselt 60 sekundit.

Käivitasin need käsu shelli promptis.

pgbench -c 10 -j 2 -T 60 -n -f skript1.sql pood1

pgbench -c 10 -j 2 -T 60 -n -f skript2.sql pood2

Tulemused
Number of transactions actually processed näitab täidetud tehingute arvu, latency average näitab keskmist tehingu täitmise aega ning tps (transactions per second) tehingute arvu sekundis.

FOR SHARE

number of transactions actually processed: 427170

latency average = 1.403 ms

tps = 7128.911827 (without initial connection time)

FOR UPDATE

number of transactions actually processed: 101473

latency average = 5.903 ms

tps = 1694.174696 (without initial connection time)

Tulemusest on näha, et FOR SHARE kasutamise korral suudeti fikseeritud ajaperioodi jooksul läbi viia neli korda rohkem transaktsioone. Erinevus tuleneb sellest, et FOR UPDATE sundis kõiki 10 kasutajat ootama sama rea luku taga üksteise järel järjekorras (serialiseerimine), samas kui FOR SHARE lubas neil kõigil kontrolli teostada samaaegselt.

Miks Oracle'i korral kasutame FOR UPDATE?

Oracle'i lukustusmudel erineb PostgreSQL-ist. Seal on SQL-i tasemel arendaja jaoks ridade lukustamiseks päringu sees põhimõtteliselt vaid üks vahend – SELECT ... FOR UPDATE. Oracle'is puudub otsene vaste PostgreSQL-i FOR SHARE lukule, seega tuleb seal leppida rangema lukustamisega. FOR UPDATE on eksklusiivne lukk, mis tähendab, et isegi kui soovitakse andmeid vaid kontrollida (mitte muuta), ei saa teised tehingud samal ajal sama rida lukustada. See on paratamatu piirang Oracle'i keskkonnas konkurentsiolukorraga toimetulemiseks.

Hinda vastust:

Keskmine hinne: 5.0