防止MySQL和PHP重复发送记录

Question

我有一个名为ad_banner_queue的表，用于根据广告权重生成队列。广告被插入到广告表中。如果所有现有的在队列中的广告都已发送给用户，则会生成队列。

现在的问题是，如果同时收到多个请求且Rand()返回相同的记录，应该如何防止发送重复的广告？

以下是代码：

<?php
/* To Get the random Ad */
public function getBanner($params) {
    /* Fetch the Random from table */
    $ads_queue = (new \yii\db\Query())
            ->select('ad_quque_id, banner_image, unique_code')
            ->from('ad_banner_queue')
            ->join('inner join', 'advertisement', 'ad_banner_queue.ad_id = advertisement.ad_id')
            ->where('is_sent=0')
            ->orderBy('RAND()')
            ->one();

    /* In case of queue is not there generate the new queue */
    if ($ads_queue === false) {
        $output = $this->generateAdQueue();
        //In case of something went wrong while generating the queue
        if ($output == false) {
            return array();
        }

        //Now fetch the record again
        $ads_queue = (new \yii\db\Query())
                ->select('ad_quque_id, banner_image, unique_code')
                ->from('ad_banner_queue')
                ->join('inner join', 'advertisement', 'ad_banner_queue.ad_id = advertisement.ad_id')
                ->where('is_sent=0')
                ->orderBy('RAND()')
                ->one();
    }

    /* Now, marked that one as is_sent */
    Yii::$app->db->createCommand()->update('ad_banner_queue', ['is_sent' => 1], 'ad_quque_id =:ad_quque_id', array(':ad_quque_id' => $ads_queue['ad_quque_id']))->execute();
    return $ads_queue;
}

/**
 * Below will Generate the Queue if not exist
 */
public function generateAdQueue() {
    /* First check thatt there is existing queue, if so don't generate it */
    $data_exist = (new \yii\db\Query())
            ->select('ad_quque_id')
            ->from('ad_banner_queue')
            ->where('is_sent=0')
            ->scalar();
    if ($data_exist === false) {
        /* Delete all other entries */
        (new \yii\db\Query())
                ->createCommand()
                ->delete('ad_banner_queue')
                ->execute();

        /* Fetch all banner */
        $ads = (new \yii\db\Query())
                ->select('ad_id, unique_code, ad_name, banner_image,ad_delivery_weightage')
                ->from('advertisement')
                ->where('status_id in (8)') //Means only fetch Approved ads
                ->all();
        if (!empty($ads)) {
            foreach ($ads as $ad) {
                /* Make entry as per that weightage, example, if weightage is 10 then make entry 10 times */
                $ins_fields = array();
                for ($i = 1; $i <= $ad['ad_delivery_weightage']; $i++) {
                    $ins_fields[] = array($ad['ad_id']);
                }
                Yii::$app->db->createCommand()->batchInsert('ad_banner_queue', ['ad_id'], $ins_fields)->execute();
            }
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

- DS9

---

建议您实现一种“故障安全”解决方案，即向相关表格添加唯一索引。这样，在数据库层面上可以确保重复插入失败。此外，除了这个方法，可能（也可能不）可以在PHP端采取措施以减少这种情况发生的可能性。 - Tim Biegeleisen

实际上，我不想将错误发送给用户。他们应该收到广告。但是我想防止向不同的用户发送相同的广告。在生成队列时也适用相同的规则。 - DS9

你上面给出了近100行代码，需要记住我们不知道你的应用程序。 - Tim Biegeleisen

1/ 添加一些带有唯一索引的ad_user表，该索引基于id_ad + id_user +某个参数“date_sending_ad”。 2/ 在发送广告之前，请检查此用户是否已收到过此广告的电子邮件。 3/ 如果您认为可以再次发送此广告，因为足够的时间已经过去，请再次发送并更新“date_sending_ad”列。 - Mickaël Leger

代码写得不错，讨论和答案也很好，但是：这种情况发生的可能性有多大？（非常之小...甚至不值得为广告进行调查...） - xerx593

Answer 1

我理解您的意思是，同时进行请求的不同“人”不应该得到相同的随机行？为了避免在两个运行请求中选择相同记录的微小可能性，最可靠的方法（未经测试）是锁定表并在事务中执行读取和更新。您必须使用支持此功能的存储引擎，例如InnoDB。

使用事务表（如InnoDB表）完成LOCK TABLES和UNLOCK TABLES的方法是使用SET autocommit = 0开始事务，而不是START TRANSACTION，然后使用LOCK TABLES。然后，直到显式提交事务之前，不应调用UNLOCK TABLES。

例如，如果您需要一次读取和写入表格，可以这样做：

SET autocommit = 0;
LOCK TABLES ad_banner_queue AS ad_banner_queue_w WRITE, ad_banner_queue AS ad_banner_queue_r READ;
... perform your select query on ad_banner_queue_r, then update that row in ad_banner_queue_w with is_sent = 1...
COMMIT;
UNLOCK TABLES;

我们使用别名锁定的原因是，您不能在同一查询中多次使用相同名称引用已锁定的表。因此，我们使用别名来获取表和每个别名的单独锁。

- inquam

---

呵呵，有趣的黑客技巧 ;) - Félix Adriyel Gagnon-Grenier

Answer 2

尽管这似乎是一个琐碎的问题，但它并不是。有几种方法可以处理它，每种方法都有其自身的缺点，主要有三个不同的方面需要考虑：

忍受它

在现实生活中，您很少会遇到重复的拉取机会，您需要认真思考是否愿意面对额外的工作，只是为了确保广告不会连续两次显示。此外，您还需要考虑缓存的存在，您可能会因为使广告原子而折磨自己，最后发现浏览器/代理/缓存正在提供重复的广告:(

在数据库中处理它

您可以通过让数据库负责保持数据安全和一致来处理此问题（实际上这是数据库的主要任务），有几种方法：

• 锁和表（如先前建议的那样），我个人不喜欢使用PHP和MySQL中的锁定方法，您将遭受性能惩罚并冒着死锁风险，但无论如何，这仍然是一种解决方案，您只需在队列表上进行选择以更新，以确保没有人再次读取，直到您更新为止。问题在于，在此过程中，您将锁定整个表，并且您需要小心处理DB Driver和autocommits。
• 游标 游标是基本上为您要执行的任务创建的数据库结构，您可以使用其功能安全地遍历它。由于事务，将游标用于PHP可能非常棘手，您需要非常了解自己在做什么，以避免出错。
• 游标和存储过程 在数据库中处理此问题的最佳方法是在数据库内部管理游标，这就是为什么存储过程存在的原因，只需创建过程以从游标中拉取新项，并在全部消耗完毕后再次填充它。

在PHP端处理它

在这种情况下，您需要在PHP上实现自己的队列，可能有几种方法可以实现，但主要问题可能是在应用程序上实现多进程安全的原子操作，如果您不确定应用程序的执行流程，我个人不喜欢使用任何类型的锁定，否则您可能最终会将其全部锁定。无论如何，在这里有三个机会：

• 使用 sems 或 mutex，它们都包括在 PHP 或第三方中。超时和锁定可能会变成一场噩梦，并且很难检测，因此我建议避免使用。

• 使用 PHP MSG Queue，我认为这是最安全的方法，只要您在 * nix 系统上运行您的应用程序，就将所有可用的广告发送到消息队列中，而不是在数据库上创建表格，一旦所有广告被消费，您可以再次重新生成队列。这个系统的缺点是您的服务器不能分布式，并且如果您在重新启动之前没有保存当前队列状态，您可能会失去当前的队列状态。

• 第三方队列系统，根据您的应用程序工作负载或互动，您可能需要使用队列管理系统，如果要使用分布式系统，则必须使用此系统，使用消息队列系统来处理此问题可能听起来太严重，但这种方法可能是救命稻草。

总结

如果您无法忍受并且对数据库有足够的熟练掌握，那么可以使用存储过程和游标，您无需为并发性而苦恼，只要使用符合 ACID 的数据库即可（例如不要使用 MyISAM）。

如果您想避免编码到数据库中，并且您的系统是 * nix 并且不会分布式，可以尝试使用 msg_queues。

如果您认为您的系统可能有时是分布式的或不依赖于旧的 SysV 机制，则可以尝试使用消息代理，如 RabbitMQ，这些好东西非常令人着迷，一旦开始使用它们，您就会每天看到它们的新用途。

- L. Amigo

---

你可以使用Innodb进行行级锁定。 你也可以使用GET_LOCK（）获取命名锁，而不锁定表格。对于恰好此情况非常有用。 - jhaagsma

就像我之前说的，锁是一种解决方案，只是我不会用它。当并发访问的数据不同时，行级锁定是很好的选择，这样当我锁定A行时，你可以获取B行，但在这种情况下，你正在选择所有可用的广告，因此你正在锁定所有可用的广告，任何并发进程想要获取所有可用的广告，所以无论你锁定一个行还是整个表，第二个进程都会被锁定，直到你完成为止。另外，你希望防止第二个进程读取相同的广告，因此共享锁定也没有机会。 - L. Amigo

另一个问题是在选择操作中有一个连接，没有涉及到单个表，当然可以重新设计为选择更新查询，仅涵盖一个表并检索仅1条记录以最小化被锁定的机会。 - L. Amigo

公平；那个广告上的命名锁不应该起作用吗？在X时间内对广告/用户进行锁定，如果不能立即获得锁定，则选择新的锁定？ - jhaagsma

Answer 3

您可以使用互斥锁组件来确保只有一个进程尝试从队列中弹出广告。

$banner = [];
$key = __CLASS__ . '::generateAdQueue()' . serialize($params);
if (Yii::$app->mutex->acquire($key, 1)) {
    $banner = $this->getBanner($params);
    Yii::$app->mutex->release($key);
}

然而请注意，这可能会大大降低性能，特别是如果您想同时处理多个请求。您可以考虑使用不同的技术来解决此类队列问题，关系型数据库并不是非常适合此任务。使用基于Redis的队列和SPOP可能是更好的选择。

- rob006

Answer 4

很可能广告是从单独的页面呈现的。HTML是“无状态”的，因此您不能指望一个页面知道先前显示过哪些广告。因此，您要么需要在页面之间传递此信息，要么将其存储在与各个用户相关联的数据库中。您还希望进行一些随机化操作？让我们同时完成这两件事。什么是“状态”？有一个“初始状态”，此时您会随机选择第一个要显示的广告。然后将该信息传递到下一页（在URL、cookie或数据库中）。另一种“状态”会查看先前的状态并计算要显示的下一条广告。（最终，您需要担心广告用尽的问题——您会重新开始吗？您会重新随机化吗？等等。）但是如何避免连续两次显示相同的“随机”广告呢？您有N个广告——SELECT COUNT(*)...，您选择了第J个作为要显示的第一个广告——简单应用RAND()，可以在SQL或应用程序中执行。选择一个数字M，使得M和N是“相对质数”。下一个广告是号码(J := (J + M) mod N)。这将循环遍历所有广告，不重复显示，直到所有广告都显示完毕。再次强调，这可以在SQL或应用程序中完成。从一页传递信息到下一页。要获取第J行：要么将行唯一且连续编号，要么使用ORDER BY ... LIMIT 1 OFFSET J。（注意：将J填入SQL可能会比较棘手。）没有表锁，没有互斥体，只需从一页传递信息到下一页。

- Rick James

Answer 5

您可以使用事务和SELECT FOR UPDATE语句来锁定数据并保证查询的一致性执行。例如：

public function getAds()
{
    $db = Yii::$app->db;
    $transaction = $db->beginTransaction(Transaction::REPEATABLE_READ);
    try {
        $ads_queue = (new \yii\db\Query())
            ->select('ad_quque_id, banner_image, unique_code')
            ->from('ad_banner_queue')
            ->join('inner join', 'advertisement', 'ad_banner_queue.ad_id = advertisement.ad_id')
            ->where(new Expression('is_sent=0 FOR UPDATE'))
            ->orderBy('RAND()')
            ->one();
        if ($ads_queue === false) {
            $transaction->commit();
            return null;
        }
        $db->createCommand()->update('ad_banner_queue', ['is_sent' => 1], 'ad_quque_id =:ad_quque_id', array(':ad_quque_id' => $ads_queue['ad_quque_id']))->execute();      
        $transaction->commit();
        return $ads_queue;
    } catch(Exception $e) {
        $transaction->rollBack();
        throw $e;
    }
}

public function getBanner($params)
{
    $ads_queue = $this->getAds();
    if (is_null($ads_queue)) {
        $output = $this->generateAdQueue();        
        if ($output == false) {
            return array();
        }
        $ads_queue = $this->getAds();
    }
    return $ads_queue;
}

- Maksym Fedorov

Answer 6

你应该创建一个单独的数据库表，并在用户通过它接收到广告时进行标记。在向用户发送广告之前，请检查他是否已经收到过。

- bbrody

---

你仍然可能会遇到竞态条件，即当你从广告表中读取信息时，第二个用户请求相同的广告并获得该广告时，你正要将该信息写入辅助表。为了确保这种情况不会发生，你需要锁定表并将操作作为事务执行。 - inquam

Answer 7

让你的索引变成唯一的，或者添加一个检查来检查数据并查看是否为重复项。

希望这可以帮到你。祝好运。

- Moubarak Hayal