Everything you wanted to know about blocking...but were afraid to ask

OK...this is driving me nuts...

In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...

put in WAITFOR...sometime it worked...started with an empty table..it worked...left the 28k rows, blocked...

Now, put SELECT COUNT(*)...works each and every g-d damn time...

HUH?

Now I get to the bcp out..

added the same code WAITFOR/SELECT *...

blocks each and ever g-d damn time...

I'm very reticent to COMMIT and start another tranny block...

Anyone have any ideas?

SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO

CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO

CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO

INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@.Path sysname
, @.FName sysname
AS

SET NOCOUNT ON

BEGIN TRAN

DECLARE @.cmd varchar(8000), @.Servername sysname, @.rc int, @.error int, @.rowcount int
, @.Old varchar(255), @.New varchar(255), @.x int

CREATE TABLE #bcpLog(Col1 varchar(8000))

SET @.rc = 0

DELETE FROM wrk_DataHold

SELECT @.error = @.@.error, @.rowcount = @.@.ROWCOUNT
IF @.error <> 0
BEGIN
SET @.rc = -1
GOTO usp_ModifyRows_Error
END

SELECT @.x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'

SET @.cmd = 'bcp wrk_DataHold in ' + @.Path + @.FName + ' -S ' + @.@.SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew

OPEN OldNew

FETCH NEXT FROM OldNew INTO @.Old, @.New

WHILE @.@.FETCH_STATUS = 0
BEGIN

UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@.Old,@.New)
WHERE Col1 LIKE '%'+@.Old+'%'

SELECT @.error = @.@.error, @.rowcount = @.@.ROWCOUNT
IF @.error <> 0
BEGIN
SET @.rc = -1
GOTO usp_ModifyRows_Error
END

INSERT INTO #bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@.Old) + '" With "' + RTRIM(@.New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@.rowcount)+' row(s) affected)'

FETCH NEXT FROM OldNew INTO @.Old, @.New
END

CLOSE OldNew
DEALLOCATE OldNew

SELECT @.x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'

SELECT @.FName = SUBSTRING(@.FName,1,CHARINDEX('.',@.FName)-1)+'.new'

INSERT INTO #bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @.Path + @.FName
/*
SET @.cmd = 'bcp wrk_DataHold out ' + @.Path + @.FName + ' -S ' + @.@.SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

SET @.cmd = 'bcp #bcpLog out D:\bcpLog.txt -S ' + @.@.SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @.cmd
*/

COMMIT TRAN

usp_ModifyRows_Exit:

SELECT * FROM #bcpLog
DROP TABLE #bcpLog
SET NOCOUNT OFF
RETURN @.rc

usp_ModifyRows_Error:

CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit

GO

SET NOCOUNT OFFIs BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) an option? Since it can be part of your transaction, it dodges the bullet on locking/blocking issues.

-PatP|||Well, it's not the bcp in anymore that's the problem...

It's the bcp out...

And as Nigel suggests

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33554

He's not sure why it works at all...

All I know is that it does...

I was assuming...

DELETE Occurs...

The fact of this is that there should be a lock on the table

bcp occurs..separate thread...

should be totally blocked...and it is...

Put a SELECT COUNT(*) in before the bcp..

now the bcp works...why? I have no idea...I guess it shouldn't...

Now it works...do the replaces...fine..

I know this is true because I put the bcp out in a comment block and it finishes successfuly...

take the bcp out, out of the comment block...and it's blocked...

Guess I need to make separate transactions...|||Just for the jolly factor, I'd execute sp_lock (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_la-lz_6cdn.asp) for your spid before and after the SELECT @.@.count to see what changed.

-PatP|||yeah, I'll have to check it out...

I just made the sproc have 3 transactions...3 seconds...done...

Tried to use profiler...anyone got a good template...what a lot of stuff going on...|||If anyones' interested...

SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO

CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO

CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO

INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@.Path sysname
, @.FName sysname
AS

SET NOCOUNT ON

BEGIN TRAN
DECLARE @.cmd varchar(8000), @.Servername sysname, @.rc int, @.error int, @.rowcount int
, @.Old varchar(255), @.New varchar(255), @.x int

CREATE TABLE ##bcpLog(Col1 varchar(8000))

SET @.rc = 0

DELETE FROM wrk_DataHold

SELECT @.error = @.@.error, @.rowcount = @.@.ROWCOUNT
IF @.error <> 0
BEGIN
SET @.rc = -1
GOTO usp_ModifyRows_Error
END
COMMIT TRAN

BEGIN TRAN
SET @.cmd = 'bcp wrk_DataHold in ' + @.Path + @.FName + ' -S ' + @.@.SERVERNAME + ' -U -P -c'
INSERT INTO ##bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew

OPEN OldNew

FETCH NEXT FROM OldNew INTO @.Old, @.New

WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@.Old,@.New)
WHERE Col1 LIKE '%'+@.Old+'%'

SELECT @.error = @.@.error, @.rowcount = @.@.ROWCOUNT
IF @.error <> 0
BEGIN
SET @.rc = -1
GOTO usp_ModifyRows_Error
END

INSERT INTO ##bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@.Old) + '" With "' + RTRIM(@.New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@.rowcount)+' row(s) affected)'

FETCH NEXT FROM OldNew INTO @.Old, @.New
END

CLOSE OldNew
DEALLOCATE OldNew
COMMIT TRAN

BEGIN TRAN

INSERT INTO ##bcpLog(Col1)
SELECT 'Preparing to Archive Old file To '
+ @.Path + '_'+ @.FName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar (25),GetDate()),'-','_'),':','_'),' ','_')

SET @.cmd = 'MD ' + @.Path+
+REPLACE(@.FName,'.','_')
+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),G etDate(),120),'-','_'),':','_'),' ','_')
INSERT INTO ##bcpLog(Col1) SELECT @.cmd
INSERT INTO ##bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

SET @.cmd = 'MOVE '+ @.Path + @.FName + ' '
+ @.Path + REPLACE(@.FName,'.','_')
+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),G etDate(),120),'-','_'),':','_'),' ','_')+ '\'+ @.FName
INSERT INTO ##bcpLog(Col1) SELECT @.cmd
INSERT INTO ##bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

INSERT INTO ##bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @.Path + @.FName

COMMIT TRAN

SET @.cmd = 'bcp wrk_DataHold out ' + @.Path + @.FName + ' -S ' + @.@.SERVERNAME + ' -U -P -c'
INSERT INTO ##bcpLog(Col1) EXEC master..xp_cmdShell @.cmd

BEGIN TRAN
SET @.cmd = 'bcp ##bcpLog out ' + @.Path + 'bcpLog.txt -S ' + @.@.SERVERNAME + ' -U -P -c'
SET @.cmd = 'EXEC master..xp_cmdShell "'+@.cmd+'", no_output'
EXEC(@.cmd)
COMMIT TRAN

usp_ModifyRows_Exit:

-- SELECT * FROM ##bcpLog
DROP TABLE ##bcpLog
SET NOCOUNT OFF
RETURN @.rc

usp_ModifyRows_Error:

CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit

GO

SET NOCOUNT OFF