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.

  • 4
    You have hit on one of the fundamental differences between Oracle and MSSQL. In fact I believe this behavior of Oracle is unique to Oracle among all RDBMS products, and many would argue that this is a key reason they consider Oracle to be a superior RDBMS. Instead of trying to make a judgement on how you think it "should" be, or trying to make one behave like the other, it's better to simply adapt best practice for whatever rdbms you are working with. "When in Rome . . . " – EdStevens Apr 28 '19 at 17:45
  • 3
    @EdStevens What's unique to Oracle? Postgressql uses MVCC and Sql Server has had snapshot isolation available as an optional setting since 2005. – Martin Smith Apr 28 '19 at 22:11
  • @EdStevens Not forgetting Firebird! – Vérace May 2 '19 at 17:43

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.

| improve this answer | |
  • 4
    Adding that READ_COMMITTED_SNAPSHOT is on by default in Azure SQL Database. The default behavior in on-prem SQL versions (locking instead of row versioning) is for backwards compatibility, IMHO. – Dan Guzman Apr 28 '19 at 21:05
  • 2
    The licensing model and cost of the product is a pure commercial decision and has nothing to do with the technical implementation. – Colin 't Hart Apr 29 '19 at 6:35
  • 1
    Sufficient to say it was once one of main justifications of Oracle's pricing, but it's history now. – kubanczyk Apr 29 '19 at 7:45
  • 1
    Thanks for your valuable comments, I have edited my answer and removed pricing comparison. – Learning_DBAdmin Apr 29 '19 at 9:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.