SQL SERVER – 2005 – Mechanisms to Ensure Integrity and Consistency of Databases – Locking and Row Versioning

Today I was going through Book On Line while researching something, I come across one interesting small article about two mechanisms to ensure integrity and consistency of databases – 1) Locking 2) Row Versioning

Let us see their definition from Book Online Itself.

Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. Each transaction frees its locks when it no longer has a dependency on the locked resources.

Row versioning
When a row versioning-based isolation level is enabled, the Database Engine maintains versions of each row that is modified. Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

You can read more about this subject here.

Locking in the Database Engine
Customizing Locking and Row Versioning
Isolation Levels in the Database Engine
Managing Concurrent Data Access
Row Versioning-based Isolation Levels in the Database Engine

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

Exit mobile version