[Database] Isolation level

Rex Chiang

·

Follow

1 min read

·

Jun 2, 2023

--

Listen

Share

Read uncommitted

• A transaction can read the data that another transaction hasn’t been committed.
• All of the race conditions can occur.

Read committed

• A transaction can only read the data that has been committed.
• Can avoid the race condition of dirty read.

Repeatable read

• A transaction can read the data consistently.
• Can avoid the race condition of dirty read and read skew (Non-repeatable read).
• Cooperate with Share/Exclusive locks to block the data can avoid the race condition of dirty read, read skew, lost update.
• Cooperate with snapshot can avoid the race condition of dirty read, read skew, phantom read.

Serializable

• Multiple transactions are executed serially.
• Can avoid all of the race conditions.
• Execute transactions serially, but totally lost the efficiency for concurrency.
• Cooperate with two-phase Lock (Share/Exclusive/Range locks) can improve a little bit of the efficiency for concurrency, but need to manage lot of behavior of locks and dead lock situation.
• Cooperate with SSI (Serializable Snapshot Isolation) can keep the efficiency for concurrency, but need to retry lot of times of the transaction in highly race condition.