PHP and MySQL: Why does this work without blocking?

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:

• Get an identifier from officeRechNr from the current year and month

• Increase id by one and update in officeRechNr

• Echo increased id

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

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

I suggested that this behaves differently when I execute a parallel script.

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 ran both scripts at the same time, so I assumed that $ number should be 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 result was

New id is 3
New id is 4

, , mysql php.

? script - ?

Answer 1

, - .

, script , .

:

• 1.
• 2.
• - 1 .
• - .
• 1 "3" .
• Webserves 2. , ( ).
• 1 "4" .
• Webserver 1 .
• , - , , 2 .
• 2 "4" .
• 2 "5" .
• 2 .

, - , , script .

(AUTO_INCREMENT MySQL, SERIAL PostgreSQL ..).

Answer 2

, , .

, .

:

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

:

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

, , print_r($times) , , .

, PHP. :

- , PHP , .

Answer 3

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

In addition, you do not need a lock at all. you can use

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

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