We help IT Professionals succeed at work.

Database Table Locks

dalebrumbaugh
on
288 Views
Last Modified: 2011-10-03
We are currently trying to "catch" data from another program's database before it is deleted.  The program is a document management system, so when a user deletes a "document", all records pertaining to that document are deleted in the database.  Our goal is to capture that data before it is deleted.  To do this, we have set up a trigger on the table that is updated when documents are deleted.  This trigger will call the following sp and "capture" all the data related to the document and put it into a table.  

When only one instance of the document management software is running, the sp runs fine and captures the data.  However, if two instances or more of the dm software are running, the program logs that deadlocks were created on the database and fails to delete the documents.  

The stored procedure is causing the deadlocks, but we're not sure exactly where and how.  All "select" statements are using "with (nolock)".  I know that update statements might be doing it as well as the insert statements.  How do we keep these statements from locking the tables?  Is there a way that the sp can check to see if an instance of the sp is already running, then wait until the instance is finished?


CREATE PROCEDURE get_docinfo
/*      @StoreID int,
      @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
      

/*  Update each record with a tocid */
      DECLARE @StoreID int
      DECLARE @VolumeID int      
      DECLARE @iTocID nvarchar(50)            
      
      DECLARE records CURSOR LOCAL for
      SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null
      OPEN records
      FETCH NEXT FROM records
      into @StoreID, @VolumeID
      print @storeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                  update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
            FETCH NEXT FROM records
            into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
      print 'Beginning record loop'
      DECLARE @tocstring nvarchar(500)
      DECLARE toc_cursor CURSOR LOCAL FOR
      SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null
      OPEN toc_cursor
      FETCH NEXT FROM toc_cursor
      into @itocid, @storeid, @volumeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            /* Get LF Path*/
            declare @counter int
            declare @mystring varchar(250)
            declare @objectname as varchar(250)
            declare @parent int
            set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
            set @counter = 0
            set @mystring = ''
            set @objectname = ''
      
            while @objectname <> 'ROOT FOLDER'
            begin
                    set @counter = @counter + 1
                    set @objectname = (select name from toc with (nolock) where tocid = @parent)
                    set @mystring =  @objectname + '\' + @mystring
                    set @parent = (select parentid from toc with (nolock) where tocid = @parent)
            end
            print @mystring
            print @storeid
                       update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

            /*Get Volume Information*/
            declare @IsImageFixed varchar(10)
            declare @FixedPath varchar(50)
            declare @RemovablePath varchar(50)
            declare volume_cursor cursor for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
            FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                   Toc ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol ON Toc.VolumeId = Vol.VolumeId)
            OPEN volume_cursor
            FETCH NEXT FROM volume_cursor
            into @IsImageFixed, @FixedPath, @RemovablePath
            close volume_cursor
            deallocate volume_cursor

            update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

            /*Get Document Name*/
            declare @DocumentName varchar(250)
            set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
            update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

            /*Get Template Name*/
            declare @TemplateName varchar(100)
            set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

            /*Get Table Name */
            declare @TableName varchar(50)
            set @TableName = (SELECT  top 1   Tstr.TableName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

            /*Get PageID, PageNum*/
            declare @PageID varchar(50)
            declare @PageNum varchar(10)

            declare page_cursor cursor for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid)
            OPEN page_cursor
            FETCH NEXT FROM page_cursor
            into @PageID, @PageNum
            close page_cursor
            deallocate page_cursor
            update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

            /*Get Template Data*/
            declare @stmt as varchar(4000),
            @col as varchar(100)
            declare c cursor for
            select column_name from information_schema.columns with(nolock) where table_name = @TableName
            open c
            fetch next from c into @col
            set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
            while @@fetch_status = 0
            begin
                 set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                                fetch next from c into @col

                 if @@fetch_status = 0
                 set @stmt = @stmt + '+ '
            end
            set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
            close c
            deallocate c
            
            exec (@stmt)


              FETCH NEXT  FROM toc_Cursor
              into @itocid, @storeid, @volumeid
      END
      CLOSE toc_Cursor
      DEALLOCATE toc_Cursor
      print 'Exited toc_cursor loop'
      /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

      truncate table deletedpagecache
GO
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:

Author

Commented:
All of the cursors are select  with (nolock) statements, though.  This should only be causing performance issues, not locking issues.  Does the table lock while a select command is being issued?  I'm thinking the update statements are causing this and different instances of the sp are being ran at the same time.  I think I need a way for the sp to wait for all instances of the sp to finish???  Of course, I don't know all the ins and outs of sql and t-sql, either.   Please advise.
Top Expert 2006

Commented:
hi,

Change all the cursur defination to be for Read Only
Change all the read only tables to be with nolock
Create Necessary Indexes


CREATE PROCEDURE get_docinfo
/*     @StoreID int,
     @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData with (nolock) ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
     

/*  Update each record with a tocid */
     DECLARE @StoreID int
     DECLARE @VolumeID int    
     DECLARE @iTocID nvarchar(50)          
     
     DECLARE records CURSOR LOCAL for
     SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null for read only
     OPEN records
     FETCH NEXT FROM records
     into @StoreID, @VolumeID
     print @storeid
     WHILE @@FETCH_STATUS = 0
     BEGIN
          set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                 update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
          FETCH NEXT FROM records
          into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
     print 'Beginning record loop'
     DECLARE @tocstring nvarchar(500)
     DECLARE toc_cursor CURSOR LOCAL FOR
     SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null for read only
     OPEN toc_cursor
     FETCH NEXT FROM toc_cursor
     into @itocid, @storeid, @volumeid
     WHILE @@FETCH_STATUS = 0
     BEGIN
          /* Get LF Path*/
          declare @counter int
          declare @mystring varchar(250)
          declare @objectname as varchar(250)
          declare @parent int
          set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
          set @counter = 0
          set @mystring = ''
          set @objectname = ''
     
          while @objectname <> 'ROOT FOLDER'
          begin
                 set @counter = @counter + 1
                 set @objectname = (select name from toc with (nolock) where tocid = @parent)
                 set @mystring =  @objectname + '\' + @mystring
                 set @parent = (select parentid from toc with (nolock) where tocid = @parent)
          end
          print @mystring
          print @storeid
                     update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

          /*Get Volume Information*/
          declare @IsImageFixed varchar(10)
          declare @FixedPath varchar(50)
          declare @RemovablePath varchar(50)
          declare volume_cursor cursor for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
          FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                  Toc with (nolock) ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol with (nolock) ON Toc.VolumeId = Vol.VolumeId) for read only
          OPEN volume_cursor
          FETCH NEXT FROM volume_cursor
          into @IsImageFixed, @FixedPath, @RemovablePath
          close volume_cursor
          deallocate volume_cursor

          update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

          /*Get Document Name*/
          declare @DocumentName varchar(250)
          set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
          update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

          /*Get Template Name*/
          declare @TemplateName varchar(100)
          set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
          FROM         DeletedPageCacheData with(nolock) INNER JOIN
          Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
          Tstr  with (nolock)ON Toc.TemplateId = Tstr.TemplateId
          WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
          update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

          /*Get Table Name */
          declare @TableName varchar(50)
          set @TableName = (SELECT  top 1   Tstr.TableName
          FROM         DeletedPageCacheData with(nolock) INNER JOIN
          Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
          Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
          WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
          update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

          /*Get PageID, PageNum*/
          declare @PageID varchar(50)
          declare @PageNum varchar(10)

          declare page_cursor cursor for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid) for read only
          OPEN page_cursor
          FETCH NEXT FROM page_cursor
          into @PageID, @PageNum
          close page_cursor
          deallocate page_cursor
          update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

          /*Get Template Data*/
          declare @stmt as varchar(4000),
          @col as varchar(100)
          declare c cursor for
          select column_name from information_schema.columns with(nolock) where table_name = @TableName for read only
          open c
          fetch next from c into @col
          set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
          while @@fetch_status = 0
          begin
               set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                               fetch next from c into @col

               if @@fetch_status = 0
               set @stmt = @stmt + '+ '
          end
          set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
          close c
          deallocate c
         
          exec (@stmt)


            FETCH NEXT  FROM toc_Cursor
            into @itocid, @storeid, @volumeid
     END
     CLOSE toc_Cursor
     DEALLOCATE toc_Cursor
     print 'Exited toc_cursor loop'
     /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

     truncate table deletedpagecache
GO
Top Expert 2006

Commented:
In addition to above i will recommend to create this index

Create nonclustured index ix_deletepagecachedata on deletepagecachedate(volumeid, storeid)
go

Author

Commented:
What is meant by the following?

"Change all the read only tables to be with nolock"

How would this be done?  Remember that I can't "hard set" some tables to nolock, because they are used by another application.
Top Expert 2006

Commented:
Check this statement of yours
===================

INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock)
LEFT JOIN DeletedPageCacheData with (nolock)  <---------------- Added nolock hint here.
ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))

Author

Commented:
I went through and made the suggested change, including all other join statements as well.  I am still getting the problem of a lock somewhere.

Any ideas where?

-------
CREATE PROCEDURE get_docinfo
/*      @StoreID int,
      @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData with (nolock) ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
      

/*  Update each record with a tocid */
      DECLARE @StoreID int
      DECLARE @VolumeID int      
      DECLARE @iTocID nvarchar(50)            
      
      DECLARE records CURSOR READ_ONLY for
      SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null
      OPEN records
      FETCH NEXT FROM records
      into @StoreID, @VolumeID
      print @storeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc with (nolock) ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData with (nolock) ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                  update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
            FETCH NEXT FROM records
            into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
      print 'Beginning record loop'
      DECLARE @tocstring nvarchar(500)
      DECLARE toc_cursor CURSOR READ_ONLY FOR
      SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null
      OPEN toc_cursor
      FETCH NEXT FROM toc_cursor
      into @itocid, @storeid, @volumeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            /* Get LF Path*/
            declare @counter int
            declare @mystring varchar(250)
            declare @objectname as varchar(250)
            declare @parent int
            set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
            set @counter = 0
            set @mystring = ''
            set @objectname = ''
      
            while @objectname <> 'ROOT FOLDER'
            begin
                    set @counter = @counter + 1
                    set @objectname = (select name from toc with (nolock) where tocid = @parent)
                    set @mystring =  @objectname + '\' + @mystring
                    set @parent = (select parentid from toc with (nolock) where tocid = @parent)
            end
            print @mystring
            print @storeid
                       update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

            /*Get Volume Information*/
            declare @IsImageFixed varchar(10)
            declare @FixedPath varchar(50)
            declare @RemovablePath varchar(50)
            declare volume_cursor cursor READ_ONLY for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
            FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                   Toc ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol with (nolock) ON Toc.VolumeId = Vol.VolumeId)
            OPEN volume_cursor
            FETCH NEXT FROM volume_cursor
            into @IsImageFixed, @FixedPath, @RemovablePath
            close volume_cursor
            deallocate volume_cursor

            update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

            /*Get Document Name*/
            declare @DocumentName varchar(250)
            set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
            update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

            /*Get Template Name*/
            declare @TemplateName varchar(100)
            set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

            /*Get Table Name */
            declare @TableName varchar(50)
            set @TableName = (SELECT  top 1   Tstr.TableName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

            /*Get PageID, PageNum*/
            declare @PageID varchar(50)
            declare @PageNum varchar(10)

            declare page_cursor cursor READ_ONLY for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid)
            OPEN page_cursor
            FETCH NEXT FROM page_cursor
            into @PageID, @PageNum
            close page_cursor
            deallocate page_cursor
            update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

            /*Get Template Data*/
            declare @stmt as varchar(4000),
            @col as varchar(100)
            declare c cursor READ_ONLY for
            select column_name from information_schema.columns with(nolock) where table_name = @TableName
            open c
            fetch next from c into @col
            set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
            while @@fetch_status = 0
            begin
                 set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                                fetch next from c into @col

                 if @@fetch_status = 0
                 set @stmt = @stmt + '+ '
            end
            set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
            close c
            deallocate c
            
            exec (@stmt)


              FETCH NEXT  FROM toc_Cursor
              into @itocid, @storeid, @volumeid
      END
      CLOSE toc_Cursor
      DEALLOCATE toc_Cursor
      print 'Exited toc_cursor loop'
      /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

      truncate table deletedpagecache
GO
------
Top Expert 2006

Commented:
You need to have following indexes
========================

create nonclustured index ix_DeletedPageCache on DeletedPageCache(storeid)
create nonclustured index ix_DeletedPageCacheData on DeletedPageCacheData(storeid,columnid)
create nonclustured index ix_DeletedPageCacheData_2 on DeletedPageCacheData(tocid)
create nonclustured index ix_DeletedPageCacheData_3 on DeletedPageCacheData(ifpath)

create nonclustured index ix_doc on doc(tocid, storeid)
create nonclustured index ix_vol on vol(volumeid)
create nonclustured index ix_toc on toc(volumeid)
create nonclustured index ix_tstr on tstr(templateid)

Author

Commented:
Thanks for the suggestion imran fast; I am still getting the deadlock however.  For future reference of anybody looking at this thread, nonclustured is actually nonclustered.  Just for clarificaiton in my own mind; would it not be the update statements that are locking the table?
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.