Repeatable Read Transaction Başlatmak
Örnek
Şöyle yaparız
=# SET default_transaction_isolation TO 'Repeatable Read';Repeatable Read Başladıktan Sonra - SELECT Hep Aynı Sonucu Döndürür
Örnek
Elimizde şöyle bir kod olsun. TX1 a tablosunda b tablosundaki toplam satır sayısını yazıyor. TX2 ise b tablosuna a tablosundaki toplam satır sayısını yazıyor.
TX2 a tablosundan select yapar. TX1 a tablosunda insert yapıp, commitlese bile, TX2 yine a tablosundan select yaparsa boş sonuç elde eder.
-- Tx1 -- Tx2=# BEGIN; =# BEGIN;BEGIN BEGIN
=# INSERT INTO a SELECT count(*) FROM b;INSERT 0 1 =# INSERT INTO b SELECT count(*) FROM a; INSERT 0 1=# COMMIT;COMMIT =# SELECT COUNT(*) FROM a; count ------- 0 (1 row) =# COMMIT; COMMITÖrnek
Elimizde şöyle iki transaction olsun
-- Tx1:simple_bank> select * from accounts;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------2 | two | 100 | USD | 2020-09-06 15:06:44.666424+003 | three | 100 | USD | 2020-09-06 15:06:44.666424+001 | one | 80 | USD | 2020-09-06 15:06:44.666424+00(3 rows)-- Tx2:simple_bank> select * from accounts where id = 1;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00(1 row)simple_bank> select * from accounts where balance >= 80;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------2 | two | 100 | USD | 2020-09-06 15:06:44.666424+003 | three | 100 | USD | 2020-09-06 15:06:44.666424+001 | one | 80 | USD | 2020-09-06 15:06:44.666424+00(3 rows)
TX1 hesaptan 10 TL çekerek güncelleme yapar
-- Tx1:simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00(1 row)UPDATE 1
TX1 commitler ancak TX2 halen ilk okuduğu veriyi görmektedir.
-- Tx1:simple_bank> commit;COMMIT-- Tx2:simple_bank> select * from accounts where id = 1;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00(1 row)
Hatta TX2'nin eski veriyi gördüğü bile tekrar kontrol edilebilir
-- Tx2:simple_bank> select * from accounts where balance >= 80;id | owner | balance | currency | created_at---------+-------+---------+----------+-------------------------------2 | two | 100 | USD | 2020-09-06 15:06:44.666424+003 | three | 100 | USD | 2020-09-06 15:06:44.666424+001 | one | 80 | USD | 2020-09-06 15:06:44.666424+00(3 rows)
Ama TX2 update yapamaz, çünkü yaparsa normalde Lost Update problemi olur. PostgreSQL buna izin vermediği için hata verir
-- Tx2:simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;ERROR: could not serialize access due to concurrent update
Örnek
Elimizde şöyle iki tane sql olsun. TX1 foo tablosunda yeni satır yaratır
INSERT INTO foo (name) VALUES ('BAZ')TX2 foo tablosunu sorgular ve bar tablosuna yazar
INSERT INTO bar (foo_name, foo_id) VALUES ('BAZ', (SELECT id FROM foo WHERE name = 'BAZ'))Eğer iki cümle Read Committed olarak eşzamanlı (concurrent) çalıştırılıyorsa ikinci cümlenin id sütununun bazen null olduğu görülebilir. Açıklaması şöyle
it is possible under certain circumstances to end up inserting a row into bar where foo_id is NULL. The two queries are executed in different transactions, by two completely different processes.
Ancak eğer TX2'de Repeatable Read kullanırsak TX1 ile aynı anda başlarsa TX1'in hiçbir yazdığını göremez. Açıklaması şöyle
The subselect in the INSERT INTO bar cannot see the new row concurrently inserted in foo because the latter is not committed yet.But by the time that the query that checks the foreign key constraint is executed, the INSERT INTO foo has committed, so the foreign key constraint doesn't report an error.A simple way to work around that is to use the REPEATABLE READ isolation level for the INSERT INT bar. Then the foreign key check uses the same snapshot as the INSERT, it won't see the newly committed row, and a constraint violation error will be thrown.
Repeatable Read Başladıktan Sonra - Lost Update Problemi Olmaz
Eğer bir başka transaction bizim istediğimiz satırı değiştirir ve commit'lerse "could not serialize access due to concurrent update" hatası alırız. Açıklaması şöyle.
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message.
ERROR: could not serialize access due to concurrent update
Örnek
Elimizde şöyle bir kod olsun. Burada TX1 update işlemine başlıyor. TX2 is select işlemine başlıyor. Birbirlerini engellemezler. Ancak TX'de aynı anda yazmak isterse Lost Update Problem olacağı için TX2 rollback edilir.
-- Tx1 -- Tx2=# BEGIN; =# BEGIN;BEGIN BEGIN
=# UPDATE list SET x=x-1; =# SELECT * FROM list; x --- 1 2 3 4 (4 rows) =# DELETE FROM list WHERE x=4; -- (Tx2 gets blocked)
=# COMMITCOMMIT -- Tx2 ends in error ERROR: could not serialize access due to concurrent update =# \echo :SQLSTATE 40001 =# ROLLBACK; ROLLBACKÖrnek
Şöyle yaparız. TXA lost update problemine maruz kalacağı için rollback edilir.
process A: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;process B: BEGIN;process B: UPDATE purchases SET value = 500 WHERE id = 1;process A: UPDATE purchases SET value = 600 WHERE id = 1;-- process A wants to update the value while process B is changing it-- process A is blocked until process B commits
process B: COMMIT;process A: ERROR: could not serialize access due to concurrent update-- process A immediately errors out when process B commits