Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-09-27 : 07:43:32
|
| Dear Experts,There is a main parent stored procedure that calls 5 child procedure within itself when executed. Each individual procedure had its own transaction blocks ( BEGIN TRAN - COMMIT TRAN) and also the main procedure has its own transaction block.Since the parent procedure was taking a long time to execute, we removed the transaction blocks from the other 5 child procedures. Our idea is that the BEGIN TRAN - COMMIT TRAN of the parent procedure will take care of the COMMIT or ROLLBACK of the entire process.Now we are encoutering several dead locks when the application is run.When I see the deadlock graph from the Profiler, the both process shows the same object id. I understand that the same procedure's process caused the deadlock. It also shows me a index name in the KEY LOCK rectangular block in the deadlock graph.I'm not sure how to proceed further. Any help would be appreciated.Thanks in advance,Hariarul |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-27 : 08:20:19
|
| The chld transaction would only increment and decrement the transaction count so removing them shoudn't have had much effect.Are you sure you aren't spawning another connection somewhere.Which service pack do you have - think there was a problem with this sort of thing which was fixed but maybe there are still instances outstanding.When you say both process show the same object_id are you talking about the spid or the resource that the deadlock is on?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-09-27 : 08:31:57
|
| I'm using SQL Server 2005 - SP2. The object_id I meant is the one which it shows on clicking the oval shaped info (with the process info). It shows as given below.Statement:Proc[DatabaseID = 8 ObjectId = 1234567]BTW, How do I see the other spawning connections ? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-09-28 : 01:06:18
|
| Thanks Nr. |
|
|
|
|
|
|
Deadlock issue.