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).

Answer 2

Adam, it seems that your queries are not executing in parallel.

Record times at crucial events and print them to confirm this.

After the first statement execute:

$stmt = $db->prepare('SELECT zahl ...');
...
$stmt->execute();
$times['select'] = date('H:i:s'); //human-readable format. eg: 11:15:32

Then again after the value is updated at the server:

$stmt = $db->prepare('UPDATE officeRechNr ...');
...
$stmt->execute();
$times['update'] = date('H:i:s');

I suspect we'll find out if you do print_r($times) for each query that the queries were not happening in parallel, but in sequence.

This is definitely the case if you're using the built-in PHP server. The docs say:

The web server runs a only one single-threaded process, so PHP applications will stall if a request is blocked.

Answer 3

If you want locking but not full-table locking, try using SELECT...FOR UPDATE in a transaction to lock just the rows you select (assuming you have an index on jahr, monat). See http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

Alternatively, you don't need locking at all. You could use

UPDATE officeRechNr set zahl = LAST_INSERT_ID(zahl+1) WHERE jahr = ? and monat = ?

Read the docs of http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id for details.