Deadlock via SELECT INTO query getting a SIU lock

• tom_ciolek 89611

  Valued Member

  Points: 70

  More actions

  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?

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  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]

• Igor Micev

  SSC-Dedicated

  Points: 33110

  More actions

  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

• tom_ciolek 89611

  Valued Member

  Points: 70

  More actions

  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