PHP and Concurrency

Question

I've been doing some web development work in PHP recently which has led me to study up on the language in general. So far I have not needed to use it to interact with a database, but I know it provides a lot of convenient functions for doing so.

Although I know basic SQL and have worked with basic manipulation of data in a database, I don't understand how web developers who base their websites around PHP/Javascript/SQL are able to manage users modifying the same data at the same time.

For example, lets say you have a blackjack website which divides users into one of two teams when they sign up. Every time a user wins a game, a portion of their winnings is added to a running total for that team.

So lets say the pseudo code for the function that does this looks something like this:

... 
$total = mysql_query("SELECT score FROM team1");
$total = $total + $mytotal;
mysql_query("UPDATE team1 SET score='".$total."'");
...

If two players are playing at the same time, it's very possible that they will both call SELECT before the other one has a chance to increment and update the table, so one users changes will be immediately overwritten.

My question is, how does one avoid this? Is it done using PHP at the code level, or are there features provided by whatever database you are using that help to prevent this?

I've been doing some research, and it seems that PHP does provide a semaphore mechanism, and I've also noticed that mysql offers a LOCK table feature. However, I'm not sure which of these, if either, is used in practice.

Answer 1

Keep the logic at the DB, those semantics have mostly been solved for you.

update teams
set score = score + 1
where team_id = 1

.. or whatever score and whatever team number.