3 ms·

Correct me if I am wrong, MS Sql server isn't true MVCC database and there is some support and it is not default, you have to set READ_COMMITTED_SNAPSHOT ON for

Correct me if I am wrong, MS Sql server isn't true MVCC database and there is some support and it is not default, you have to set READ_COMMITTED_SNAPSHOT ON for the database. They implemented it in 2005. Most microsoft shops don't bother to develop their softwares for this, they are using archaic locking transactions. I've seen many Sql Server deployments keep choking on locking transactions and and people are buyin bigger servers, more Windows and Sql Server licences for each cpu/core to deal with their choked database server. So while Postgresql's defaults are all about technical concerns, Sql server's defaults are just convenient for Microsoft sales team.

kbenson 10y ago
I can't comment on whether this is correct, but on the MSSQL box I integrate with, lock contention and the server automatically killing deadlocked queries is a regular occurrence. Like 50+ times a day regular.
AaronFriel 10y ago
Both are true! MS SQL Server is a "true MVCC" database (no-true Scotsman?) and you are correct. SQL Server supports snapshot/MVCC isolation for transactions, but it must be enabled. They are determined to maintain 20 years of backward compatibility, and while I think that's wrong, they leave snapshot isolation disabled by default. https://msdn.microsoft.com/en-us/library/ms173763.aspx https://msdn.microsoft.com/en-us/library/ms173763.aspx
- serguzest 10y ago
  It uses tempdb for tracking versions, it is not true mvcc in the engine.
  - br1 10y ago
    That's an internal detail. It's much cleaner than postgresql that leaves old tuples in disk and has to vacuum them later.
  - greggyb 10y ago
    It also offers an optimized in-memory transactional engine that is lockless and compiles stored procedures to native code.