I was talking with a friend about how a database works and she asked me to explain transaction isolation levels to her. So let’s try…
My first intent was to create an analogy with something simple, for example, a book. Two people trying to read/write on the same book. They can read at the same time and it won’t cause any conflict. (shared lock). However, if one tries to write while the other one is reading, depending on the level of isolation that is being used, one of them may be blocked.
Imagine the situation where a person A finished writing page 1 of a book, so person B can read that page. The transaction was open when person A started writing and closed when page 1 was finished. This situation is called READ COMMITTED, because the person B can only read the page 1 after the person A finish writing and closes the transaction. This is SQL Server default isolation level.
Another situation is when the person B is reading the page 1 while the person A is writing, it is called READ UNCOMMITTED. The person A didn’t close the transaction and while the person B is reading something may be changed by person A. If the person B tries to read it again, it might be different because the transaction is still opened for person A.
Next case is when the person B starts to read the page first and person A tries to change what the person B is reading. The isolation level REPEATABLE READ will maintain what the person B is reading until the transaction is finished. That means if the person B tries to read again he will see the same data and nobody can change it. In this case person A will still be able to read and add new pages.
The last level is SERIALIZABLE and this scenario the person B is reading and the person A tries to write a new page, but in this case, the person A will only be able to insert/delete/update any page after person B closes his transaction. Both person A and B can read because the shared lock is compatible. It’s similar to the REPEATABLE READ, however the difference here is that you can’t do any changes in the book.