Re:Microsoft SQL Server 2016 Express
by AaronFriel on Wednesday October 12, 2016 @11:51AM
(#12693929)
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.
Re:Microsoft SQL Server 2016 Express
by serguzest on Wednesday October 12, 2016 @12:51PM
(#12694470)
It uses tempdb for tracking versions, it is not true mvcc in the engine.
That's an internal detail. It's much cleaner than postgresql that leaves old tuples in disk and has to vacuum them later.
It also offers an optimized in-memory transactional engine that is lockless and compiles stored procedures to native code.
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.
So while Postgresql's defaults are all about technical concerns, Sql server's defaults are just convenient for Microsoft sales team.