PHP & MySQL: Why is this working without lock?

Question

I am using InnoDB and have the following table

officeRechNr

year  | month  | id   | 
------------------------
2016  | 7      | 2    | 
2016  | 6      | 5    |
2016  | 5      | 6    |

My script works as follows:

1. Get id from officeRechNr from current year and month

2. Increase id by one and update in officeRechNr

3. Echo increased id

So if the script would be executed one after another, I would expect:

New id is 3
New id is 4
New id is 5

I assumed that this behaves differently when I execute the script parallel.

Here is my script:

 $db = new mysqli("localhost","user","pass","db");

 $year     = date("Y");
 $month    = date("m");

 $stmt         = $db->prepare('SELECT zahl FROM officeRechNr WHERE jahr = ? AND monat = ?');

 $stmt->bind_param('ii', $year, $month);
 $stmt->execute();

 $result            =   $stmt->get_result();
 $row           =   $result->fetch_assoc();

 $number       =  $row['zahl'] + 1;
 sleep(20);

 $stmt         = $db->prepare('UPDATE officeRechNr set zahl = ? WHERE jahr = ? and monat = ?');
 $stmt->bind_param('iii',$number, $year, $month);
 $stmt->execute();
 echo "New id is $number";

I executed both scripts simultaneously, so I assumed $number must be equal to 3 for both scripts and then they sleep for 20 seconds. Therefore I would expect

New id is 3
New id is 3

to my surprise, the outcome was

New id is 3
New id is 4

I thought I need to write-lock the database to achieve this result, as explained in Lock mysql table with php.

Why did I not get the expected outcome? Does this script always return different id's - even if executed at exact same time?

Answer 1

It seems like your webserver is unable to execute more than one request at a time.

What this means, in practice, is that even if you open your script in two different times, the server will not execute the second request before the first one is finished.

The timeline looks like this:

• You open tab 1.
• You open tab 2.
• Webserver receives the query from tab 1, and starts executing it.
• Webserver starts executing it.
• Tab 1 reads "3" from the DB.
• Webserves receives the query from tab 2. However, it doesn't have any free worker, so it delays its execution (puts it in a waiting list).
• Tab 1 stores "4" in the DB.
• Webserver completes the execution of your tab 1 query, and returns the result.
• Now that the webserver has nothing to do, it looks at its waiting list, finds the query from tab 2, and starts executing it.
• Tab 2 reads "4" from the DB.
• Tab 2 stores "5" in the DB.
• It completes of the tab 2 query and returns the result.

Of course, in a production environment, a real webserver will execute multiple requests at once, which means your script is very unsafe.

Prefer using your database's builtin solutions for this (AUTO_INCREMENT for MySQL, SERIAL for PostgreSQL, etc).