MySQL :: ロック機構を用いたカウンターテーブル

明示的にロック、アンロックをせずとも、下記でこと足りるようです。

update counter set count = LAST_INSERT_ID(count + 1);
select LAST_INSERT_ID();

上記を踏まえた上で、以下をお読みください。

ロック機構を用いたカウンターテーブルです。
以下のような、カウント値を格納しておく「counter」テーブルがあるとします。

CREATE TABLE `counter` (
 `id` tinyint unsigned NOT NULL,
 `count` int unsigned NOT NULL,
 PRIMARY KEY (id)
);

※. プライマリーキーが必要な理由は後述。

通常は以下のような UPDATEクエリーでカウント値をインクリメントしてから、SELECTしてカウント値を取得すると思います。

UPDATE counter SET count = count + 1 WHERE id = 1;
SELECT count FORM counter WHERE id = 1;

しかし、同時に複数スレッドから上記クエリーを発行されたら整合性を保てるでしょうか。
スレッドA,Bが同時にクエリーを発行した場合、以下のように意図しない順番で実行されてしまうかもしれません。

UPDATE counter SET count = count + 1 WHERE id = 1; //スレッドA
UPDATE counter SET count = count + 1 WHERE id = 1; //スレッドB
SELECT count FORM counter WHERE id = 1; //スレッドA
SELECT count FORM counter WHERE id = 1; //スレッドB

こうなってしまっては、スレッドA,Bとも同じ値が返ってしまいます。

ただのアクセスカウンター等の用途なら別にどうってことないのですが、
もっとクリティカルな機能や案件で、絶対に絶対に絶対に同じ値を返してはならない。
返すぐらいならエラーを返したり、アップデートに失敗した方がまし。
というような場合は下記を検討してください。

以下は、ロック機構を用いて整合性が保証されたカウンターロジックです。
コードサンプルは php です。

$db->query('LOCK TABLES counter WRITE');
$db->query("INSERT INTO counter (id, count) VALUES (1, 1) ON DUPLICATE KEY UPDATE count = count + 1");
$count = $db->getColumn('SELECT count FROM counter WHERE id = 1');
$db->query('UNLOCK TABLES');
return $count;

１）LOCK TABLES counter WRITE で、排他ロック

$db->query('LOCK TABLES counter WRITE');

２）ON DUPLICATE KEY UPDATE
　　　すでに行が存在する場合は +1 した値でアップデート。
　　　行が存在しない場合は 1 をインサート。

$db->query("INSERT INTO counter (id, count) VALUES (1, 1) ON DUPLICATE KEY UPDATE count = count + 1");

３）現在のカウント値を取得(インクリメントされた値)

$count = $db->getColumn('SELECT count FROM counter WHERE id = 1');

４）ロックを解除

$db->query('UNLOCK TABLES');

５）値を返却

return $count;

• １）～５）のステートメントはクラスを作ってメソッドにするか、関数にして利用すべきです。
• ２）で ON DUPLICATE KEY UPDATE する理由は、あらかじめ行を用意しておく必要をなくすためです。
• 冒頭の「プライマリーキーが必要な理由」は、ON DUPLICATE KEY UPDATE を利用するために プライマリーキーが必要だったからです。

public function get()
{
    // LOCK TABLES: WRITE
    $this->manipulator->lock('WRITE');
 
    // ON DUPLICATE KEY UPDATE
    $this->manipulator->columns($this->pkey, 'count', 'upd_datetime');
    $this->manipulator->values(array(
        $this->pkey    => 1, 
        'count'        => 1, 
        'upd_datetime' => date('Y-m-d H:i:s'), 
    ));
    $this->manipulator->onDuplicateKeyUpdate(array(
        'count'        => array('=', 'count + 1'), 
        'upd_datetime' => date('Y-m-d H:i:s'), 
    ));
    $this->manipulator->insert();
 
    // SELECT
    $this->manipulator->select('count');
    $this->manipulator->where(array(
        $this->pkey => 1, 
    ));
    $count = $this->manipulator->fetchColumn();
 
    // UNLOCK TABLES
    $this->manipulator->unlock();
 
    return $count;
}