在PHP中锁定MySQL INNODB行

问题描述

我有一个名为meta的表，其中有两列name和value.

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 SET value=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行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！