January 10, 2005 at 10:11 pm
We have a table to manage the hitCounts of visited pages:
CREATE TABLE [dbo].[AccessedWebPage] (
[PageName] [varchar] (50) NULL ,
[HitCount] [int] NULL ,
[LastAccessTime] [datetime] NULL
) ON [PRIMARY]
GO
And we have the fowllowing stored procedure to count the hit numbers:
CREATE PROCEDURE dbo.pageHitCounting
(
@PageName varchar(50),
@LastAccessTime datetime
)
AS
set nocount on
select PageName from AccessedWebPage where ageName=@PageName">PageName=@PageName
if @@rowcount = 0
begin
begin tran
insert into AccessedWebPage(PageName, HitCount, LastAccessTime) values (@PageName, 1, @LastAccessTime)
commit tran
end
else
update AccessedWebPage set LastAccessTime = @LastAccessTime, HitCount = HitCount +1 where PageName = @PageName
GO
The problem is after this stored procedure runs for a while; there are some duplicate web page names in the table AccessedWebPage. This may be because of concurrent running of this SP, and at the same moment they run “select PageName from AccessedWebPage where ageName=@PageName">PageName=@PageName” for the same page and get the null resultset, so they insert the page into the table because there is no primary key for this table. So what should we do to fix this problem and I don't think applying a primary key is a good one because it doesn't address the root issue. Applying row-level lock or setting the isolation level may be the choice but I don't know how to do it.
January 11, 2005 at 1:42 am
hey.
I would user a primary key, but if you want you can look at the locking hints at BOL or here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
