I want to implement SO like tags on userpost. I have a table called tag_data with columns tagId, title, count. I have a separate table that links the relationship between a post and the many tags it may use.
Heres the problem, how do i get the current count, increase or decrease it by one and store it SAFELY. So no other connection/thread will update it between the time i do select and update?
I assume you also want the new count, other wise this is a no brainer, just update set count=count+1.
If your db support output clause on UPDATE (eg. SQL Server 2K5 or 2K8):
UPDATE table
SET count = count + 1
OUTPUT inserted.count
WHERE id=@id;
otherwise:
begin transaction
update table
set counter=counter+1
where id=@id;
select counter
from table
where id=@id;
commit;
Pseudocode:
begin transaction
A = select count from tag_data where tagId = TagId
update tag_data set count = A+1 where tagId = TagId
commit
end transaction
I highly recommend making a stored procedure called, say, increment_tag(TagId) that does the above :)
SET Count = Count + 1 is IMHO the easiest solution..
More generally the concept of being able to get data, process it and while its being processed demand there be no underlying changes before writing the results of the processing is ususally not a reasonable one to have if you also require a scalable system.
You can of course do this and in many environments get away with it.. however these approaches will put severe limits on the scalaibility and complexity of an application before concurrency issues render the system unusable.
IMHO the better approach is to take an optimistic route and detect/retry if in the unususal case something you care about did change.
SELECT Count AS old ... FROM ...
.. processing ...
UPDATE ... SET Count = oldplus1 WHERE Count = old AND ...
Unless UPDATE gives you the rowcount your expecting you assume the data was modified and try again until it succeeds.