Deadlocked on lock problem in sql with multithreading

[ashu2409]

Hi,

i have created a window service with multithreading, but getting many deadlocks on sql in one Stored procedure when i start this service,and after that service does not do anything but in idle mode.

the details of exception is below.

Error :Wednesday, September 23, 2009 2:22:58 AM
Method:UPD_CrawlerLastUrl
1. Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at HJS_CrawlerService.Class.clsDB.UPD_CrawlerLastUrl(String url, Int32 xml_id, Int64 curpage, String page_counter, String thread_id, String keyword, Int32 last_url_count)
3. .Net SqlClient Data Provider
==============================================================================================================

Pleae help me...

Thanks,
Ashish Pandey

[sknake]

Post the contents of the stored procedure you using, and the CREATE TABLE statement of all tables involved. It needs to have all of the constraints and indexes on the table.

This is an MSSQL question and not an ASP.NET question. These questions should be posted to the MS SQL forum in the future. A moderator will move this thread shortly so please do not create another thread for it :)

[cliff81]

I'd recommend using SQL Profiler and setting some of the deadlock events like DeadLock Graph, Lock:Deadlock Chain, etc. this article explains what to do http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

[ashu2409]

Hi,
Thanks for reply,
this was my SP.

CREATE PROCEDURE [UPD_CrawlerLastUrl]            
         
@xml_id bigint,      
@url varchar(max),      
@curpage bigint,      
@page_counter nvarchar(max),      
@thread_id  nvarchar(max),      
@keyword  nvarchar(max),      
@last_url_count int          
AS            
BEGIN     
           
BEGIN TRANSACTION    
BEGIN TRY        
 update hjs_job_crawler_details set last_url=@url,current_page=@curpage,page_counter=@page_counter,isCompleted=0,      
 last_keyword= @keyword,last_url_count=@last_url_count      
 where job_crawler_id=@xml_id            
COMMIT    
END TRY    
BEGIN CATCH    
 SELECT ERROR_NUMBER() AS ER_Num    
ROLLBACK    
END CATCH;      
    
update hjs_job_details set thread_id = @thread_id where xml_id=@xml_id      
      
END

there is last line of this query i.e.
update hjs_job_details set thread_id = @thread_id where xml_id=@xml_id

this was causing problem, coz the last update query had taken a lot time to update the records so deadlock was there,
Now i have written a saparate sp for the last line of this SP,
and also i have used TRY CATCH, and not getting deadlock problem.
should i assume that problem has gone for deadloack?

but now there is one another problem appears, the memory usage of services increasing, in 3 days it has been goes to 300 MB, so everything hanged in 3-4 days, i am using thread pooling, might it be due to that? i think there is something wrong with multithreading management.

the table structure is as follows:-

USE [HJS_DevPostJob]
GO
/****** Object:  Table [dbo].[hjs_job_details]    Script Date: 10/05/2009 13:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hjs_job_details](
	[job_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[customer_id] [numeric](18, 0) NULL,
	[campaign_id] [numeric](18, 0) NULL,
	[job_title] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[job_code] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[company_name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[source_company_name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[company_url] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[jobdescription_url] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[about_company] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[job_summary] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[date_posted] [datetime] NULL,
	[industry] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[specialty] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[skills] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[education] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[experience] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[responsibilities] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[job_type] [int] NULL,
	[min_salary] [numeric](18, 2) NULL,
	[max_salary] [numeric](18, 2) NULL,
	[requirement] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[summary] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[street] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[city] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[state] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[zipcode] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[country] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[contact_person] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[contact_email] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[contact_phone] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[contact_fax] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[status] [int] NULL,
	[crawler_date] [datetime] NULL,
	[modified_on] [datetime] NULL CONSTRAINT [DF_hjs_job_details_modified_on]  DEFAULT (getdate()),
	[thread_id] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CrawledBy] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FeederXMLId] [int] NULL,
	[SignOnBonus] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Bonus] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Expiry_date] [datetime] NULL,
	[Package_id] [bigint] NULL,
	[ViewCount] [int] NULL,
	[short_desc] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[xml_id] [bigint] NULL,
	[source_type_id] [bigint] NULL,
	[address] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[salaryRange] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CareerPageUrl] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 
(
	[job_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and for second table :-

USE [HJS_DevPostJob]
GO
/****** Object:  Table [dbo].[hjs_job_crawler_details]    Script Date: 10/05/2009 13:07:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hjs_job_crawler_details](
	[job_crawler_id] [bigint] IDENTITY(1,1) NOT NULL,
	[cust_id] [bigint] NULL,
	[site_url] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[file_name] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[updated_date] [smalldatetime] NULL,
	[read_status] [tinyint] NULL CONSTRAINT [DF_hjs_job_crawler_details_read_status]  DEFAULT ((0)),
	[isactive] [tinyint] NULL CONSTRAINT [DF_hjs_job_crawler_details_isactive]  DEFAULT ((1)),
	[read_date] [smalldatetime] NULL CONSTRAINT [DF_hjs_job_crawler_details_read_date]  DEFAULT (((1)/(1))/(1900)),
	[read_start_date] [smalldatetime] NOT NULL DEFAULT ('01/01/1900'),
	[exception_mark] [int] NOT NULL DEFAULT ((0)),
	[exception_detail] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[thread_id ] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[source_type_id] [bigint] NULL,
	[last_url] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[isCompleted] [int] NULL,
	[current_page] [bigint] NULL,
	[page_counter] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[last_keyword] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[total_record] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[record_counter] [bigint] NULL,
	[job_expiry] [int] NULL,
	[last_url_count] [int] NULL,
 CONSTRAINT [PK_hjs_job_crawler_details] PRIMARY KEY CLUSTERED 
(
	[job_crawler_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Please suggest any solution.

Post the contents of the stored procedure you using, and the CREATE TABLE statement of all tables involved. It needs to have all of the constraints and indexes on the table.

This is an MSSQL question and not an ASP.NET question. These questions should be posted to the MS SQL forum in the future. A moderator will move this thread shortly so please do not create another thread for it :)

[sknake]

Please use code tags when posting code on daniweb:

Have you indexed against the columns you are using to locate records?

update hjs_job_crawler_details .... where job_crawler_id=@xml_id 
update hjs_job_details .... where xml_id=@xml_id 

In this case you need to create or ensure indexes exist for hjs_job_crawler_details.job_crawler_id and hjs_job_details.xml_id=@xml_id. If the tables are large and the columns aren't indexed then the SQL Server has to work harder to locate the records (increasing the probability of a deadlock due to longer running queries) and it has to lock more of the table. If you have a unique identifier or index then SQL Server can lock the index while it is updating the rows affected, but if it is not indexed then SQL Server has no choices but to start locking hunks of the table or maybe the entire table itself.

An example of creating an index:

CREATE NONCLUSTERED INDEX IX_hjs_job_details_8 ON dbo.hjs_job_details
(
  xml_id
)

[ashu2409]

Hi ,
sorry in future i will post the code in code tag.
i have made the indexes on columns as suggested,

this is a window service in C# ,which is supposed to run 24x7,now when i run it, it runs fine for some time (may be some time 15 hours or 1 day), but after that it hanged out. it does'nt do anything. it does not show any exception, but i found this in event log,

1. .NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)
   2.Windows cannot query for the list of Group Policy objects. A message that describes the reason for this was previously logged by the policy engine.

i dont know whether these two exception related to each other or not.

i am using multithreading for xml crawling in this service.
this service is in .net framework 3.5 but error is showing framework 2.0
i have checked with debugger in vs2008 but did not find anything.
is this something related to memory management or deadlock,

Please suggest any solution.
Thanks

[sknake]

Have you googled for "NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)"? It seems that error is very unspecific and can happen due to WCF data contracts, COM interop, Antivirus, firewalls, etc. That being said you should review your code but I don't think it is a 'you did _____' error, you will have to figure it out. I doubt it is related to the query deadlock but may be related to a connection to the SQL Server failing to establish, or the exception being thrown as a result of the deadlock.

Have you made any progress with it? Did the indexes help your deadlock issue? I also read an article where someone upgraded from 2gb to 4gb of ram and it also fixed the issue.

[ashu2409]

hi sknake,
Thanks for the reply,

yes i have searched a lot for "NET Runtime version 2.0.50727.1433 -Fatal Execution Engine Error (79FFEE24) (80131506)", but did'nt find any exact solution for that,it might be due to firewall or antivirus restictions, now i come to know that this exception i m getting only on our side, not on client side, so i am not very concern about this,

but on the client server we are getting this exception
"Event Type: clr20r3, P1:crawler.exe, P2:1.0.0.0, P3:4ac1dfc5, P4: mscorlib, P5:2.0.0.0, P6:492b834a; P7:343f, P8:21c, P9: system.io.ioexception"

and it writes a log for this

"Error :Thursday, October 08, 2009 12:14:29 AM
Method:ProcessPageRequest
FileName:emeritus.xml
1. The process cannot access the file path because it is being used by another process.
2. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at HJS_CrawlerService.Class.clsErrorLog.WriteErrorLog(Exception Exp, String strMethodName) in C:\Plesk\VHOSTS\healthcarejobssource.com\httpdocs\Services\HJS_Crawler_Updated\Class\clsErrorLog.cs:line 41
at HJS_CrawlerService.Class.clsJobs.InsertJob(clsXml objxml) in C:\Plesk\VHOSTS\healthcarejobssource.com\httpdocs\Services\HJS_Crawler_Updated\Class\clsJobs.cs:line 345
at HJS_CrawlerService.Class.clsThread.StartProcess(clsXml objxml, String strQuery) in C:\Plesk\VHOSTS\healthcarejobssource.com\httpdocs\Services\HJS_Crawler_Updated\Class\clsThread.cs:line 579
3. mscorlib"

and also memory usage in task manager high goes upto 200MB,
should it goes upto that level?
so it look like two thread a the same time tries to write into file, i have put a lock for that now,

let's see what happens,,,
i have to look into this for 4-5 days.......

Thanks,
i will post the feedback ..........

Have you googled for "NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)"? It seems that error is very unspecific and can happen due to WCF data contracts, COM interop, Antivirus, firewalls, etc. That being said you should review your code but I don't think it is a 'you did _____' error, you will have to figure it out. I doubt it is related to the query deadlock but may be related to a connection to the SQL Server failing to establish, or the exception being thrown as a result of the deadlock.

Have you made any progress with it? Did the indexes help your deadlock issue? I also read an article where someone upgraded from 2gb to 4gb of ram and it also fixed the issue.