By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,320 Members | 2,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,320 IT Pros & Developers. It's quick & easy.

Can you help me to avoid this deadlock?

P: 2
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
Share this question for a faster answer!
Share on Google+

Post your reply

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