My client is getting a recurrent deadlock between this two statements:
DELETE b
FROM employees a WITH (NOLOCK), employees_prs b WITH (NOLOCK)
WHERE a.employer_id = @id
AND a.id = b.employee_id
AND b.employee_prs = 1
INSERT INTO employees_prs (employee_id, employee_prs)
SELECT DISTINCT a.id, 1
FROM #tempdata01 b, employees a WITH (NOLOCK)
WHERE a.employer_id = @id
AND a.no_id = b.no_id
The resource list in the deadlock graph shows this:
<resource-list>
<pagelock fileid="4" pageid="28897262" dbid="14" objectname="db01.dbo.employees_prs" id="lock373f60880" mode="IX" associatedObjectId="72057601182466048">
<owner-list>
<owner id="process352a02988_INSERT" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process11e0a1b88_DELETE" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="4" pageid="25066463" dbid="14" objectname="db01.dbo.employees_prs" id="lock61081f580" mode="U" associatedObjectId="72057601182466048">
<owner-list>
<owner id="process11e0a1b88_DELETE" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process352a02988_INSERT" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
I think the DELETE statement has a U lock on a page, but is also waiting to acquire another U lock on another page. Likewise, the INSERT statement has an IX lock on a page and keeps waiting to acquire another IX lock on another page. Is that correct?
How can I fix the deadlock?
Table´s structure:
CREATE TABLE [dbo].[employees](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[employer] [int] NOT NULL,
[name] [varchar](30) NULL,
CONSTRAINT [employees_PK] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
CREATE TABLE [dbo].[employees_prs](
[employee_id] [int] NOT NULL,
[employee_prs] [tinyint] NOT NULL,
CONSTRAINT [PK_employees_prs] PRIMARY KEY CLUSTERED
(
[employee_id] ASC,
[employee_prs] ASC
)
)
