Fdzfgy

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.

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 set the isolation level as Read Committed Snapshot Isolation, basically this isolation level takes snapshot of your data and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. We have another isolation Read Uncommitted, this will also behave like Oracle 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 you have been talking about between Oracle and SQL server. For this reason, Oracle costs much more than SQL server per core.

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.