MySQL and MS SQL Server

Recently, MySQL had an article comparing MySQL and SQL Server at  http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

There is one clarification I would like to make to this article.  The article states that MS SQL Server has row locking, and while this is true, MS SQL Server doesn’t always use row locking and often will resort to page locking.  A page is 8k of data, so, in effect, many rows are locked at the same time even if only one row in that page is being updated.  At high data throughputs, this can lead to serious lock contention and to dead locks, even though the two processes with the contention or dead  lock are updating different rows.   Stated differently, no matter how well you order your multi-row update processes in MS SQL Server you should expect deadlocks. 

There is a way to turn off page locks on an index, but a index can’t be reorganized if page locks are off as index reorganization uses page locks.   Alternatively, one can add a row lock hints to each SQL statement, but this becomes tiresome fast.  The general approach to this problem is to keep each transaction small, smaller than you perhaps want, which reduces but doesn’t eliminate the page lock contention and dead locks.  

Easy to use with sensible defaults, MS SQL Server is a great database;  but page locking is a relic from the past when row locks consumed too much of that rare  memory.  In the 64 bit processor world, memory is too plentiful to worry about how much memory row locks take and page locks hurt scalability.   As more recent database design, the MySQL model of only using row locks makes far more sense.