Please Help Me To Solve Deadlock Problem

Hello,

I am having deadlock problem when I have a lot of visitors on my ASP.NET 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?


Code:


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...



Code:


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)