strange Deadlock in sql server.

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

strange Deadlock in sql server.

Author

Topic

johnson2008
Starting Member

1 Post

Posted - 2008-10-06 : 22:39:27

I write a asp.net website which upload flattext file to sql table. I use bcp command in stored-procedure to import the data in file to a table. and then in this table I should do some complex operation to the records. Each file has almount 60 thousands rows data, so the operation will low-efficiency. In this website, I should use multiple threads, That's means after user upload a file to web server, the web application will involk the sql stored-procedure to load the file data to table(tblImport) in a background thread, and the user can upload another file and create another thread. The threads involk stored-procedure to load the operate data to the sql server. Each file data will be loaded to the same table but seprate using a field FiledID. Data from diffrent file has different FiledID. So the operation to the file will operate the different data in the same table.
Everything works well but the problem is when the user upload about ten files, that's there are ten threads involking the same stored-procedures. I have a stored-procedure as the following. When there is multiple threads involk it, that is there are several sessions of this stored-procedure operating the same table but different records. it will cause a deadlock in the table(tblImport).
Then the deadlock comes up, the t-sql cause the deadlock is:


-- update the data to the table according the temp table.
-- When there are multiple threads running this t-sql, it will be cause a deadlock.
	UPDATE tblImport SET PackID=#tempTable.PackID, Line = #tempTable.Line
	FROM tblImport(NOLOCK) INNER JOIN #tempTable ON
	tblImport.ID = #tempTable.ID

I traced this sql in the Sql Server Profiler. When the deadlock came up, there are several links running this t-sql. But why this sql can cause the deadlock. I don't very understand. Who can help me. Thank you very much.




ALTER PROCEDURE [dbo].[sp_Package]
(
	@FieldID varchar(36),
	@packageSize int,
	@return int OUTPUT
)
AS

DECLARE @PackID AS INT
DECLARE @sql AS NVARCHAR(1024)
DECLARE @Row AS INT


SET @PackID = 0
SET @Row = @packageSize

BEGIN TRY
	--BEGIN TRANSACTION
	
--Because the record to operate will has 60 thousands, load the record to a temp table.
	SELECT * INTO #tempTable from tblImport(NOLOCK) WHERE FieldID=@pid

--Apply a PackID for each @packageSize(for example 1000) records. Each @packageSize records as a package.
	WHILE(@Row = @packageSize)
	BEGIN
		
		SET @sql = 'UPDATE #tempTable SET PackID = ' + CAST(@PackID + 1 AS VARCHAR) + ' FROM 
			#tempTable INNER JOIN (SELECT TOP ' + CAST(@packageSize AS VARCHAR) + ' * FROM #tempTable WHERE FieldID=''' + @FieldID + ''' AND PackID IS NULL) S ON
			#tempTable.ID = S.ID AND #tempTable.FieldID = S.FieldID AND #tempTable.CustomNumber = S.CustomNumber'
		EXEC sp_executeSql @sql

		SET @Row = @@ROWCOUNT
		IF(@Row <> 0)
			SET @PackID = @PackID + 1
	END	

-- Apply a number to each record at each package.
	UPDATE #tempTable SET Line = tempTable.RowNumber
	FROM #tempTable INNER JOIN
	(SELECT ID, FieldID, PackID, CustomNumber, ROW_NUMBER() OVER (PARTITION BY FieldID, PackID ORDER BY FieldID, PackID) AS RowNumber
	FROM #tempTable) tempTable ON
	#tempTable.ID=tempTable.ID AND #tempTable.FieldID=tempTable.FieldID AND #tempTable.CustomNumber=tempTable.CustomNumber
	AND #tempTable.PackID = tempTable.PackID

-- update the data to the table according the temp table.
-- When there are multiple threads running this t-sql, it will be cause a deadlock.
	UPDATE tblImport SET PackID=#tempTable.PackID, Line = #tempTable.Line
	FROM tblImport(NOLOCK) INNER JOIN #tempTable ON
	tblImport.ID = #tempTable.ID

	--COMMIT TRANSACTION
	SET @return = 0
END TRY
BEGIN CATCH
	--ROLLBACK TRANSACTION

	DECLARE @ErrorMessage nvarchar(4000) 
    DECLARE @ErrorSeverity int 
    DECLARE @ErrorState int 
    DECLARE @ErrorNumber int 
    DECLARE @ErrorLine int     

    SET @ErrorMessage = 'DB server:[sp_Package] an error occurs.'  + ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY() 
    SET @ErrorState = ERROR_STATE() 
    SET @ErrorNumber = ERROR_NUMBER() 
    SET @ErrorLine = ERROR_LINE()     

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine) 

END CATCH

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-06 : 23:36:01

Do you get the deadlocks even with the begin/commit/rollback transaction statements commented out (as they are in your posted code)?
by the way, the (nolock) hint is ignored since it is on the table being updated.

What is the other statement involved in the deadlock? The same sp/statement from a different spid?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources