CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
dandcg
dandcg

Reputation: 442

Multiple Update Statements / Transient Tables / Avoiding Locking

I’m looking for some guidance as to the best approach for processing data within a database using SQL Server.

Basically I have a series of transient tables which are loaded with data and then series of update statements are executed against them. Multiple instances of these series of update statements run against the same table, but each instance only updates row with a specific identifier (lineage). I’m looking to be able to confidently avoid any locking issues / deadlocks when instances are run concurrently.

The two thought I had on this are as follows:

  1. All updates statements contain the hint rowlock, so that page locks will not occur which cover different lineages.
  2. Change the isolation level - dirty reads would never actually occur. Can anybody shed any light on which may be the best approach to this situation?

Upvotes: 1

Views: 1640

Answers (1)

StuartLC
StuartLC

Reputation: 107357

Assuming your table looks something like:

CREATE TABLE MyTable
( 
   ID INT IDENTITY(1,1),
   LineageId INT NOT NULL, -- FK to Lineage
   Col1 ...
   Col2 ...

   PRIMARY KEY CLUSTERED(ID),
   FOREIGN KEY LineageId REFERENCES Lineage(LineageId)
)

  1. Recommend that you change the clustered index on your table to LineageId (but see later). This should guarantee that the fewest pages are updated if SQL does escalate your row lock to a page lock. The downside of this is that LineageId isn't unique, so SQL will add a uniquifier. So, before changing the clustering, if your PK is incrementing and if you find that almost all rows in your table are already contiguously arranged by LineageId (e.g. if data is inserted into this table by a single threaded batch job), then clustering by an incrementing surrogate pkey would be preferable as this avoids the uniquifier (better narrowness).

  2. The ROWLOCK hint may help, although this is no guarantee. http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/60238304-04e8-4f98-84d1-3ddf1ed786a9

  3. Setting Isolation Level to READ UNCOMMITTED or SNAPSHOT or using the (NOLOCK) hint would need to be done to the other concurrent queries to to avoid them from blocking during your LineageId updates to your table. This would be a drastic measure, and uncommitted reads can lead to integrity problems.

Upvotes: 1

Related Questions