コードを吐き出す処理が正しいか、排他制御など問題ないか確認と質問について

Question

いつもお世話になっております。

・目的
未使用のレコードを1件取得し、使用したレコードを使用済みに更新する

・不安点
排他制御が出来ているか不安。同時に処理が走った時にエラーにならないかどうか

giftsテーブル
+----+----------------+------------------+
| id | condition_code | number |
+----+----------------+------------------+
| 1 | 1 | 1234567890123456 |
| 2 | 1 | 2234567890123456 |
| 3 | 1 | 3234567890123456 |
| 4 | 1 | 4234567890123456 |
| 5 | 1 | 5234567890123456 |
+----+----------------+------------------+

try {
        $dataSource = $this->getDataSource();
        $dataSource->begin();
        $options['conditions'] = [
            'Gift.condition_code' => 1,
        ];
        $options['fields'] = [
            'Gift.id',
            'Gift.number',
        ];

        $results = $this->Gift->find('first', $options);
        $saveFields = [
            'id'       => $results ['Gift']['id'],
            'condition_code' => 2,
        ];

        $this->Gift->set($saveFields);

        if ($this->Gift->save() === false) {
            throw new DbException(['label' => 'Failed to save line_gifts', 'description' => serialize($results ['Gift']['id'])]);
        }
        $dataSource->commit();

        } catch (DbException $e) {
            $dataSource->rollback();

            throw new FatalErrorException($e->getAttributes());
        }

行っていることはまずは未使用状態のnumberを取得(使用はcondition_code=1未使用は2)
そのIDを条件にcondition_codeを2に更新すると言う単純な作業ではあるのですが、
これだと同時にアクセスがあった際に同じnumberを最初のfindで取得してしまう可能性があるのではないかと懸念しています。実際はトランザクション内に外のDBに格納する処理も走っているので不安です。
また2回DBにアクセスしているので、出来れば1回にまとめたいのですが、

UPDATE gifts SET condition_code=2 where id= (SELECT id FROM (SELECT id FROM gifts WHERE condition_code=1 ORDER BY id ASC LIMIT 1) as tmp FOR UPDATE);

おそらくこのSQLであれば私が行いたいことと排他制御を考えたSQLだと思うのですが、これを実現するcakephpの書き方がわからず困っています。

質問としましては
・上記のコードだとやはり複数アクセスがあった時に問題は起こるでしょうか？またその対策が何かありましたらご教授ください。
・上記のSQLを実行出来るcakephpの書き方をご教授ください。

長くなりましたがよろしくお願いします。

Comments

[m.ts10806]

>未使用のコードを1件取得し、使用したコードを使用済みに更新する
レコードではなくコードですか？

[test_87097]

すいません、レコードです。

Answer 1

for updateはselectで指定して行ロックを行い、commitやrollbackで解放されます。
SQLのイメージだと以下の様になります。

SELECT id into @id FROM gifts WHERE condition_code=1 ORDER BY id ASC LIMIT 1 FOR UPDATE;
UPDATE gifts SET condition_code=2 where id= @id;
commit;

phpでの実装は以下を参考に。
PHP + PDO + MySQL のトランザクション制御方法まとめ

Comments

[test_87097]

回答ありがとうございます。
行ロックはUPDATEより前に行わないと意味がないですか？
UPDATE gifts SET condition_code=2 where id= (SELECT id FROM (SELECT id FROM gifts WHERE condition_code=1 ORDER BY id ASC LIMIT 1) as tmp FOR UPDATE);
みたいに一度に書くのは駄目なのでしょうか？

[sazi]

updateにfor update指定は出来ないと思いますけど、エラーになりませんか？

[sazi]

サブクエリーでfor update指定していましたね。
> 一度に書くのは駄目なのでしょうか
試したことはありませんが、明示的ではありませんし、やらない方が無難だと思います。

他のDBMSではサブクエリーでもOKという事を試されているのを見たことはありますが。

[test_87097]

ご回答ありがとうございます。
そうなんですね、よく理解できました。