Tuesday, November 07, 2006

DB Locking Vs Versioning

How a database server handles concurrency is a great deal of interest to any application. Historically SQL Server has been lagging behind DBMSs like Oracle for their rather legacy way of handling this with multiple levels of locking mechanisms. By comparison most other DBMS adapt a versioning mechanism. You can read about both in this excellent article.

But SQL 2005 has put a step forward and introduced versioning. To check out all the levels of versioning refer MSDN. What we have used in our project is 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED' which is the lowest level of versioning giving the maximum performance and the maximum risk of fetching invalidated data in to the application. We are working on a reporting application where the changes to the data is much less frequent compared to the reporting of the data and this was very much acceptable.

Post a Comment