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 |
|
lotek
Starting Member
42 Posts |
 Posted - 2006-07-02 : 04:20:17
|
| Im executing a stored procedure that modifies some table data and then attempts to dump a file using BCP/Xp_cmdshell. When i do this i get into a deadlock where the spid is blocking itself. It appears like there is an open transaction so the processes are waiting for each other, but i didnt explicitly open any transactions. I fixed it temporarily by specifying the NOLOCK option on the bcp query, but im looking for a better way. Any ideas? (I need to keep all this stuff in one sp otherwise i will have to do some major retooling.)ThanksMatt |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-07-02 : 11:11:13
|
| Related topic:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67250I suppose you could test for a parallelism issue (as talked about in the thread above) by trying the statements with the maxdop option "option(maxdop 1)". It it does not seem to be due to parallelism even though you didn't explicitly begin a transaction, check if any exists while the problem is happening using "dbcc opentran".Be One with the OptimizerTG |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-02 : 13:46:02
|
| >> When i do this i get into a deadlock where the spid is blocking itself.Which spid is blocked - the one doing the update or the one doing the bcp?If it was due to a transaction then you should see the bcp spid being blocked by the SP spid not a spid blocking itself.==========================================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. |
|
|
|
lotek
Starting Member
42 Posts |
Posted - 2006-07-02 : 15:00:52
|
| The script is too big to post but it goes something like:1. OSQL Executes a script2. This script uses an INSERT/EXEC statement to call a stored procedure.3. The stored procedure calls another procedure that modifies Table1. Then the same procedure tries to bcp/xp_cmdshell a file using the data from table1. I did some more digging:spid 65 is the process using OSQL to execute the main script.spid 66 thread 1 is blocked by spid 66. spid 66 thread 2 is blocked by spid 65.It looks like both spid 66 threads have a lock type of "KEY" and their status is "WAIT". I ran DBCC OPENTRAN and it said:Oldest active transaction:SPID: 65NAME: InsertExecIt seems to have something to do with the Insert/Exec statement becuase when i run the sp by itself in query analyzer it works fine.Ideas? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-02 : 15:50:29
|
| >> This script uses an INSERT/EXEC statement to call a stored procedureEvery dml statement is executed in a transaction - the insert causes a transaction - therefore any updates within the sp will hold locks until the insert completes so the bcp will be blocked.Do you have to do the insert from the result of the sp?==========================================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. |
|
|
|
lotek
Starting Member
42 Posts |
Posted - 2006-07-02 : 16:04:32
|
| I see.Well, yes i need to do the insert from the sp's result unfortuantly. I might be able to change this behaviour, but not without some serious retooling. I could probably live with the NOLOCK option for now. I can't forsee any serious problems with that. What do you think?Thanks for the replies!Matt |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-02 : 16:27:40
|
| You could create a temp table call the sp which populates the temp table then you can access that data in the calling routine.==========================================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. |
|
|
|
|
|
|
Self Blocking SP using BCP