473,836 Members | 1,407 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can you help me to avoid this deadlock?

2 New Member
Hello,

I am having deadlock problem when I have a lot of visitors on my website at the same time. I am using NetTiers templates to generate C# classes for accessing DB layer and problem is in my custom Store Procedure.

I have Article table and ArticleLanguage table.
One record from Article (Id, Position, StatusId) table is the same for all languages and in ArticleLanguage (Id, LanguageId, ArticleId, Name) table I have only article names for every language.
I then physically created ArticleListing "ghost" table that have all fields from these two tables and this listing table is used for displaying articles in grid and this table is filled with my custom Store Procedure. It must not be created in memory (temporary table) because NetTiers must generate Entity for it (I got TList collection).
And there I have a SQL problem, because in my Store Procedure first command is DELETE FROM ArticleListing (I also tried this trick with GUID, it didn't help me much - you will see in script bellow) and then I do INSERT FROM Article INTO ArticleListing. .. and then UPDATE ArticleListing FROM ArticleLanguage ...

When there is a lot of users they call this store procedure and DeadLocks occured very often - I suppose because of deleting and inserting into this "ghost" table... I am sending you a scripts in order to know exactly what I am doing and please help me with advice is it possible to change Store Procedure to avoid deadlock?

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE dbo.Article(
  2.     Id int IDENTITY(1,1) NOT NULL,
  3.     Position int NULL,
  4.     StatusId int NULL
  5.  CONSTRAINT PK_Article PRIMARY KEY CLUSTERED 
  6. (
  7.     Id ASC
  8. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
  9. ) ON PRIMARY
  10.  
  11.  
  12. CREATE TABLE dbo.ArticleLanguage(
  13.     Id int IDENTITY(1,1) NOT NULL,
  14.     LanguageId int NULL,
  15.     ArticleId int NULL,
  16.     Name varchar(100) NULL
  17.  CONSTRAINT PK_ArticleLanguage PRIMARY KEY CLUSTERED 
  18. (
  19.     Id ASC
  20. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
  21. ) ON PRIMARY
  22. GO
  23. SET ANSI_PADDING OFF
  24. GO
  25. ALTER TABLE dbo.ArticleLanguage  WITH CHECK ADD  CONSTRAINT FK_ArticleLanguage_Article FOREIGN KEY(ArticleId)
  26. REFERENCES dbo.Article (Id)
  27. GO
  28. ALTER TABLE dbo.ArticleLanguage CHECK CONSTRAINT FK_ArticleLanguage_Article
  29.  
  30.  
  31. CREATE TABLE dbo.ArticleListing(
  32.     Id int IDENTITY(1,1) NOT NULL,
  33.     TransactionGuid varchar(40) NULL,
  34.     TransactionDate datetime NULL CONSTRAINT DF_ArticleListing_TransactionDate  DEFAULT (getdate()),
  35.     LanguageId int NULL,
  36.     ArticleId int NULL,
  37.     ArticleLanguageId int NULL,
  38.     Name varchar(100) NULL,
  39.     Position int NULL,
  40.     StatusId int NULL
  41.  CONSTRAINT PK_ArticleListing PRIMARY KEY CLUSTERED 
  42. (
  43.     Id ASC
  44. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
  45. ) ON PRIMARY
  46.  
  47.  
  48. GO
  49. SET ANSI_NULLS ON
  50. GO
  51. SET QUOTED_IDENTIFIER ON
  52. GO
  53. -- =============================================
  54. -- Description: Returns Article and ArticleLanguage listing in one table
  55. -- =============================================
  56. CREATE PROCEDURE dbo._ArticleListing_GetListing
  57. (
  58.     @LanguageId int,
  59.     @ArticleId int = null,    
  60.     @StatusId int = null
  61. )
  62. AS
  63. BEGIN
  64.  
  65.     --Delete old records
  66.     DELETE FROM ArticleListing WHERE TransactionDate < DATEADD(minute, -2, GETDATE())
  67.  
  68.     --Create new GUID for this transaction
  69.     DECLARE @TransactionGuid varchar(40)
  70.     SET @TransactionGuid = NEWID()
  71.  
  72.     --Insert from Article
  73.     INSERT INTO ArticleListing
  74.     (
  75.             TransactionGuid,
  76.             ArticleId,
  77.             Position,
  78.             StatusId
  79.     )
  80.     SELECT
  81.             @TransactionGuid,
  82.             Id,
  83.             Position,
  84.             StatusId
  85.     FROM    Article
  86.     WHERE (@ArticleId IS NULL OR Id = @ArticleId) AND
  87.                 (@StatusId IS NULL OR StatusId = @StatusId)
  88.  
  89.     --Update from ArticleLanguage
  90.     UPDATE  ArticleListing SET
  91.                     ArticleListing.LanguageId = ArticleLanguage.LanguageId,
  92.                     ArticleListing.ArticleLanguageId = ArticleLanguage.Id,
  93.                     ArticleListing.Name = ArticleLanguage.Name
  94.     FROM        ArticleLanguage LEFT JOIN
  95.                     ArticleListing ON ArticleListing.ArticleId = ArticleLanguage.ArticleId
  96.     WHERE        TransactionGuid = @TransactionGuid AND
  97.                     ArticleListing.ArticleId = ArticleLanguage.ArticleId AND
  98.                     ArticleLanguage.LanguageId = @LanguageId AND
  99.                     (@ArticleLanguageId IS NULL OR ArticleLanguage.Id = @ArticleLanguageId)
  100.  
  101.     --Delete not valid records
  102. DELETE FROM ArticleListing WHERE TransactionGuid = @TransactionGuid AND 
  103. (LanguageId IS NULL OR ArticleLanguageId IS NULL)
  104.  
  105.     SELECT    Id,
  106.                     TransactionGuid,
  107.                     TransactionDate,
  108.                     LanguageId,
  109.                     ArticleId,
  110.                     ArticleLanguageId,
  111.                     Name,
  112.                     Position,
  113.                     StatusId
  114.     FROM        ArticleListing
  115.     WHERE        TransactionGuid = @TransactionGuid
  116.     ORDER BY Position
  117. END
  118.  


and exception...


Expand|Select|Wrap|Line Numbers
  1. Message: Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  2.  
  3. StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at
  4. System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at
  5. System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
  6. runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
  7. stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at
  8. System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at
  9. System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at
  10. System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at
  11. System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at
  12. System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at
  13. System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at
  14. System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at
  15. Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at
  16. Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at MyProject.Data.Utility.ExecuteReader(Database database, DbCommand dbCommand) at
  17. MyProject.Data.SqlClient.SqlArticleListingProviderBase.GetListing(TransactionManager transactionManager, Int32 start, Int32 pageLength, Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
  18. MyProject.Data.Bases.ArticleListingProviderBaseCore.GetListing(Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
  19. UserControls_ArticleGrid.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at
  20. System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at
  21. System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at
  22. System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  23.  
Nov 14 '07 #1
0 1961

Sign in to post your reply or Sign up for a free account.

Similar topics

1
6160
by: Steve Thorpe | last post by:
Hi I have a deadlock situation and I am trying to debug my Trace Log. How do I find out what is the cause ? I can see from the trace I have an exclusive lock on a RID, but how can I find out what/where 'RID: 7:1:431830:13 ' is ? Regards
7
9236
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
1
2362
by: debian mojo | last post by:
Hello faculties, i'm encountering a strange a deadlock problem on my remote server which is used to give application demo to the client. It has happened that on one of the databases a deadlock situation is taking place. What is the most detailed way to detect such the cause of such a deadlock to the innermost level of detail, like what statements, stored procedures and locks are causing the deadlock to occur.
3
7632
by: Nigel Robbins | last post by:
Hi There, I'm getting a deadlock when I have two clients running the following statement. DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid There is a compound index on file_uid / obj_uid. The isolation level is UR and I have set DB2_RR_TO_RS=YES. Any thoughts why I'm getting the deadlock ?
1
4246
by: Rohit Raghuwanshi | last post by:
Hello all, we are running a delphi application with DB2 V8.01 which is causing deadlocks when rows are being inserted into a table. Attaching the Event Monitor Log (DEADLOCKS WITH DETAILS) here. From the log it looks like the problem happens when 2 threads insert 1 record each in the same table and then try to aquire a NS (Next Key Share) lock on the record inserterd by the other thread. Thanks Rohit
20
10873
by: Dean Stevens | last post by:
I have two processes: one holds a semaphore and the other waits for the semaphore. When the process which holds the semaphore is dead, the deadlock occurs. My question is there is anyway (in semaphore or operating systme) to avoid such situation? Thanks!
1
1400
by: doudou-shen | last post by:
I will use threadpool do some work with threadpool . but I haven't any information about it . who can help me! thank a lot
4
6209
by: Madhu Gopinathan | last post by:
Hi All, I am faced with a horrible hang problem. I have a COM exe server that executes some tasks. The task execution manager is a thread that manages the pool of threads, which is 4 per processor. Each task is processed in a separate thread. Each of the executer threads is an STA thread, and it goes ahead and executes the task. No problems are encountered when tasks are executed one at a time, but when multiple tasks are executed...
5
11583
by: byahne | last post by:
We just went live today with a production SQL Server 2005 database running with our custom Java application. We are utilizing the jTDS open source driver. We migrated our existing application which was using InterBase over to SQL Server. To minimize the impact to our code, we created a stored procedure which would allow us to manage our primary key IDs (mimicing the InterBase Generator construct). Now that we have 150+ users in the...
0
10549
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10592
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9376
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5650
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4019
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.