Post reply

Deadlock via SELECT INTO query getting a SIU lock

  • July 11, 2013 at 10:38 am

    #275083

    i'm trying to resolve a deadlock issue between a SELECT INTO query and an UPDATE query.

    UPDATE tableA

    SELECT INTO DiffDatabase.dbo.newTable FROM tableA

    the UPDATE query has an IX lock on page 1. the SELECT INTO query has a SIU lock on page 2. the UPDATE is requesting an IX lock on page 2, while the SELECT INTO wants a S lock on page 1, thus the deadlock

    my question is, why does the SELECT INTO have a SIU lock in the first place?

  • July 16, 2013 at 4:04 pm

    #1633033

    That sounds a little funny. But are those the complete statements? That is, there are no WHERE clauses?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 22, 2013 at 5:51 am

    #1634420

    Hi,

    It's a typical cycle type deadlock.

    Which isolation level do you use?

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • July 22, 2013 at 9:40 am

    #1634532

    all read committed. i fixed the issue with a query rewrite and a new index. thanks for the reply though

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply