Query lang.forUpdate

Testing Is Documentation

tests/Database/Query/ForUpdateTest.php

对数据库悲观锁的支持，排它锁和共享锁。

Uses

<?php

use Leevel\Kernel\Utils\Api;
use Tests\Database\DatabaseTestCase as TestCase;

forUpdate 排它锁 FOR UPDATE 查询

第一步事务中加入排它锁未提交

在未提交前，表 test_query 的 tid = 1 行将会锁住，其它查询在这一行数据无法加上排它锁和共享锁，更不能更新改行数据，一直等待直到 commit 或者超时。

BEGIN;
SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 FOR UPDATE;
-- COMMIT;

提交后 commit，其它会正常执行。

排它锁失败

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 FOR UPDATE;

共享锁失败

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 LOCK IN SHARE MODE;

更改失败

UPDATE `test_query` SET `name` = 'hello' WHERE `tid` = 1;

普通查询正常

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1;

public function testForUpdate(): void
{
    $connect = $this->createDatabaseConnectMock();

    $sql = <<<'eot'
        [
            "SELECT `test_query`.* FROM `test_query` FOR UPDATE",
            [],
            false
        ]
        eot;

    self::assertSame(
        $sql,
        $this->varJsonSql(
            $connect
                ->table('test_query')
                ->forUpdate()
                ->findAll(),
            $connect
        )
    );
}

forUpdate 取消排它锁 FOR UPDATE 查询

public function testCancelForUpdate(): void
{
    $connect = $this->createDatabaseConnectMock();

    $sql = <<<'eot'
        [
            "SELECT `test_query`.* FROM `test_query`",
            [],
            false
        ]
        eot;

    self::assertSame(
        $sql,
        $this->varJsonSql(
            $connect
                ->table('test_query')
                ->forUpdate()
                ->forUpdate(false)
                ->findAll(),
            $connect,
            1
        )
    );
}

lockShare 共享锁 LOCK SHARE 查询

第一步事务中加入排它锁未提交

在未提交前，表 test_query 的 tid = 1 行将会锁住，其它查询在这一行数据无法加上排它锁，更不能更新改行数据，但是共享锁是可以的，一直等待直到 commit 或者超时。

BEGIN;
SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 LOCK IN SHARE MODE;
-- COMMIT;

提交后 commit，其它会正常执行。

排它锁失败

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 FOR UPDATE;

共享锁成功

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1 LOCK IN SHARE MODE;

更改失败

UPDATE `test_query` SET `name` = 'hello' WHERE `tid` = 1;

普通查询正常

SELECT `test_query`.* FROM `test_query` WHERE `tid` = 1;

public function testLockShare(): void
{
    $connect = $this->createDatabaseConnectMock();

    $sql = <<<'eot'
        [
            "SELECT `test_query`.* FROM `test_query` LOCK IN SHARE MODE",
            [],
            false
        ]
        eot;

    self::assertSame(
        $sql,
        $this->varJsonSql(
            $connect
                ->table('test_query')
                ->lockShare()
                ->findAll(),
            $connect
        )
    );
}

lockShare 取消共享锁 LOCK SHARE 查询

public function testCancelLockShare(): void
{
    $connect = $this->createDatabaseConnectMock();

    $sql = <<<'eot'
        [
            "SELECT `test_query`.* FROM `test_query`",
            [],
            false
        ]
        eot;

    self::assertSame(
        $sql,
        $this->varJsonSql(
            $connect
                ->table('test_query')
                ->lockShare()
                ->lockShare(false)
                ->findAll(),
            $connect,
            1
        )
    );
}