Deadlocks :-( 1

[HestonJames]

Hello Guys,

I've got this import script here which takes a bunch of data, sticks it in a temporary staging table and then move the lot into the correct tables, buiding FK's and whatnot.

					CREATE TABLE ##MessageStaging (
						DeviceAddress char(17) Collate Database_Default,
						PushPort smallint ,
						LogDateTime datetime,
						LogClass_ID int,
						Media_ID int,
						Campaign_ID int
					)


[b]loop and import data into the temp table.[/b]
					
					INSERT 
					  INTO OUI
					     ( OUI 
					     , Manufacturer )
					SELECT DISTINCT LEFT(##MessageStaging.DeviceAddress, 8)
					     , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
					  FROM ##MessageStaging
					LEFT OUTER
					  JOIN OUI
					    ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress, 8) 
					 WHERE OUI.OUI IS NULL 
										
					INSERT
					  INTO Device
					     ( OUI_ID
					     , Address
					     , PushPort )
					SELECT DISTINCT
					       OUI.OUI_ID
					     , SUBSTRING(##MessageStaging.DeviceAddress,10,8)
					     , ##MessageStaging.PushPort
					  FROM ##MessageStaging
					INNER
					  JOIN OUI
					    ON OUI.OUI = left(##MessageStaging.DeviceAddress,8) 
					LEFT OUTER
					  JOIN Device
					    ON Device.OUI_ID = OUI.OUI_ID
					   AND Device.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8)
					 WHERE Device.OUI_ID IS NULL
					
					UPDATE Device
					   SET PushPort = ##MessageStaging.PushPort
					  FROM ##MessageStaging
					INNER
					  JOIN OUI
					    ON OUI.OUI = left(##MessageStaging.DeviceAddress,8) 
					INNER
					  JOIN Device as D2
					    ON D2.OUI_ID = OUI.OUI_ID
					   AND D2.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8)
					   AND ( D2.PushPort IS NULL
					       or D2.PushPort <> ##MessageStaging.PushPort )
							
					INSERT
					  INTO MessageLog
					     ( Device_ID
					     , LogDateTime 
					     , LogClass_ID
					     , Media_ID   
					     , Campaign_ID
					     , ThinkTank_ID )
					SELECT Device.Device_ID
					     , ##MessageStaging.LogDateTime
					     , ##MessageStaging.LogClass_ID
					     , ##MessageStaging.Media_ID
					     , ##MessageStaging.Campaign_ID
					     , '#LOCAL.tank_id#'
					  FROM ##MessageStaging
					INNER
					  JOIN OUI
					    ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress,8) 
					INNER
					  JOIN Device
					    ON Device.OUI_ID = OUI.OUI_ID
					   AND Device.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8)

On occasion when this code is being run I'm recieving the following error message:

Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Now, this is of no real concequence to me as I'm catch the error and retrying the import a few moments later and it all goes in fine, however, I'd still like to get to the bottom of what is causing this issue.

Can anyone make any reccomendations on this? I'd really appreciate it.

One thing to note, this is not actualy a global temp table ## the code comes from an Adobe ColdFusion query block and I have to ## to escape the pound, its actualy only producing a single # in the block in the generated SQL.

Cheers,

Heston

 

[jbenson001]

http://www.sql-server-performance.com/tips/deadlocks_p1.aspx

 

[HestonJames]

jbenson,

Thank you for that, interesting little document. I have this whole import process currently wrapped in a single transaction, including the import of data into the staging table.

I'll take note of a few of those pointers and see if I can shorten the process so that we're not locking the resources for such a long period of time.

Cheers,

Heston

 

[jbenson001]

Hope it helps

 

[HestonJames]

Purple star for effort ;-)

 

[jbenson001]

Thank you!!

 

[HestonJames]

Hello Guys,

I'm goging to drag this thread back to life again as this little deadlock situation is causing me all kinds of headaches and I could really use a little more help.

Because this import script can be called from multiple clients its becomming more and more of an issue as the client base grows and I'm getting deadlock error reports thrown at me on a very regular basis now, all stemming from this import proceedure (I say 'proceedure', but I dont mean SP, just a process .

Now, after reading through the link which JBeson has provided I can understand a little more clearly what this problem is about, however, I'm not sure how to effectivly tackle the situation.

I'm trying to identify which part of the process is causing the grief, see, my logic is that the first couple of steps, creating the temp table and importing the data into it shouldnt be a problem, right? because each connection to the database will have its own temp table instance so we shouldn't have seperate application threads trying to acquire a lock on the same temp table as its importing data. Is that correct?

So, this would leave me to believe that the issue must result lower down the chain, when we're moving data from the temp table into its perminant home.

In the real world application the entire proceedure above is placed in a transaction block, could this be whats causing the problem? I'm going to guess that when starting the transaction the server acquires locks on all the resources it'll require, and not free them untill the end of the transaction, is that correct?

Now, I'm wondering if I really need to place this entire lump inside a transaction? See, all the queries except the last one are just preps, they just ensure that all the appropriate FK restraints are in place ready for when we do the final import into the MessageLog table in the last query, so if all the queries were to process correctly and then fail on the last, it wouldnt be of any real concequence to me, I wouldnt need to rollback, it would just mean less prep the time the data is next imported.

How would you guys reccomend that I deal with this problem? The thought of this problem escalating much further has got me a little spooked and I'd like to nip it in the bud as soon as possible.

Cheers all,

Heston