| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2004-01-06 : 15:05:52
|
Any ideas?I execute a sproc from QA.It interogates a folder: Select @Command_String = 'Dir ' + @FilePath + '\*.txt'Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String Which I then Parse and get the details...Set up a CURSOR (Booo hiss....)DECLARE XLAT_Folder CURSOR FOR SELECT Create_Time , File_Size , [File_Name] FROM XLAT_Folder_Parsed ORDER BY [File_Name] [EDIT] WHILE FETCH STATUS = 0BEGINBEGIN TRAN[/EDIT][EDIT2]DELETE FROM Table Name from directory[/EDIT2]Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)I then bcp the data in using my connection pooling id (I'm logged on as sa in qa) SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in ' + @FilePathAndName + ' -f' + 'd:\Data\Tax\' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt' + ' -S' + @@servername + ' -U -P' SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT' INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)[EDIT] MOVE DATA FILE TO ARCHIVECOMMIT TRANANOTHER FETCH[/EDIT]A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...When I do sp_who, it shows the blocking...I'm so confused....Brett8-) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 16:07:24
|
| Have you tried the different options in the cursor like READ ONLY? When the block occurs, what table is it on and what kind of lock is it?Tara |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-06 : 16:22:30
|
| Who blocks who and why and when? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-06 : 16:23:29
|
The "parent" spid is holding a lock "type" of Key with a mode of 'x' with a status of "grant".It's on the table the bcp is tryin to load.I'm beginning to think the DELETE is contimuing to hold the lock in the sproc, and the exec of the xp_cmdshell bcp is tryin to execute...But I thought it was all synchronous (serial) inside a sproc, and you had to execute a job from withing to develop independent trhreads...Funny thing is, 3 other files loaded prior to this...Even funnier, is that it sometimes makes it through...sp 55 is blocking 5755 15 791673868 0 TAB IX GRANT55 15 791673868 1 KEY (bf01dda231bb) X GRANT55 2 304814609 0 TAB Sch-M GRANT55 15 0 0 DB S GRANT55 15 791673868 1 KEY (5401a1bef037) X GRANT55 15 791673868 1 KEY (5401f5e097ea) X GRANT55 15 791673868 1 KEY (a901d97d1cb0) X GRANT55 15 791673868 1 KEY (a301d6bfff37) X GRANT55 2 1 1 KEY (11009eba66a2) X GRANT55 15 791673868 1 KEY (c4019e78fe6b) X GRANT55 15 791673868 1 KEY (ae0187479caa) X GRANT55 15 791673868 1 KEY (a201b2807b05) X GRANT55 15 791673868 1 KEY (d401f0e46ecb) X GRANT55 15 791673868 1 KEY (91017653b4f5) X GRANT55 15 791673868 1 KEY (c6015fc19233) X GRANT55 15 791673868 1 KEY (ac01b1c2ee2b) X GRANT55 15 791673868 1 KEY (830138df4fb3) X GRANT55 15 791673868 1 KEY (ba010a58e088) X GRANT55 2 1 0 TAB IX GRANT55 2 3 0 TAB IX GRANT55 2 2 0 TAB IX GRANT55 15 791673868 1 KEY (5901e2643fe7) X GRANT55 2 0 0 EXT 1:127992 X GRANT55 2 1 3 KEY (1100ebba00e6) X GRANT55 15 791673868 1 KEY (cb012464dc80) X GRANT55 15 791673868 1 PAG 1:19025 IX GRANT55 15 791673868 1 KEY (5b01125bae5e) X GRANT55 15 791673868 1 KEY (b30173b3e551) X GRANT55 15 791673868 1 KEY (a501d9e4b89f) X GRANT55 2 1 2 KEY (6016bf8c96da) X GRANT55 15 791673868 1 KEY (a601262fc258) X GRANT55 15 791673868 1 KEY (9e014187ebbf) X GRANT55 15 791673868 1 KEY (8201d0a9f927) X GRANT55 2 3 1 KEY (1200d1849526) X GRANT55 15 791673868 1 KEY (5b0100b719cc) X GRANT55 2 2 1 KEY (1100605f76c4) X GRANT55 15 791673868 1 KEY (9101be785a4a) X GRANT55 15 791673868 1 KEY (a5011880c629) X GRANT55 15 791673868 1 KEY (220177aadba1) X GRANT55 15 791673868 1 KEY (c701870370bb) X GRANT55 15 791673868 1 KEY (7d0125208648) X GRANT55 15 791673868 1 KEY (5c01c2770c25) X GRANT55 15 791673868 1 KEY (9e0190f41c8e) X GRANT55 15 791673868 1 KEY (4f01f971f4f7) X GRANT55 15 791673868 1 KEY (a4017c83cb64) X GRANT55 15 791673868 1 KEY (a8016baf8488) X GRANT55 15 791673868 1 KEY (c2016e17468a) X GRANT55 15 791673868 1 KEY (1e01cc0f22ab) X GRANT55 15 791673868 1 KEY (a50149483711) X GRANT55 15 791673868 1 KEY (68015cf7195f) X GRANT55 15 791673868 1 KEY (a501c6b6280f) X GRANT55 15 791673868 1 KEY (600158781d0c) X GRANT55 15 791673868 1 KEY (a101f7df083e) X GRANT55 15 791673868 1 KEY (5401baab3b27) X GRANT55 15 791673868 1 KEY (ab0170e7054d) X GRANT55 15 791673868 1 KEY (1901fb436a7a) X GRANT55 15 791673868 1 KEY (4f01eb9d4365) X GRANT55 15 791673868 1 KEY (6a018fb51589) X GRANT55 15 791673868 1 KEY (a201611b1353) X GRANT55 15 791673868 1 KEY (aa01600c9214) X GRANT55 15 791673868 1 KEY (5401a8478cb5) X GRANT55 15 791673868 1 KEY (a5011ca97473) X GRANT55 15 791673868 1 KEY (68014e1baecd) X GRANT55 15 791673868 1 KEY (bb016fdcf8bd) X GRANT55 15 791673868 1 KEY (6d0116d4aa0d) X GRANT55 15 791673868 1 KEY (c701be48d9ca) X GRANT55 15 791673868 1 KEY (a401dc3d5cb3) X GRANT55 15 791673868 1 KEY (6401924adc72) X GRANT55 15 791673868 1 KEY (c1011c1b5de3) X GRANT55 15 791673868 1 KEY (ae0130692a81) X GRANT55 15 791673868 1 KEY (60015c6d4669) X GRANT55 15 791673868 1 KEY (19019c277398) X GRANT55 15 791673868 1 KEY (c001f4a6afee) X GRANT55 15 791673868 1 KEY (620137f1d2f2) X GRANT55 15 791673868 1 KEY (ed01adf69aae) X GRANT55 15 791673868 1 KEY (c0012c06376d) X GRANT55 15 791673868 1 KEY (7c0193634ccf) X GRANT55 15 791673868 1 KEY (c701961a1586) X GRANT55 15 791673868 1 KEY (d8016ac86b81) X GRANT55 2 3 2 KEY (c00073c77f8f) X GRANT55 15 791673868 1 KEY (a101d0c87ff5) X GRANT55 15 791673868 1 KEY (cd01e5ddb0d8) X GRANT55 15 791673868 1 KEY (bb01478e34f1) X GRANT55 15 791673868 1 KEY (a501ad815d6d) X GRANT55 15 791673868 1 KEY (a101b8ead4f8) X GRANT55 15 791673868 1 KEY (9e011dff5427) X GRANT55 15 791673868 1 KEY (da01ab7107d9) X GRANT55 15 791673868 1 KEY (b4010d6042d5) X GRANT55 15 791673868 1 KEY (ae01cb5d6075) X GRANT55 15 791673868 1 KEY (1001edc1e8af) X GRANT55 15 791673868 1 KEY (ba01c9afc7ba) X GRANT55 15 791673868 1 KEY (06021a9cc094) X GRANT55 15 791673868 1 KEY (ab014fc14802) X GRANT55 15 791673868 1 KEY (a30101ae1530) X GRANT55 15 791673868 1 KEY (9b011f51234b) X GRANT55 15 791673868 1 KEY (a801f95692c2) X GRANT57 15 0 0 DB S GRANT57 15 791673868 0 TAB IX GRANT57 15 0 0 DB [BULK-OP-LOG] NULL GRANT57 15 0 0 DB [BULK-OP-DB] NULL GRANT57 15 791673868 1 KEY (9e0190f41c8e) S WAIT57 15 791673868 1 PAG 1:19025 IX GRANT sysprocesses55169 0 0x0000 0 WRITELOG 56443 0 0x0000 0 NETWORKIO 57439 55 0x0003 1424531 LCK_M_S KEY: 15:791673868:1 (9e0190f41c8e) Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-06 : 16:27:05
|
quote:
Originally posted by Stoad
Who blocks who
The spid that executes a sproc in QA (55) Blocks the execution of an xp_cmdshell generated spid from the sproc (57)Now out of orderquote:
and when
When the sproc is executed, however 3 other files load before the blockingquote:
and why?
 Well, if I knew that I wouldn't be such a scrub...now would I?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 16:31:06
|
| I don't see the DELETE command in the portion of code that you posted. Could you post all of the relevant code?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-06 : 17:06:57
|
I re-edited the original post...The delete comes before the bcp...The commit occurs after the move of the file to archive and after the successful load of the file. NOTE: I also made the temp table (the one that holds the xp_cmdshell results) permanent so I could see it when the blocking occurs...But there are no rows...I guess it doesn't spit out the results until done...keep on swimming...keep on swimmming....keep on swimming..... BEGIN TRAN--SELECT @File_Name SELECT @cmd= 'select * from dbo.sysobjects where id = object_id(N' + '''' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '''' +')' + 'and OBJECTPROPERTY(id, N' + '''' + 'IsUserTable' + '''' + ') = 1'--SELECT @cmd EXEC(@cmd) SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error If @Error_Out <> 0 BEGIN Select @Error_Loc = 5 Select @Error_Type = 50001 GOTO Load_XLAT_sp_Error END If @Result_Count = 0 BEGIN SELECT @Error_Loc = 5 SELECT @Error_Message = 'Table Not Found. Make sure file name ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' Is Correct' SELECT @Error_Type = 50002 GOTO Load_XLAT_sp_Error END--PRINT 'CREATING WORK TABLE'/* CREATE TABLE XLAT_BCP_Results (Col1 varchar(8000))*/ SELECT @cmd = 'DELETE FROM '+ @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1)--SELECT @Cmd EXEC(@Cmd) SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error If @Error_Out <> 0 BEGIN Select @Error_Loc = 6 Select @Error_Type = 50001 GOTO Load_XLAT_sp_Error END Select @FilePathAndName = @FilePath + '\' + @File_NameBrett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 17:14:42
|
| Instead of DELETE FROM, try TRUNCATE TABLE. I'll do some research as to why the DELETE is holding the locks after it has completed.Tara |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-07 : 02:07:47
|
| BTW, Brett, I can't rollback bcp's inserts.... Can you?begin tranexec master..xp_cmdshell 'bcp mydb..t in D:\my.txt -c -Smyserver -Usa -Pmypwd'select * from trollback transelect * from t |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-07 : 08:52:57
|
quote:
Originally posted by Stoad
BTW, Brett, I can't rollback bcp's inserts.... Can you?begin tranexec master..xp_cmdshell 'bcp mydb..t in D:\my.txt -c -Smyserver -Usa -Pmypwd'select * from trollback transelect * from t
No I can't...means I have to rethink this....So if the DELETE is succesful and the bcp fails...I wonder about BULK INSERT...but in a PROD environment, I'd have to file an exception...don't know why....any thoughts on why?And Tara, I can't file an exception for TRUNCATE..Think I'll move the commit before the bcp, right after the delete...But it's curious as to why 3 go, but not the forth...STOAD: Hey great monicker...DELETE FROM Year_End..XLAT_CompanyBEGIN TRANEXEC master..xp_cmdshell 'bcp Year_End..XLAT_Company in d:\Data\Tax\XLAT\XLAT_Company.txt -fd:\Data\Tax\XLAT_Company.fmt -SNJROS1D151\NJROS1D151DEV -U -P', NO_OUTPUTSELECT COUNT(*) FROM Year_End..XLAT_CompanyROLLBACK TRANSELECT COUNT(*) FROM Year_End..XLAT_Company Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-07 : 09:06:42
|
| Don't know why...but when I moved the COMMIT up to just after the DELETE, it works for all of them....like total elapsed time 2 seconds...not alot of data mind you...just wonder why it locked for the last one and not the others...I would have thought that the delete would have finished and then the bcp would run....Also, thanks stoad, I thought the bcp would have been rolled back...but I guess because it's outside the transaction (a guess) that it committed itself (2 phase commit?).Anyway, thanks for all the input.Much appreciated.Brett8-) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-07 : 15:27:04
|
| Aha, Brett.. and I am still digesting your discoveries. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-08 : 07:03:44
|
| Looks like as if kind of "tablockX" works. What if explicitlyhint a less severe lock: delete from t with (paglock).......??? |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-16 : 15:07:42
|
| Back to our rams. If it is sequential then how the bcpmanages to prevent the simple selecting from the books?begin trandelete from books where n>20select * from books---- select * from txt << uncomment this to see a miracleexec master..xp_cmdshell'bcp xx..books out D:\ff.txt -n -Sxxx -Usa -Pxxx'---- rollback tran select @@trancountNote: don't replace select * with e.g. select count(*) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-17 : 06:10:50
|
| Compare:begin trandelete from t where n>7exec master..xp_cmdshell'bcp db..t out D:\ff.txt -n -Sxxx -Usa -Pxxx'andbegin trandelete from t where n>7exec master..xp_cmdshell'bcp "select * from db..t with(nolock)" queryout D:\ff.txt -n -Sxxx -Usa -Pxxx' |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-20 : 11:21:55
|
| Hey, just checked back...Is the leading me to belive that it is NOT serial?How can it be partially serial?Is it returning before the work is done? Or it thinks the work is done and it isn't?Brett8-) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-20 : 16:11:44
|
| I would be very glad to get any comments on this:In 1st QA connection run this code:begin trandelete from t where n=5555--- select * from t with(nolock)--------- rollback tran select @@trancountThen, seconds later, in 2nd QA connection run this:begin trandelete from t where n=7777select getdate()--------- rollback tran select @@trancountSeems like now we get oops - deleting cannot be started -the Globe icon keeps spinning - and select getdate() cannotbe executed............BUT!!!! Switch back to the 1st connection window and run therethe commented --- select * from t with(nolock) ................Rows with n=7777 are deleted!! What is going on????????????Why the 2nd connection executing gets stuck after its delete statement? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-01-20 : 16:41:55
|
| In reference to your last post Stoad, the reason might be due to locking granularity. If the n=7777 rows (or Keys) reside on the same page or extent as any rows (Or Keys) in the n=5555 and the locking granularity is anything higher than a row lock then trouble!DavidM"SQL-3 is an abomination.." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-20 : 16:47:41
|
| What does sp_lock show you stoad?Still want to know why my bcp was blocked by the delete....Had to rework the process....Brett8-) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-21 : 04:05:18
|
| Guys,I only want to understand why the 2nd transaction stops thoughits deletes were done. Other case if they were NOT. But they were.And what now the 2nd transaction is waiting for? I'm not able to seeany Logic in that. And while I can't see it I don't mind what sp_lock shows. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-02-13 : 17:27:50
|
| Found an answer for my own above question:why the 2nd delete statement gets blocked?Though select ... with(nolock) shows that rowswhere n=7777 were successfully deleted.In the 1st connection:begin trandelete from t where n=5555Few seconds later and in the 2nd connection:begin trandelete from t where n=7777The answer: because the column "n" is non-indexed one!!Try to index it and you'll see the pleasant difference.I think the whole picture is like this: though rows wheren=7777 were deleted the sql engine needs to make surethat he deleted all such rows and for this end hewaits until the rows (where n=5555) will be freed.In short,without an index he/she(?) is forced to scan the entire table. |
|
|
|
Next Page
|
Blocking When Doing bcp