I’ve recently been talking to a new chief architect who has moved into our company. This person has been working in IT a long time and has some fixed opinions over what is good and what is bad practice. It’s not my place to agree or disagree as this individual has found a groove that works for them through experience and they stick with it.
However, they appear to have an inherent dislike for anything Microsoft.
They have repeated said that “SQL Server only does page locks whereas Postgres et al do row locks”. Ergo, SQL Server isn’t as efficient as it’s competitors and should be replaced (with Postgres). We are also moving from Windows/Azure to Linux/AWS because “Azure is bad and AWS is better” (though this is a different discussion).
I have challenged them on this as SQL Server clearly does row level locking, migrating to page and table level locks if necessary.
The explanation was odd, but I’m not going to discount it without comments from the community.
They said that (and I’m paraphrasing), in their testing, although SQL Server does indeed do row level locks, the issue is how it handles disk writes. At the disk level the writes are at page level, therefore when writing data, SQL is in fact hamstrung by the fact that all writes are page locks, regardless of how SQL manages it’s own locking strategies.
This does sound plausible – disks are read/written in blocks and a block could cover multiple table rows.
I then asked why does Postgres or any other database not have this problem. The answer was very vague and something to do with “Tuples”…. I have no idea what this meant and I wasn’t about to challenge my Chief Architect.
Is this a linux vs windows thing? Linux is more efficient in disk writes?
Even if true, would this be something that would impact SQL Server when compared to Postgres, Oracle, MySQL etc… I would have thought it would have strategies in place to handle this… delayed writes etc?