Library tutorials & articles
SQL Server Locks
Introduction
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.
Related articles
Related discussion
-
how to find second largest number through a query in sql
by om_java2009 (29 replies)
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
Permutations and combinations of multiple return codes
by actuszeus (1 replies)
-
help needed on tree level sql?
by kareen (0 replies)
-
dtPicker date format query
by konikula (1 replies)
Related podcasts
-
Stack Overflow: Podcast #28
This is the twenty-eighth episode of the StackOverflow podcast, where Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. Jeff met the inestimable Joey DeVilla aka Accordion Guy...
To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link: http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
I've implemented the [WITH (NOLOCK)] hint and it's been a reasonable success. The only additional note of caution I would add is that we have one table that is occasionally updated (batch inserts).
The problem is that the data is inserted and then the index is updated. If the index is read after the data has been updated but before the index is updated then this can cause the following error:
Number = -2147217900, Description = Could not continue scan with NOLOCK due to data movement., Source = Microsoft OLE DB Provider for SQL Server, SQLState = 42000, Native Error = 601.
Great.
This little hint with those little word (nolock) saved my life - and the life of our application. (http://www.gpsoverip.de)
Thank you VERY much ...
You say (with regard to locking): "For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. But I think you'll find that for better than 90% of your application, it would not be that big of a deal if a user (or even intermediate code) saw an uncommitted modification"
What do you mean? If data is being interrogated, then the returned result set MUST be consistent (ACID).
Seeing uncommitted code is a no-no, whether Financial, denormalized or whatever.
Please contribute your suggestion in relation with various issues related with log file management in SQL server. Such as
1. Methods to Shrinking the log file.
2. Recover the database form Log file
3. Recovery mode etc
This thread is for discussions of SQL Server Locks.