What is the difference between serializable and repeatable read isolation level?

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

Repeatable read prevents only non-repeatable read (so you can read the same data in the same transaction without fear of someone changing it - even though it's a rare need for doing it).

Serializable prevents both non-repeatable read and phantom rows (so you can't even INSERT data). That means you can READ and WRITE (SELECT, UPDATE) rows that are not included with serializable transaction, but you can't DELETE OR INSERT rows on TABLE level.

Both repeatable read and serializable are very strict and there is not always need for them!