mysql - 从库上的行锁定是否也适用于主库？

Question

例如在交易中，

START TRANSACTION;
SELECT count(*) as count FROM `order` WHERE user_id = 25286 LOCK IN SHARE MODE;
INSERT INTO `order` (`id`, `user_id`, `product_id`) VALUES (NULL, '25286', '36296');

我们执行 SELECT LOCK IN SHARE MODE; SELECT 查询将在从数据库上执行并锁定一行。

SELECT LOCK IN SHARE MODE 是否也在 master 数据库上创建行锁定，以便 INSERT 查询不会在 master 数据库上运行？

Answer 1

NO - 锁定不会渗透到整个服务器。所以基本上，从服务器上的显式锁定不会锁定主服务器中的相应表，反之亦然。

我们在案例中使用的一个简单的解决方法是将所有这些特定的锁定查询定向到仅主服务器。因此，无论哪个客户端 session 正在运行，这些查询始终仅在主服务器上运行。因此并发连接问题得到处理。

我在 PHP 代码中做了类似的过滤。为了将查询定向到相关服务器(读取查询到从属服务器，并将查询写入主服务器)，编写了一个自定义函数来识别查询的类型。

值得注意的一点是，事务/锁定/解锁操作中的查询始终被视为写入 查询。

此外，对于 Set，只有 SET AUTOCOMMIT 和 SET TRANSACTION 是 Write 命令。

请在下面找到我们正在使用的实际代码的大幅简化版本:

/*
* All the WRITE operation commands
*/
$write_commands = array(
    'create', 
    'alter', 
    'drop', 
    'truncate',
    'comment', 
    'rename', 
    'insert', 
    'update',
    'delete', 
    'merge', 
    'call', 
    'lock', 
    'unlock',
    'start', 
    'commit', 
    'rollback', 
    'savepoint',
    'set', 
    'replace' 
);


/*
* method to determine whether Read or Write
* @param $sql String (SQL query string)
* @return: void
*/
function determineReadOrWrite(string $sql): void {

    $dml_query = false;

    $words = str_word_count(strtolower(trim($sql)), 1);
    $first_word = isset($words[0]) ? $words[0] : '';
    $second_word = isset($words[1]) ? $words[1] : '';

    if (in_array($first_word, $this->write_commands)) {
        /* if it is not "set" then we set to master link */
        if ($first_word !== 'set'
        || ($first_word === 'set' && $second_word === 'autocommit')
        || ($first_word === 'set' && $second_word === 'transaction')
        ) {
            $dml_query = true;

            /* If we Lock tables or Begin a Transaction, we should run on Write servers only */
            /* till we Commit/Rollback or Unlock Tables */
            if(($first_word === 'start' && $second_word === 'transaction') 
            || $first_word === 'lock'){

                /* Set whether the current query is starting a Transaction / Lock etc */
                $this->wait_for_commit_rollback = true;
            }

            /* We are doing Commit/Rollback or Unlock Tables */
            if ($first_word === 'commit' 
            || $first_word === 'rollback' 
            || $first_word === 'unlock') {
                $this->wait_for_commit_rollback = false;
            }
        }
    }

    /* It's a insert/update/delete/etc query - to be run on Write Db only */
    if ($dml_query || $this->wait_for_commit_rollback) { 
        $this->setActiveConnectionToWrite(true);            

    } else {
        $this->setActiveConnectionToRead();
    }

}