Best Practice for Page View Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best Practice for Page View Count

Author

Topic

sduffy77
Starting Member

18 Posts

Posted - 2009-03-13 : 10:20:52

hello All,
What is the best practice for keeping a page view count in sql.
Currently:
We have a field named [viewcount] in the page table.
I am doing an update in the sproc that gets the page record where it sets the viewcount = viewcount + 1.
However the issue comes where if multiple users try to view the same page at the same time, we get deadlocks.

Is there a better way that I can be doing this?

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-13 : 10:30:23

you can do something like
select @a = id
from page with (updlock)
where id = @a
or
udpate page
set viewcount = viewcount
where id = @a
to aquire a lock in the beginning. thus you will have only locking w/o deadlocks
If this does not work out - show us the deadlock graph

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

sduffy77
Starting Member

18 Posts

Posted - 2009-03-13 : 12:13:38

quote:
Originally posted by heavymind

you can do something like
select @a = id
from page with (updlock)
where id = @a
or
udpate page
set viewcount = viewcount
where id = @a
to aquire a lock in the beginning. thus you will have only locking w/o deadlocks
If this does not work out - show us the deadlock graph

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

Thanks Vadym,
What exactly is this doing?

quote:
select @a = id
from page with (updlock)
where id = @a
or

sduffy77
Starting Member

18 Posts

Posted - 2009-03-19 : 15:42:13

Any other ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources