laravelで更新時ロックをかける

laravelで更新時ロックをかける際、レコードロックとテーブルロックでは書き方が異なる・両者を同時に使えない。

動作検証 ： laravel 10 / mysql

トランザクション

レコード更新時にロックをかけ、更新する。トランザクション内で実行する。

↓DBファザードのtransactionメソッドを利用。コミットやロールバックが自動で行われる。

$params = ['studentId' => 'student001', 'studentName' => 'てすと花子'];
DB::transaction(function () use ($params) {
    // 指定の生徒を取得・ロックする★
    // 該当の生徒の選択講座のテーブルをロックする★★

    // レコード更新する
}, 3);

対象レコードの専有ロック ★

• ある生徒レコードに対しての専有ロック。トランザクションが終わるまで参照更新不可。

↓ Model（クエリビルダ）利用の場合。OK、ロックかかる

DB::transaction(function () use ($params) {
    // 指定の生徒を取得・ロックする
    $student = Student::where('student_id', $params['studentId'])->lockForUpdate()->first();
    // 指定の生徒を更新
    $student->student_name = $params['studentName'];
    $student->save();
}, 3);

↓ DBクラス（SQLクエリ）利用の場合。OK、ロックかかる

DB::transaction(function () use ($params) {
    // 指定の生徒を取得・ロックする
    $sql = "SELECT * FROM students WHERE student_id = :studentId FOR UPDATE";
    $result = DB::select($sql, ['studentId' => $params['studentId']]);
    // 指定の生徒を更新
    $sqlu = "UPDATE students SET student_name = :studentName WHERE student_id = :studentId";
    $resultu = DB::update($sqlu, $params);
}, 3);

テーブルロック ★★

• ある生徒の、選択講座複数レコードの追加・更新・削除処理。
  講座の予約が満席か等の参照も、件数が変動するため処理が終わるまでは不可。テーブルロックとする。
• Eloquentのクエリビルダを使う方法はなさそう。生クエリ実行する。

（参考）
https://qiita.com/zackey2/items/079128df6567091b8a1c

↓ OK、テーブルロックかかる

DB::transaction(function () use ($params) {
    // 自動コミットをOFFにする
    DB::unprepared('SET AUTOCOMMIT = 0');
    // 選択講座テーブルをロックする
    DB::unprepared('LOCK TABLES selected_lectures WRITE');
    // 選択講座の追加・更新・削除
    //（略）
}, 3);

「対象レコードの専有ロック＋テーブルロック」は エラーになる

上記2つ、対象生徒のレコードと、その生徒の選択講座テーブル を同時にロックする。
単純に2つを併記すると・・NG、エラーになった。

$param = [];
DB::transaction(function () use ($params) {
    DB::unprepared('SET AUTOCOMMIT = 0');
    // 選択講座テーブルをロックする
    DB::unprepared('LOCK TABLES selected_lectures WRITE');

    // 指定の生徒を取得・ロックする★
    $sql = "SELECT * FROM students WHERE student_id = :studentId FOR UPDATE";
    $result = DB::select($sql, ['studentId' => $params['studentId']]);

    // 指定の生徒を更新
    $sqlu = "UPDATE students SET student_name = :studentName WHERE student_id = :studentId";
    $resultu = DB::update($sqlu, $params);

    // 選択講座の追加・更新・削除
    //（略）
}, 3);

DB::update()で、studentsがロックされてないと言われる。★が効いていない模様
SQLSTATE[HY000]: General error: 1100 Table 'students' was not locked with LOCK TABLES

複数テーブルをロック：OK

studentsも、テーブルロック指定するとOKだった。
ロック範囲が大きくなるのでstudentsは特定のレコードのみのロックでよいのだが、できないっぽい。

↓ OK、テーブルロックかかる

DB::transaction(function () use ($params) {
    DB::unprepared('SET AUTOCOMMIT = 0');
    // 選択講座テーブル、生徒テーブルもロックする
    DB::unprepared('LOCK TABLES selected_lectures WRITE, students WRITE');

    // FOR UPDATEなしで取得
    $sql = "SELECT * FROM students WHERE student_id = :studentId";
    $result = DB::select($sql, ['studentId' => $params['studentId']]);

    // 指定の生徒を更新
    $sqlu = "UPDATE students SET student_name = :studentName WHERE student_id = :studentId";
    $resultu = DB::update($sqlu, $params);

    // 選択講座の追加・更新・削除
    //（略）
}, 3);

おまけ：自動コミット指定なしのエラー

DB::unprepared('SET AUTOCOMMIT = 0');

がないと、
There is no active transaction
のエラーになる。
DB::update()時点で自動コミット・トランザクションが終了し、DB::transaction()抜ける時にトランザクションがない！ということみたい。

おまけ：ロック中、modelからリンクテーブルを参照するとエラー

予約システムの実コード、モデルを利用した場合にエラーが出た。

 public static function findBystudentId(studentId$studentId, bool $lockForUpdate = false) : ?StudentEntity
  {

    if ($lockForUpdate === true) {
      DB::unprepared('SET AUTOCOMMIT = 0');
       //生徒テーブルと、選択講座テーブルもロックする
      DB::unprepared('LOCK TABLES students WRITE, selected_lectures WRITE');
    }

    $student = Student::where('student_id', $studentId->toString())->first();
    if (!$student) {
      return $student;
    }

    //（略）

    return $student->toEntity();  //★★★
  }

General error: 1100 Table 'lectures' was not locked with LOCK TABLES at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:423)

最後の★★★内の処理で、呼んでいるmodelに講座テーブル（lectures）へのリンクがあったのが原因。
参照しているだけなのに・・・
（リンクしていると保存も可能なのか？だからロックを要求されている？？ 未調査。）

解決方法：LOCK TABLES に 「lectures READ」を追加した。無用なロックが増えた.
modelのリンクなどlaravelは自動で便利な機能も多いけど、条件が重なると不都合が出てくる。仕方ないと言えば仕方ない。
※もしかしたらリンクを無効にする方法があるのかもしれない。未調査

DB::unprepared('LOCK TABLES students WRITE, selected_lectures WRITE, lectures READ');

model:リンクを参照している部分

namespace App\Models;

class SelectedLecture extends Model


    // リンク
    public function lecture()
    {
        return $this->belongsTo(Lecture::class, 'lecture_id', 'lecture_id');
    }


    public function toEntity(): SelectedLectureEntity
    {
        //略

        if ($this->lecture) {　//ココ!!!!!
            $selectedLectureEntity->setLecture($this->lecture->toEntity());
        }
        return $selectedLectureEntity;
    }