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.

Answer 2

The database manages concurrent locking and ensures that changes are made atomically. This is a benefit of using a database that supports ACID transactions.

In most cases changes are quick enough that lock contention is minimal. But it can still happen, so you need to check for error status returned after you execute an SQL query. The mysql_query() function returns FALSE if there's a problem.

Then you get to figure out the cause (update conflict, insufficient permissions, SQL syntax error, etc.) by calling mysql_errno(). See http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html for MySQL error code reference.

Answer 3

IF your real world problem is bigger than this simple example, you should use a transaction in conjunction with lock tables to make sure users don't overwrite each other.

Answer 4

I agree with Xepochs answer, don't introduce concurrency concerns unless you absolutely need to. For a great answer regarding different locking strategies see here.

Answer 5

Major databases are designed to handle such situations.

When using MySQL, the InnoDB storage engine has the row-locking feature, which locks the row that is being written to. So, any update request that is for the same row will wait until the previous operation on the row completes.

If you use MyISAM storage engine, that locks the whole table when a row is being updated. That creates a choke on the server when multiple players simultaneously push update requests. So it is all about what database and particularly what storage engine you are using for the purpose.

Answer 6

PHP is not a language designed for multi-threading, and I don't think it ever will be.

If you need mutex functionality, PHP has a Semaphore functions you can compile in.

Memcache has no mutex capability, but it can be emulated using the Memcache::add() method.

If you are using a MySQL database, and are trying to prevent some kind of race condition corruption, you can use the lock tables statement, or use transactions.

You could try pounding on your code with a load test tool that can make multiple requests at the same time. Jmeter comes to mind.