PHP& MySQL:为什么没有锁就可以工作? [英] PHP & MySQL: Why is this working without lock?

问题描述

我正在使用InnoDB并具有下表

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. 从 officeRechNr 获取当前年和月

将 id 加1，并在 officeRechNr

回声增加了 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.

这是我的剧本:

 $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";

我同时执行了两个脚本，所以我假设两个脚本的$ number必须等于3，然后它们休眠20秒.因此，我希望

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

令我惊讶的是，结果

New id is 3
New id is 4

我认为我需要对数据库进行写锁定才能实现此结果，如锁定中所述带有php的mysql表.

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

为什么我没有得到预期的结果?该脚本是否总是返回不同的ID-即使在完全相同的时间执行?

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

推荐答案

如果要锁定而不是全表锁定，请尝试在事务中使用SELECT...FOR UPDATE来仅锁定所选行(假设您具有索引)贾尔(monat).参见 http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

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 = ?

阅读 http:的文档://dev.mysql.com/doc/refman/5.7/zh-CN/information-functions.html#function_last-insert-id 了解详情.

这篇关于PHP& MySQL:为什么没有锁就可以工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！