SQL SERVER lock:escalation

• Lalit-247722

  SSC Journeyman

  Points: 93

  More actions

  July 22, 2005 at 8:22 am

  #90128

  Hi

  We have a Database where all tables have a coulmn called ID.

  We have a table named APAL3ObjectLink now the APAL3ObjectLink table allows

  to arbitrarily link to objects...using IDs and Type

  for example, if i want to link the AP3Shipment with ID=12 and the APAL3Activity with ID=234 I create an object link like:

  INSERT INTO APAL3ObjectLink (FromEntityID, FromEntityName, ToEntityID, ToEntityName, Type) VALUE ('12', 'AP3Schipment', '234', 'APAL3Activity', 1)

  the system builds triggers such that when a row in any table is deleted, it also deletes all related APAL3ObjectLink's.

  now we have triggers which remove any object links associated with an object

  for example, if u delete shipment 12, the trigger will do that:

  DELETE APAL3ObjectLink WHERE (FromEntityID=12 AND FromEntityName='AP3Shipment') OR (ToEntityID=12 AND ToEntityName='AP3Shipment')

  but i'm worried about recursion with all this

  I have now noticed a few times deadlocks with APAL3ObjectLink's

  it appears that from a certain moment onwards, sql server simply locks the entire table

  the entire OL table that is it seems to be random... well let's say i've never noticed a pattern

   

  but i know we have a lock:escalation with OL's

  The Cascade Delete Trigger goes like this For Table AP3Shipment -

  CREATE trigger cd_APAL3ObjectLink_AP3Shipment on AP3Shipment

  for delete

  as

  if @@rowcount = 0

    return

  delete APAL3ObjectLink from APAL3ObjectLink, deleted WHERE (APAL3ObjectLink.FromObjectType='AP3Shipment' and APAL3ObjectLink.FromObjectID=deleted.ID) or (APAL3ObjectLink.ToObjectType='AP3Shipment' and APAL3ObjectLink.ToObjectID=deleted.ID)

  We have same kind of triggers for other Tables also which have records in APAL3ObjectLink Table.

  Does anybody know how to fix it?

  ie Any Amendenments in Trigger or some other solution to this.

  Thanks

• Calico

  Old Hand

  Points: 372

  More actions

  July 22, 2005 at 11:03 am

  #576510

  Hi,

  In the past we encountered many deadlocks caused by many updates during office hours. The deadlock was caused by lock escalation from row to table.

  During thoses days (small period) i activated -T1211 Startup Parameter.

  You must be very carefull with -T1211 Startup Parameter !

  Check with Micosoft about -T1211

  may be it can help you

  Thank you

  Calico