"Go" records an incident in PHP, MySQL in high concurrency scenarios

Read a blog, feel the work is worth learning, the original text as follows:

Accident description

In a project, on-line a new function, a continuous customer service to our response, there are users of individual props up to 4.2 billion, but there has been no evidence that this is, does exist, and intuition tells us that it is impossible, has not cared about, Until then really found a user is really 4.2 billion, then our entire company was shocked, if there are a large number of users is such a situation, the company to lose hundreds of thousands of, our boss told us, it must be what the data overflow, and finally we a bunch of people, crazy look code, found ...

If the normal program logic goes on, the code is completely no problem, but I found a place, if in high concurrency, is going to get out of the wrong program logic, why I saw, very simple, I am in a company, because this problem troubled me for one months, really unforgettable! Not much to say, said more is a tear! On the code, reproduce the scene (here are some simple code to reproduce the scene), the name of the item is defined as props.

<?phpmysql_connect ("localhost", "Mysql_user", "Mysql_password"); mysql_select_db ("user"); $consume _props_count = 10; Number of props to consume $select = ' select ' Count ' from ' user ' where ' userid ' =123456 '; $query = mysql_query ($select); $row = Mysql_fetch_a SSOC ($query);//Compare the current item quantity if ($row [' Count '] < $consume _props_count) {return false;} Number of items deducted $update = "Update ' user ' Set ' count ' = ' count '-{$consume _props_count}"; $query = mysql_query ($update); return Mysql_num_rows ($query);? >

As you can see, if you follow the normal process of deducting props to walk, this is not a problem, but in high concurrency scenario, two times the lock props query very likely to get the same result , and then they can Compare the current number of props this step logic , but the first time to add props to the operation of the props buckle, or buckle enough to continue to buckle for the second time, think of what will happen!

The Pit Daddy! The second time will be deducted the number of items negative.

But that still doesn't explain the 4.2 billion overflow there! Alas, the old saying that has come true again and again, our count field data type is Unsigned int, pit daddy MySQL, if the field is Unsigned int, and then enter a negative number, it will let this figure into 4.2 billion of this huge value.

See what you got! That's 4.2 billion of the way, pit!

Solution, you can change the SQL in the update

<?php$update = "Update ' user ' SET ' count ' = (case is ' count ' <={$consume _props_count} then 0 ELSE ' Count '-{$consume _ Props_count} END) WHERE ' userid ' =123456 '?>

In this way, you will not buckle to a negative number, in addition to the case, but also to the unsigned int field type, to int.

Excerpt from: http://h5b.net/php-mysql-high-concurrency-accident

"Go" records an incident in PHP, MySQL in high concurrency scenarios