Integration of PHP and database queue

With the development of modern web applications, more and more tasks need to be processed asynchronously to improve website performance and user experience. One common way is to use a queue system to queue up the tasks that need to be processed, and then process them asynchronously by a background process. Both PHP and databases are widely used tools in web development, so using them together can achieve a simple and easy-to-maintain queuing system.

This article will introduce how to use PHP and a database to implement a simple queue system, including how to add tasks to the queue, how to process tasks asynchronously, and how to ensure the reliability of tasks.

1. The basic principle of database queue

The basic principle of database queue is to create a task list in the database, and then use the transaction mechanism of the database to ensure the stability of concurrent access. When a task needs to be added, the task information is first inserted into the task list and a database transaction is started. In a transaction, first query whether there is a task being processed in the task list. If not, the first task in the queue will be processed as the current task. If there are tasks being processed, commit the transaction and wait for the next polling cycle.

2. Create a task table

First, you need to create a task table, including fields such as task id, task type, task parameters, and task status. Among them, the task status can be waiting for processing, processing, processed, failed, etc. The sample code is as follows:

CREATE TABLE queue (
id int(11) NOT NULL AUTO_INCREMENT,
type varchar( 50) NOT NULL,
params text NOT NULL,
status tinyint(4) NOT NULL DEFAULT '0',
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Add tasks to the queue

You can use the following code to add tasks to the queue Add to the queue:

<?php
function addToQueue($type, $params) {
    $dbh = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
    $sql = "INSERT INTO `queue` (`type`, `params`, `status`) VALUES (:type, :params, 0)";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':type', $type, PDO::PARAM_STR);
    $stmt->bindParam(':params', $params, PDO::PARAM_STR);
    $stmt->execute();
}

4. Process the tasks in the queue

In another script, you need to poll the tasks in the queue regularly to process the tasks waiting to be processed.

<?php
function processQueue() {
    $dbh = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
    $dbh->beginTransaction();

    // 查询是否正在处理任务
    $sql = "SELECT * FROM `queue` WHERE `status` = 1 FOR UPDATE";
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $currentTask = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$currentTask) {
        // 如果没有正在处理的任务，从队列中取出第一个任务
        $sql = "SELECT * FROM `queue` WHERE `status` = 0 ORDER BY `id` ASC LIMIT 1 FOR UPDATE";
        $stmt = $dbh->prepare($sql);
        $stmt->execute();
        $currentTask = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($currentTask) {
            // 标记任务为正在处理
            $sql = "UPDATE `queue` SET `status` = 1 WHERE `id` = :id";
            $stmt = $dbh->prepare($sql);
            $stmt->bindParam(':id', $currentTask['id'], PDO::PARAM_INT);
            $stmt->execute();
        }
    }

    if ($currentTask) {
        // 处理当前任务
        try {
            if ($currentTask['type'] == 'example') {
                // 异步处理任务
                // ...
                // 标记任务为已完成
                $sql = "UPDATE `queue` SET `status` = 2 WHERE `id` = :id";
                $stmt = $dbh->prepare($sql);
                $stmt->bindParam(':id', $currentTask['id'], PDO::PARAM_INT);
                $stmt->execute();
            }
        } catch(Exception $e) {
            // 标记任务为失败
            $sql = "UPDATE `queue` SET `status` = 3 WHERE `id` = :id";
            $stmt = $dbh->prepare($sql);
            $stmt->bindParam(':id', $currentTask['id'], PDO::PARAM_INT);
            $stmt->execute();
        }
    }

    $dbh->commit();
}

5. Ensure the reliability of the task

In order to ensure the reliability of the task, you can use transactions to process tasks, and put the status update operations of the tasks together with the business operations in the transaction to ensure When task processing fails, the transaction can be rolled back to avoid incomplete task processing.

6. Conclusion

Using PHP and database to implement a queue system is a simple and reliable method that can effectively improve the performance and user experience of web applications. Since PHP and database are both widely used tools, they can be easily used together to implement asynchronous task processing. However, in actual applications, there are many other advanced queue technologies that can be used, such as Redis queue, message queue, etc. Developers can choose the queue solution that suits them based on specific needs.

The above is the detailed content of Integration of PHP and database queue. For more information, please follow other related articles on the PHP Chinese website!