If you’ve used Oracle (or listened to the pitch) much, you’ve likely heard the mantra “readers don’t block writers, writers don’t block readers”. This has been true for Oracle for a very long time. It’s implemented with their undo log (also used for rollbacks). To get an idea of how it’s implemented, check out this article on what a update costs in Oracle.
In SQL Server 2000, queries (select statements) took a shared lock on the relevant rows (we’ll ignore lock escalation for now), and updates took exclusive locks. This makes them less overhead, but more contentious (readers blocked writers, writers blocked readers – writers will always block writers, of course, and readers never block readers)
While that’s still the default situation for SQL Server 2005, I’m happy to see that there’s a new “Snapshot” isolation level in 2005 that gives you the the choice to have this “readers don’t block writers and writers don’t block readers” effect. Similar to the undo log, the older versions of the data are kept around, but in the SQL Server 2005 case, this older data is kept around in the tempdb. This has some advantages in terms of easier maintenance, but it can be considered a similar mechanism.
What’s great is that the admin now has a choice. Oracle’s never given you the choice (which is fine, it’s how they chose their architecture), but now SQL Server gives you that choice. In many cases (lots of updates, long-running transactions, etc.), this will be an extremely valuable feature as the previous choice was usually to NOLOCK the long-running queries so they wouldn’t take the shared lock and block writers.
If you’re interested, you should definitely check out this article on the snapshot isolation level in sql server 2005.
