Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the transaction is either committed or rolled back (as far as I understood from When should you use “with (nolock)”).
At the same time, other RDBMSes, such as PostgreSQL, allow you to read values from a row that has an active writing transaction on it. They would just give you the values that were there prior to the writing. Particularly that seems to be the behaviour with PostgreSQL MVCC (as I have learned from the Introduction to Chapter 13. Concurrency Control).
And I have this burning question. Why is there a possibility for deadlocks in SQL Server while in other RDBMSes you just get the old value before the new one is written?
NOTE: I am asking because I may have understood incorrectly.
Best Answer
SQL Server by default operates under
Read Committedisolation.If you want SQL Server to behave more like other implementations, what you're looking for is either
Read Committed Snapshot Isolation, orSnapshot Isolation.You can read more about them here.
Full disclosure, I am a Brent Ozar Unlimited employee
Using
NOLOCKorRead Uncommittedis generally frowned upon for serious production use, as it can allow dirty reads and other unintended artifacts while modification queries are in progress.