Lock in SQL Server and Oracle [duplicate]

Question

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.

Answer 1

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to turn on the Read Committed Snapshot database option, basically this option takes a snapshot of your data before modifications and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. Another isolation level Read Uncommitted will in this situation return data but with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.

There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.

You may read more about isolation level at Microsoft site at this link.

There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

1. Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

2. Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/

3. Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/

4. Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels

5. Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar (unable to find now) where he has clearly explained the difference between Oracle and SQL server you have mentioned. For this reason, Oracle used to cost lot more earlier than SQL server per core however now the case/scenario is different. One more thing to add here - In azure, RCSI is default behavior.

I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.