php - 同时 PHP 数据库查询

Question

假设我有一个函数，用于在特定情况下将数据库中名为 project_id 的列递增 1。 (假设自动递增不会满足此用例，因为在确定是否递增之前必须运行逻辑)。

当用户单击“新建项目”时，该函数会在数据库中找到当前最大的 project_id，执行一些其他操作，并通过将 project_id 递增 1 添加一行。

是否有可能当多个用户连接时，服务器处理其他一些东西所花费的时间导致多个用户选择相同的 project_id？如果是这样，您将如何避免这个问题？以这种方式增加只是一个坏主意吗？

这是一个例子。这个例子是基于 Laravel 的，但是这个问题普遍适用。

function ReallyLongFunction () {
    //figure out what the current max project_id is and increment by 1
    $newProjectId = Project::max("project_id")+1;


    //Some other stuff that the server has to do. I am exaggerating here the length of time.
    //If another user triggers this function, will both users will have the same project id?
    sleep(2);

   //Insert new row into database
   $project = new TempProject;
   $project->project_id = $newProjectId;
   $project->save();
}

Answer 1

在您自己的代码中执行 sql server 可以在内部做得更好的事情总是一个坏主意。 $newProjectId = Project::max("project_id")+1; 听起来很像 auto_increment 的替代品。但是，如果您的要求比这更复杂，则需要 locks

[LOW_PRIORITY] WRITE lock:

The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Lock requests for the table by other sessions block while the WRITE lock is held.

The LOW_PRIORITY modifier affects lock scheduling if the WRITE lock request must wait, as described later.

If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

如果很多用户同时访问同一个页面，响应将开始变慢，因为在第一个请求完成之前无法处理第二个请求。