本文介绍了在 PHP 中锁定 MySQL INNODB 行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个名为 meta 的表,有两列 namevalue.

I have a table called meta, with two columns name and value.

在许多客户端同时调用的 php 脚本中,我这样做:-

In a php script, which is called by many clients concurrently, I do this:-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan') LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");

或者这个:-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' <b>FOR UPDATE</b>) LIMIT 1000;UPDATE meta SETvalue=value+1000 WHERE name='scan';");

不幸的是,这似乎不起作用,因为客户端最终会得到重复的 id.数据库负载很重,SELECT 需要几秒钟时间.

Unfortunately, this doesn't appear to work as clients are ending up with duplicate id's. The database is heavily loaded and the SELECT takes a few seconds.

推荐答案

$mysqli->autocommit(FALSE);
$mysqli->query("BEGIN;");
$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' FOR UPDATE) LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");
$mysqli->commit();

这是一个复杂的问题;锁定和事务级别,但上面的魔术是 BEGIN 语句.没有它,每条语句都在自己的事务级别上运行,并且 FOR UPDATE 锁被解锁得太早了.

It's a complex issue; locking and transaction levels, but the magic above was the BEGIN statement. Without it, each statement was running in its own transaction level, and the FOR UPDATE lock was being unlocked too early.

这篇关于在 PHP 中锁定 MySQL INNODB 行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-19 04:56