Menu
What is the difference between serializable and repeatable read isolation level? Can anyone please give a simple example.
246
Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.
In READ COMMITTED mode, the second time Query 1 was executed, the age would have changed. Under multiversion concurrency control, at the SERIALIZABLE isolation level, both SELECT queries see a snapshot of the database taken at the start of Transaction 1. Therefore, they return the same data.
Repeatable Read Isolation Level. The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.
The SERIALIZABLE isolation level is the highest isolation level based on pessimistic concurrency control where transactions are completely isolated from one another. The ANSI/ISO standard SQL 92 covers the following read phenomena when one transaction reads data, which is changed by second transaction: dirty reads.
Summary:
In a Repeatable Read isolation level, new rows can be inserted into the dataset.
In a Serializable isolation level, all the rows are locked for the duration of the transaction, no insert, update or delete is allowed.
Please refer to these for examples and explanations.
Original links are broken — Wayback Machine links are from the same day and time.
Isolation Level — serializable
http://beyondrelational.com/modules/2/blogs/28/posts/10467/sql-server-transaction-isolation-level-serializable.aspx
Wayback Machine SQL Server Transaction Level Serializable
Isolation Level — repeatable-read
http://beyondrelational.com/modules/2/blogs/28/posts/10466/sql-server-transaction-isolation-level-repeatable-read.aspx
Wayback Machine SQL Server Transaction Level Repeatable Read
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
