Geek Answers Handbook

Update account (field) safely in SQL

I want to implement SO as 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 mail and many tags that it can use.

Here's the problem: how to get a current account, increase or decrease it by one and keep it SAFE. So no other connection / thread will update it between the time I select and update?

+5
source share
3 answers

I assume that you also want a new score, otherwise wise is not a problem, just set the count = count + 1 set.

db UPDATE (, SQL Server 2K5 2K8):

UPDATE table
   SET count = count + 1
   OUTPUT inserted.count
   WHERE id=@id;

:

begin transaction
update table 
    set counter=counter+1
    where id=@id;
select counter
    from table
    where id=@id;
commit;
+13

SET Count = Count + 1 IMHO .

, , , .

, . , concurrency .

, - / , -, , .

SELECT Count AS old... FROM...

.. ...

UPDATE... SET Count = oldplus1 WHERE Count = old AND...

UPDATE , , , , .

0

pseudo code:

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 following :)

-2
source

More articles:


All Articles