MS SQL Locking and Concurrency

Posted On 2008-01-15 by FortyPoundHead
Tags: Tip 
Views: 1841

SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity. In contrast, with Oracle, the rule is "readers don't block writers and writers don't block readers."

This is possible without compromising data integrity because Oracle will dynamically re-create a read-consistent image for a reader of any requested data that has been changed but not yet committed. In other words, the reader will see the data as it was before the writer began changing it (until the writer commits). SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment.

About the Author

FortyPoundHead has posted a total of 1974 articles.

Comments On This Post

No comments on this post yet!

Do you have a thought relating to this post? You can post your comment here. If you have an unrelated question, you can use the Q&A section to ask it.

Or you can drop a note to the administrators if you're not sure where you should post.

Your IP address is:

Before you can post, you need to prove you are human. If you log in, this test goes away.

Code Links