Transaction hukusanya statements katika kizio kimoja cha atomic: ama zote zina-commit au zote zina-roll back, ikitoa A, C, I, D za ACID. Isolation level hurekebisha "I" — kiasi gani transactions zinazoendana zinaweza kuona data ambayo haijafanywa commit au inayobadilika ya nyingine.
| Level | Dirty read | Non-repeatable read | Phantom |
|---|
| READ UNCOMMITTED | inawezekana | inawezekana | inawezekana |
| READ COMMITTED | hapana | inawezekana | inawezekana |
| REPEATABLE READ (InnoDB default) | hapana | hapana | mara nyingi hapana* |
| SERIALIZABLE | hapana | hapana | hapana |
*REPEATABLE READ ya InnoDB inatumia MVCC (kila transaction husoma snapshot thabiti) pamoja na next-key locks, hivyo inazuia phantoms nyingi pia — imara zaidi kuliko kiwango cha SQL kinavyohitaji.
SELECT @@transaction_isolation; -- REPEATABLE-READ by default
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- snapshot taken here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both updates apply, or ROLLBACK undoes both
Baki na chaguo-msingi la InnoDB (REPEATABLE READ) isipokuwa una sababu. READ COMMITTED (ambayo Postgres/Oracle hutumia kama chaguo-msingi) inapunguza lock contention kwa mifumo yenye writes nyingi; SERIALIZABLE ni sahihi lakini inafanya kazi zinazogongana kuwa serial na inaweza kusababisha deadlock zaidi.
Isolation ni kifunguo kati ya usahihi na concurrency. Kuelewa anomalies hizi kunakuwezesha kueleza bugs kama "jumla haikukamilika chini ya mzigo" na kuchagua level inayoweka transactions zinazohamisha fedha kuwa sahihi bila kubana throughput bila sababu.
Maktaba ya maswali ya mahojiano ya IT yenye majibu ya kina — kutoka Junior hadi Senior.
Changia