ACID - Isolation - Transaction'ların Birbirlerini Etkilememesi

Isolation Nedir

Transaction'ların birbirlerini etkilememesi anlamına gelir. Designing Data Intensive Applications kitabındaki açıklama şöyle.

Most databases are accessed by several clients at the same time. That is no problem if they are reading and writing different parts of the database, but if they are accessing the same database records, you can run into concurrency problems (race conditions).

...

Isolation in the sense of ACID means that concurrently executing transactions are isolated from each other: they cannot step on each other’s toes. The classic database textbooks formalize isolation as serializability, which means that each transaction can pretend that it is the only transaction running on the entire database. The database ensures that when the transactions have committed, the result is the same as if they had run serially (one after another), even though in reality they may have run concurrently.

However, in practice, serializable isolation is rarely used, because it carries a performance penalty. Some popular databases, such as Oracle 11g, don’t even implement it. In Oracle there is an isolation level called “serializable,” but it actually implements something called snapshot isolation, which is a weaker guarantee than serializability

Isolation Seviyeleri Nedir
ACID içindeki isolation seviyesi ve performans arasında bir seçim yapmak gerekiyor. Bu yüzden veri tabanları isolation seviyesini kontrol edebilmek için imkan tanıyorlar.

Seviyeler en güçlüden en zayıfa doğru şöyle

- Serializable

- Repeatable Read

- Read Committed

- Read Uncommitted

- Oracle default isolation level olarak "Read Committed" kullanıyor. SQL Server da aynı seviyeyi kullanıyor. Bu seviyede her iki veri tabanı için Non-Repeatable Read ve Phantom Read hataları olabilir.

- InnoDB ise ise default isolation level olarak repeatable read seviyesini kullanıyor. Bu seviyede nonrepeatable read hatası olmazken phantom read hataları halen olabiliyor.

Isolation Level ve Çözdükleri Problemler (Anomaly)
Şeklen şöyle. Burada Snapshot diye SQL Server'a özel bir isolation level daha gösteriliyor.

Read Committed Seviyesi Nedir?

Read Committed Nedir yazısına taşıdım.

Repeatable Read Seviyesi Nedir ?

Repeatable Read Nedir yazısına taşıdım.

Serializable Seviyesi Nedir ?

Serializable Nedir? yazısına taşıdım.

Ayrıca anomaliler için 

Dirty Read ve Dirty Write

Lost Update Problem

Non Repeatable Read

Phantom Read

yazılarına bakabilirsiniz.

ACID - Serializable Nedir ?

Giriş

Şeklen şöyle

SQL Server için şeklen şöyle

 Açıklaması şöyle. Range içine yeni satır eklenemediği veya silinemediği için Phantom Read engellenir.

When T1 queries a range or records, it acquires a different type of lock, signifying that it belongs to the range.

This lock is called Range-lock, (Range S-S is its status) instead of S for Read lock and X for the Write lock.

So when T1 queries a range, all the rows are range-locked.

If T2 tries to insert a new row, which might affect this range, then T2 will be blocked until T1 completes and releases the Range lock.

However, T2 can read the rows, as Range-lock allowed shared reads, but prevents certain Writes.

Örnek - Spring

Şöyle yaparız

@Transactional(isolation = Isolation.SERIALIZABLE)

ACID - Non Repeatable Read

Giriş

Şeklen şöyle

SQL Server için şeklen şöyle

Açıklaması şöyle

This can happen when a Transaction tries to read the DB row multiple times and gets different results each time e.g. if T1 reads a DB row at 2 different times, and in between these 2 reads, T2 updates the row.

Açıklaması şöyle

If a transaction reads a database row without applying a shared lock on the newly fetched record, then a concurrent transaction might change this row before the first transaction has ended.

Örnek

Aynı veriyi iki defa okuyup farklı sonuçlar alan bir örnek şöyle

The best example is, a client might order a product based on a stock quantity value that is no longer a positive integer). With the Read-Committed isolation level, it is possible to avoid non-repeatable (fuzzy) reads.

Örnek

Şeklen şöyle

Açıklaması şöyle

1. Alice and Bob start two database transactions.

2. Bob’s reads the post record and title column value is Transactions.

3. Alice modifies the title of a given post record to the value of ACID.

4. Alice commits her database transaction.

5. If Bob’s re-reads the post record, he will observe a different version of this table row.

ACID - Read Committed Seviyesi Nedir ?

Giriş

Şeklen şöyle. İkinci satırda Repeatable Read görülebilir.

SQL Server için şeklen şöyle

PostgreSQL için açıklama şöyle. Yani PostgreSQL'in desteklediği en düşük ve ilk seviye bu

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

Designing Data Intensive Applications kitabındaki açıklama şöyle.

The most basic level of transaction isolation is read committed. It makes two guarantees:

1. When reading from the database, you will only see data that has been committed

(no dirty reads).

2. When writing to the database, you will only overwrite data that has been committed

(no dirty writes).

Yani

- Sadece Dirty Read ve Dirty Write problemini engeller. 

- Non Repeatable Read problemini engellemez. Aynı transaction içinde iki okuma farklı sonuç verebilir, çünkü başka transaction veriyi değiştirebilir.

- Lost Update problemini engellemez

- Phantom Read problemini engellemez. Benim dokunmadığım satırlar kilitlenmediği için bunlar değiştirilebilir veya yeni satır eklenebilir

- Phantom satır kapsamında Write skew problemini engellemez.

Non-Repetable Read

Örnek

Şeklen şöyle. Açıklaması şöyle

In the following diagram, the concurrent transactions see the real-time change the other made. Transaction 1 gets chris as the latest committed name even though Transaction 2 began after Transaction 1.

Burada Transaction 1 başlıyor ve 2 numaralı satırı okuyor, ancak 3 numaralı satıra dokunmuyor. Transaction 2 ise 3 numaralı satırı değiştirip commit'liyor. Transaciton 2, 3 numaralı satırı okursa değişmiş veriyi görüyor. Bu problemin ismi "Non-Repeatable Read" ve "Repeatable Read" isolation seviyesi bu problemi engeller.

Select İşlemi - Non Repeatable Read Problemi

Read Committed seviyesinde select cümlesinin başında konulan shared lock, cümle bitiminde transaction bitmemiş olsa bile kaldırılır. Açıklaması şöyle

T1 gets Read Lock, reads, and releases the Read lock asap once the read is done.

T2 can execute and update the row as it is unlocked now.

So When T1 tries to re-read the same row, it will get a different result.

Örnek

Şöyledir

T1 begins

T1 Reads A‘s Balance=Rs. 1000 [Read-1]

T2 begins

T2 Read A’s Balance= Rs. 1000 (Can read as Row= Read Locked)

(T1 reads and releases Read Lock ASAP, and the row is unlocked now)

T2 Update A’s Balance= Rs. 500 (Can Write as Write lock is free for this row)

T2 Commit. (Releases Write Lock, now row is free to Read/Write)

T1 Reads A‘s Balance=Rs. 500 [Read-2] [Different Result] [non-Repeatable]

Select İşlemi - Phantom Reads Problem

Örnek

Şöyledir

T1 begins

T1Queries select * from Tbl where X>100 → 3 rows

T2 begins

T2 Inserts 1 additional row with X=150.

T2 Commits.

T1 Queries select * from Tbl where X>100 → 4rows [Phantom Read].

ACID - Phantom Read Nedir ? (Eksik veya fazla satır)

Giriş

Şeklen şöyle

Açıklaması şöyle

A phantom read occurs when two identical queries are executed during a transaction and the row collection returned by the second query is different from the first

Phantom Read 2 şekilde ortaya çıkar

1. Belli bir koşula bakılarak satır eklenmesi/çıkarılması

1. Belli bir koşula bakmadan satır eklenmesi/çıkarılması

1. Belli bir koşula bakılarak satır eklenmesi/çıkarılması

Açıklaması şöyle. Burada SELECT ile bir koşula veya sayaç değerine bakılıyor. Daha sonra satır ekleniyor/çıkartılıyor

Having said that, a phantom read would always follow the same pattern

1. Run a SELECT query to get data that matches certain conditions.

2. Depending on the result of first the transaction either proceeds or aborts.

3. If the transaction proceeds ahead then you make some INSERT, UPDATE, DELETE call and commits the transaction.

Açıklaması şöyle

This is a very common problem and can happen in these sort of systems

1. Booking system (Two people book the same room)

2. User management system (Two people get the same username)

3. Customer service system (One agent gets assigned multiple calls at the same time)

Örnek

Bir örnek şöyle

The item count is 1 in the database.

1. User 1 fetches the count and gets 1 in return.

2. User 2 fetches the count and gets 1 in return.

3. User 1 sees count > 0, assumes the item is available and places the order, and decrements the count by 1.

4. User 2 sees count > 0, assumes the item is available and places the order, and decrements the count by 1.

5. User 1 order placed.

6. User 2 order placed.

Açıklaması şöyle

So both the user was actually able to book the item even though only 1 item was available. ... In simple words, multiple transactions read the same object to decide if a transaction should go through and then might update that or other objects.

2. Belli bir koşula bakmadan satır eklenmesi/çıkarılması

Açıklaması şöyle

This can happen if T1 queries some range of rows (say N rows), and meanwhile, T2 inserts an extra row matching the same query conditions of T1.

Then if T1 searches again, it will get an additional row(phantom read).

Kısaca aynı transaction içinde aynı sorguyu iki defa çalıştırıp, bir başka transaction'ının satır ekleyip silmesi yüzünden, eksik veya fazla satır sayısının alınması anlamına geliyor. 

Burada dikkat edilmesi gereken şey her iki sorguda da mevcut olan satırların aynı değerlere sahip olmasının garanti edilmesi. Sadece aradaki satırlar eklenip çıkarılabiliyor. Aynı değere sahip olmasının garanti edilmesi Read Committed Seviyesinde Select başlığındaki SCN numarası ile alakalı.

- Eksik veya fazla satırın olması select sum(x) from table; gibi sql cümlelerinde probleme sebep olabilir.

- Eğer primary key kullanarak tek bir satır çekiyorsak phantom read olma olasılığı ortandan kalkar.

Örnek

Bir örnek burada

Çözüm

1. "Select For Update" Kullanılabilir. "Select For Update" dokunduğu her satırı kilitleyeceği için, arkadan gelen transaction beklemek zorunda kalır.

2. Serializable Isolation Level kullanılabilir.

ACID - Repeatable Read Seviyesi Nedir ? - Benim Dokunduğum Satırı Kilitler Gibi Düşünülebilir

Giriş

Şeklen şöyle. Üçüncü satırda Repeatable Read görülebilir.

SQL Server için şeklen şöyle

Açıklaması şöyle

It ensures that the same select query will always return the same result, no matter how many times it is executed, even if some other concurrent transactions have committed new changes that satisfy the query.

Transaction içinde kullanılan tüm verinin her seferinde aynı değeri taşıyacak şekilde okunması garanti edilir. 

- Dirty Read problemini engeller. 

- Lost Update problemini engeller.

- Phantom Read problemini engellemez. Benim dokunmadığım satırlar kilitlenmediği için bunlar değiştirilebilir veya yeni satır eklenebilir

- Phantom satır kapsamında Write skew problemini engellemez.

Şeklen şöyle. Burada Transaction 1 başlıyor ve 2 numaralı satırı okuyor, ancak 3 numaralı satıra dokunmuyor. Transaction 2 ise 3 numaralı satırı değiştirip commit'liyor. Transaciton 2 3 numaralı satırı okusa bile eski veriyi görüyor.

Açıklaması şöyle

REPEATABLE READ isolation level guarantees that transactions see the committed view of the database snapshot taken by the first read. Unlike READ COMMITTED isolation level, data that is read at one point in the transaction is the same as data that is read at another point in the transaction.

... The concurrent transactions see the committed view of the snapshot taken within each transaction. Transaction 1 gets charlie, not chris, because Transaction 1 began before Transaction 2. To put it another way, the two concurrent transactions are performed in serial.

Note that the snapshot is taken by the table, not by the row or database. A first read to the table creates a new table snapshot. As to the above diagram, Transaction 1 creates a snapshot of the users table when issuing SELECT name FROM users WHERE id = 2. Once the transaction ends, the snapshot is deleted.

1. Lock Çeşitleri Nedir?

Açıklaması şöyle. Lock çeşitleri yanında lock'ın ne zaman bırakıldığı da önemlidir.

What are the locks in RDBMS?

Shared read lock: When a single row is read, a read lock is held. This allows another transaction from another thread to have a read lock on the same record — but no other transaction from another thread can impose a write lock to update it.

Update lock: When an update lock is held, other threads can only acquire a shared read lock — not update nor an exclusive write lock.

Exclusive write lock: An update lock is promoted to a write lock. I think this promotion is only possible when only one lock, which is this update lock (no other shared read lock), is locking this row. When a write lock holds a row, no other transaction from any other thread can hold any lock on that row.

-Shared read lock aynı anda okumayı engellemez, başkası yazamaz

-Update lock aynı anda okumayı engellemez, başkası yazamaz. Update lock, Exclusive Lock'a yükseltilebilir.

-Exclusive Lock başka herhangi bir lock'a müsaade etmez

2. Repeatable Read Gerçekleştirimi

Repeatable read gerçekleştirimi için iki tane yöntem var. Bunlar şöyle

1. With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 

2. With transaction 1 as Repeatable Read, other transactions can update a row, but transaction 1 does not take into account those changes.

Çoğu veri tabanı 1. yöntemi seçiyor. 

1. Yöntem şu anlama gelir Kullanılan Lock - Okumayı Engellemez Ama Yazmayı Engeller

Repeatable Read "Update Lock" kullanır. Bu lock read committed seviyesinin aksine transaction bitmeden bırakılmaz. Yani bu lock çeşidi ile başkasının benim satırıma yazması engellenir.

SQLServer - 1. Yöntem

Açıklaması şöyle

With transaction 1 as REPEATABLE READ, you cannot update a row in transaction 2 after you selected it in transaction 1.

MYSQL - 2. Yöntem

Açıklaması şöyle

While many database systems will actually behave like your first version, for MySQL, version 2 is the expected behaviour, see the documentation about repeatable read:

Bu yüzden "select ... for update" kullanılıyor. Açıklaması şöyle

In MySQL, if you want to block another transaction from updating the rows in repeatable read, you need to lock them by e.g. using select ... for update. A simple select will not place a lock unless you are in serializable isolation mode.

PostgreSQL - 1. Yöntem

Repetable Read yazısına taşıdım.

Repeatable Read Kullanım Senaryosu Örnekleri

Örnek

Eğer bir mali rapor hazırlıyorsam ve kârı iki farklı yerde gösteriyorsam iki tane SUM cümlesi çalıştırırım. Eğer repeatable read kullanmazsam okuduğum veri güncellenebilir ve iki farklı sonuç elde ederim.

3. Lost Update Çözümü Nasıldır

Örnek

Lost Update için en açıklayıcı senaryo aşağıdaki gibi. İki thread'in aynı anda banka hesabındaki paradan 100 TL çekmeye çalıştığını düşünelim.

•     funds == 100; amount == 100
•     thread A enters withdraw / transaction A starts
•     thread A executes isEnoughFunds which evaluates to true
•     thread B enters withdraw / transaction B starts
•     thread B executes isEnoughFunds which evaluates to true
•     thread A executes decreaseFunds / thread A locks db record
•     thread B waits for thread A to commit transaction and release write lock
•     thread A exits withdraw / transaction A commits
•     thread B executes decreaseFunds / thread B locks db record
•     thread B exits withdraw / transaction B commits
•     funds == -100

Yukarıdaki örnekte para olmadığı halde 100 TL çekilebildi. Eğer Repeatable read kullanırsak B thread'i A'nın işini bitirmesini beklemek zorunda kalır, çünkü satırı güncelleme niyeti ile kilitlemek istediğini bildirecek ancak kayıt zaten A tarafından kilitlendiği için bekleyecektir. Kilidi alınca da ilk transaction commit yaptığı için çekme işlemi gerçekleşmez. Açıklaması şöyle

If two transactions attempt to modify the same record, the second transaction will wait for the first one to either commit or rollback. If the first transaction commits, then the second one must be aborted to prevent lost updates.

4. Select İşlemi - Phantom Read Problemi

Bu problem halen karşımıza çıkabilir

Örnek

Şöyledir

T1 begins

T1Queries select * from Tbl where X>100 → 3 rows

T2 begins

T2 Inserts 1 additional row with X=150.

T2 Commits.

T1 Queries select * from Tbl where X>100 → 4rows [Phantom Read].

5. Diğer Konular

Spring

Spring ile bu isolation level ile transaction başlatmak için aşağıdaki kod kullanılabilir.

@Transactional (isolation=Isolation.REPEATABLE_READ)

Oracle

Oracle bu isolation seviyesini desteklemez. Açıklaması şöyle.

REPEATABLE READ; Oracle does not normally support this isolation level, except as provided by SERIALIZABLE.

Vitess

Çoğu sharded veri tabanı gibi "Cross Shard" işlemlerde Repeatable Read seviyesi desteklenmez

ACID

ACID Nedir?
Bir relational (ilişkisel) veri tabanı ACID özelliğini sağlamalı. Yani Atomicity (ya hep ya hiç), Consistency (Veri bütünlüğü yani integrity kurallarını ihlal etmeyen), Isolation ve Durability (Transaction bitince veri diske yazılır) özelliklerini sağlamalı.

ACID Kelimesi Nereden Geliyor

Designing Data Intensive Applications kitabındaki açıklama şöyle

It was coined in 1983 by Theo Härder and Andreas Reuter in an effort to establish precise terminology for fault-tolerance mechanisms in databases. However, in practice, one database’s implementation of ACID does not equal another’s implementation. For example, as we shall see, there is a lot of ambiguity around the meaning of isolation. The high-level idea is sound, but the devil is in the details. Today, when a system claims to be “ACID compliant,” it’s unclear what guarantees you can actually expect. ACID has unfortunately become mostly a marketing term.

BASE  Nedir

BASE Properties For Distributed Database Transactions yazısına taşıdım.

Atomicity Nedir (Abortability)

Designing Data Intensive Applications kitabındaki açıklama şöyle. Yani transaction abort edilirse hiç bir değişiklik kaydedilmez.

In general, atomic refers to something that cannot be broken down into smaller parts. The word means similar but subtly different things in different branches of computing. For example, in multi-threaded programming, if one thread executes an atomic operation, that means there is no way that another thread could see the half-finished result of the operation. The system can only be in the state it was before the operation or after the operation, not something in between. 

By contrast, in the context of ACID, atomicity is not about concurrency. It does not describe what happens if several processes try to access the same data at the same time, because that is covered under the letter I, for isolation (see “Isolation” on page 225).

Rather, ACID atomicity describes what happens if a client wants to make several writes, but a fault occurs after some of the writes have been processed—for example, a process crashes, a network connection is interrupted, a disk becomes full, or some integrity constraint is violated. If the writes are grouped together into an atomic transaction, and the transaction cannot be completed (committed) due to a fault, then the transaction is aborted and the database must discard or undo any writes it has made so far in that transaction.

Without atomicity, if an error occurs partway through making multiple changes, it’s difficult to know which changes have taken effect and which haven’t. The application could try again, but that risks making the same change twice, leading to duplicate or incorrect data. Atomicity simplifies this problem: if a transaction was aborted, the application can be sure that it didn’t change anything, so it can safely be retried.

The ability to abort a transaction on error and have all writes from that transaction discarded is the defining feature of ACID atomicity. Perhaps abortability would have been a better term than atomicity, but we will stick with atomicity since that’s the usual word.

ConsistencyNedir

Designing Data Intensive Applications kitabındaki açıklama şöyle.

The idea of ACID consistency is that you have certain statements about your data (invariants) that must always be true—for example, in an accounting system, credits and debits across all accounts must always be balanced. If a transaction starts with a database that is valid according to these invariants, and any writes during the transaction preserve the validity, then you can be sure that the invariants are always satisfied.

However, this idea of consistency depends on the application’s notion of invariants, and it’s the application’s responsibility to define its transactions correctly so that they preserve consistency. This is not something that the database can guarantee: if you write bad data that violates your invariants, the database can’t stop you. (Some specific kinds of invariants can be checked by the database, for example using foreign key constraints or uniqueness constraints. However, in general, the application defines what data is valid or invalid—the database only stores it.)

Atomicity, isolation, and durability are properties of the database, whereas consistency (in the ACID sense) is a property of the application. The application may rely on the database’s atomicity and isolation properties in order to achieve consistency, but it’s not up to the database alone. Thus, the letter C doesn’t really belong in ACID.

Isolation Nedir

ACID - Isolation yazısına taşıdım

Durability Nedir

Designing Data Intensive Applications kitabındaki açıklama şöyle.

The purpose of a database system is to provide a safe place where data can be stored without fear of losing it. Durability is the promise that once a transaction has committed successfully, any data it has written will not be forgotten, even if there is a hardware fault or the database crashes.

In a single-node database, durability typically means that the data has been written to nonvolatile storage such as a hard drive or SSD. It usually also involves a write-ahead log or similar (see “Making B-trees reliable” on page 82), which allows recovery in the event that the data structures on disk are corrupted. In a replicated database, durability may mean that the data has been successfully copied to some number of nodes. In order to provide a durability guarantee, a database must wait until these writes or replications are complete before reporting a transaction as successfully committed.

As discussed in “Reliability” on page 6, perfect durability does not exist: if all your hard disks and all your backups are destroyed at the same time, there’s obviously nothing your database can do to save you.