I have a table tblImportData that looks like this

CREATE TABLE [dbo].[tblImportData] ( 
[ImportDataID]      INT              IDENTITY(1,1)   NOT NULL,
[ImportFileNumber]  VARCHAR(50)                      NOT NULL,
[ImportData]        VARCHAR(4000)                    NOT NULL,
[FileName]          VARCHAR(1000)                        NULL,
[DateImported]      DATETIME                             NULL,
CONSTRAINT   [PK_ImportDataID] PRIMARY KEY CLUSTERED    (ImportDataID asc))

GO

CREATE NONCLUSTERED INDEX [IX_ImportData_ImportFileNumber] 
  ON tblImportData (ImportFileNumber asc)

Now there are several threads in our services that insert, read and delete data from this table.
And sometimes I see deadlocks in my logs.

So I was thinking about using WITH (UPDLOCK, ROWLOCK) on my select statements on this table but I am not sure if this will help me.

The situation is often like this

• a thread reads an import file and inserts each line of the file into tblImportData and gives all these lines the same ImportFileNumber
• the same thread then calls a stored procedure that has a parameter ImportFileNumber
• That stored procedure will then read all rows from tblImportTable where ImportFileNumber = @ImportFileNumber and does its magic
• after that the thread will try do delete all rows in tblImportData again delete from tblImportData where ImportFileNumber = @ImportFileNumber

And there the deadlocks appear, probably because there are about 7 threads that to the exact same thing (just with another stored proc)

So would it help me if I put WITH (UPDLOCK, ROWLOCK) in every select on tblImportData in the stored procedures?
Will it not make some of the selects hang?
Will it not make my delete hang?

Some example of the select statements in these procedures (just samples, not all selects)

select top 1 
       @Data = Importdata
from   dbo.tblImportData
where  ImportFileNumber = @ImportFileNumber
and    substring(Importdata, 1, 1) = '6'

insert into @table (ImportData, LaadDate, Reference, Chassis, Price)
 select Importdata,
       convert(datetime, substring(Importdata, 22, 10), 126) as LaadDate,
       substring(Importdata, 32, 10) as Reference,
       substring(Importdata, 156, 17) as Chassis,
       convert(decimal(16,2), substring(Importdata, 208, 6)) as Price
from   tblImportData
where  ImportFileNumber = @ImportFileNumber

declare crImportData cursor local FAST_FORWARD READ_ONLY for
select Importdata,
       FileName,
       DateImported
from   tblImportData
where  ImportFileNumber = @ImportFileNumber