Can you help me to avoid this deadlock?

[halex]

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?

CREATE TABLE dbo.Article(
    Id int IDENTITY(1,1) NOT NULL,
    Position int NULL,
    StatusId int NULL
 CONSTRAINT PK_Article PRIMARY KEY CLUSTERED 
(
    Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
 
 
CREATE TABLE dbo.ArticleLanguage(
    Id int IDENTITY(1,1) NOT NULL,
    LanguageId int NULL,
    ArticleId int NULL,
    Name varchar(100) NULL
 CONSTRAINT PK_ArticleLanguage PRIMARY KEY CLUSTERED 
(
    Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE dbo.ArticleLanguage  WITH CHECK ADD  CONSTRAINT FK_ArticleLanguage_Article FOREIGN KEY(ArticleId)
REFERENCES dbo.Article (Id)
GO
ALTER TABLE dbo.ArticleLanguage CHECK CONSTRAINT FK_ArticleLanguage_Article
 
 
CREATE TABLE dbo.ArticleListing(
    Id int IDENTITY(1,1) NOT NULL,
    TransactionGuid varchar(40) NULL,
    TransactionDate datetime NULL CONSTRAINT DF_ArticleListing_TransactionDate  DEFAULT (getdate()),
    LanguageId int NULL,
    ArticleId int NULL,
    ArticleLanguageId int NULL,
    Name varchar(100) NULL,
    Position int NULL,
    StatusId int NULL
 CONSTRAINT PK_ArticleListing PRIMARY KEY CLUSTERED 
(
    Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
 
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Returns Article and ArticleLanguage listing in one table
-- =============================================
CREATE PROCEDURE dbo._ArticleListing_GetListing
(
    @LanguageId int,
    @ArticleId int = null,    
    @StatusId int = null
)
AS
BEGIN
 
    --Delete old records
    DELETE FROM ArticleListing WHERE TransactionDate < DATEADD(minute, -2, GETDATE())
 
    --Create new GUID for this transaction
    DECLARE @TransactionGuid varchar(40)
    SET @TransactionGuid = NEWID()
 
    --Insert from Article
    INSERT INTO ArticleListing
    (
            TransactionGuid,
            ArticleId,
            Position,
            StatusId
    )
    SELECT
            @TransactionGuid,
            Id,
            Position,
            StatusId
    FROM    Article
    WHERE (@ArticleId IS NULL OR Id = @ArticleId) AND
                (@StatusId IS NULL OR StatusId = @StatusId)
 
    --Update from ArticleLanguage
    UPDATE  ArticleListing SET
                    ArticleListing.LanguageId = ArticleLanguage.LanguageId,
                    ArticleListing.ArticleLanguageId = ArticleLanguage.Id,
                    ArticleListing.Name = ArticleLanguage.Name
    FROM        ArticleLanguage LEFT JOIN
                    ArticleListing ON ArticleListing.ArticleId = ArticleLanguage.ArticleId
    WHERE        TransactionGuid = @TransactionGuid AND
                    ArticleListing.ArticleId = ArticleLanguage.ArticleId AND
                    ArticleLanguage.LanguageId = @LanguageId AND
                    (@ArticleLanguageId IS NULL OR ArticleLanguage.Id = @ArticleLanguageId)
 
    --Delete not valid records
DELETE FROM ArticleListing WHERE TransactionGuid = @TransactionGuid AND 
(LanguageId IS NULL OR ArticleLanguageId IS NULL)
 
    SELECT    Id,
                    TransactionGuid,
                    TransactionDate,
                    LanguageId,
                    ArticleId,
                    ArticleLanguageId,
                    Name,
                    Position,
                    StatusId
    FROM        ArticleListing
    WHERE        TransactionGuid = @TransactionGuid
    ORDER BY Position
END
 

and exception...

Message: Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 
StackTrace: 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.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() 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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at MyProject.Data.Utility.ExecuteReader(Database database, DbCommand dbCommand) at
MyProject.Data.SqlClient.SqlArticleListingProviderBase.GetListing(TransactionManager transactionManager, Int32 start, Int32 pageLength, Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
MyProject.Data.Bases.ArticleListingProviderBaseCore.GetListing(Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
UserControls_ArticleGrid.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)