SQL Server Locks

Introduction

Relational databases like Microsoft's SQL Server use locks to prevent multiple users from making conflicting modifications to a set of data: when a set of data is locked by a user, no other users can modify that same set of data until the first user finishes modifying the data and relinquishes the lock. There are exceptions, but let's not go there.

Some databases - SQL Server included - use locks to prevent users from seeing uncommitted modifications. In these systems, if UserA is modifying some set of data, UserB and all the rest of the users must wait until UserA is done modifying that data before UserB can get a shot at even reading the data, let alone modifying it.

Databases place locks at all levels of their physical hierarchies: rows, pages (typically a few KB of rows), extents (typically a few pages), entire tables, and entire databases. Some databases (Oracle, others?) only use fine-grained row locks, others don't do row locks at all and only allow rough-grained page, extent, table, and database locks. Most databases - SQL Server included - support row locking, but often use rough-grained locks. This is because lock management is a royal pain. Locks aren't small or simple entities, so if you only do row-level locking, you can get yourself into a world of pain: a million-row update can easily swamp memory and be a bear to manage.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Computer science education cannot make anybody an expert programmer any more than studying brushes and pigment can make somebody an expert painter” - Eric Raymond