خرید بک لینک

Vote count: 0

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
)
)

asked 2 mins ago

- - , .

برچسب: نویسنده: استخدام کار تاريخ: سه شنبه 27 بهمن 1394 ساعت: 4:13

صفحه بندی