SQLiteのtransactionにおけるimmediateとexclusive

1. 表を読み取って
2. フラグがついていない一番IDの若い行を探してきて
3. そこにデータを流し込みフラグを消す

という一連の操作をSQLiteのデータベースに対して、 おこなうPerlのプログラムを書いていて、不具合が生じた。

まあちょっと考えれば気づきそうなものなのだが、 この一連の操作が２つの場所からほぼ同時に行われると、 あるプロセスがフラグがついていない行にフラグをつける前に、 別のプロセスがフラグ付きではない行としてデータを読み取ってしまう事態が生じる。

1. プロセスAがフラグがついていない一番IDの若い行を探す
2. プロセスAはその行をID= n だと認識
3. プロセスBがフラグのついていない一番IDの若い行を探す
4. プロセスBはその行をID= n だと認識
5. プロセスAはその行を更新し、フラグを削除
6. プロセスBはその行を更新し、フラグを削除（フラグはすでに削除されているが）

SQLite の Transaction

こういう時に使うのが Transaction というものらしい。

SQLiteのTransactionには３種類ある。

• Differed （延期）
• Immediate （すぐに）
• Exclusive （排他的に）

Differed (延期)

Deferred means that no locks are acquired on the database until 
the database is first accessed. Thus with a deferred transaction, 
the BEGIN statement itself does nothing to the filesystem. Locks 
are not acquired until the first read or write operation. The first read 
operation against a database creates a SHARED lock and the first 
write operation creates a RESERVED lock. Because the acquisition 
of locks is deferred until they are needed, it is possible that another 
thread or process could create a separate transaction and write to 
the database after the BEGIN on the current thread has executed.

その名の通り、ロックができるだけ延期されるTransactionで、読み取りの際はロックされない。書き込みの段になってはじめて 予約ロックがなされる。書き込みに入るまではロックされないので、その間のデータベース操作はできてしまう。

Immediate （すぐに）

If the transaction is immediate, then RESERVED locks are acquired
 on all databases as soon as the BEGIN command is executed, 
without waiting for the database to be used. After a BEGIN IMMEDIATE, 
no other database connection will be able to write to the database or 
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes 
can continue to read from the database, however.

transactionが開始されるとすぐに予約ロックされる。その間は、書き込みがロックされると共に、別の Immediate transactionも禁止される。しかし、読み込みはできる。

Exclusive （排他的に）

An exclusive transaction causes EXCLUSIVE locks to be acquired 
on all databases. After a BEGIN EXCLUSIVE, no other database 
connection except for read_uncommitted connections will be able 
to read the database and no other connection without exception 
will be able to write the database until the transaction is complete.

この排他的transactionが始まると、 書き込みはおろか、読み込みに関しても read_uncommitted 以外の読み込みも禁止される。

PerlでSQLiteのTransactionを書く場合

あまりいい例ではないと思うが、今回自分が書いたのはこんな感じ。

Immediateは、書き込みだけではなく、他の Immediate transactionも制限されるところがポイントで、今回の問題は、当初はexclusiveなtransactionでないとダメだと思っていたが、 immediate transactionも禁止されるので、今回は immediate でも大丈夫そうだった。

$self->dbh->do('BEGIN IMMEDIATE');

# select
my $select = "SELECT user_id FROM $users_list 
                                    WHERE used = 'no' ORDER BY user_id asc";
my $sth = $self->dbh->prepare( $select );
$sth->execute();
my ($new_id) = @{$sth->fetch};

# update
my $str = q/used = 'yes' /;
for( keys %$param ){
    my ($key, $val) = ( $_, $param->{$_} );
    $str .= ', ' . $key. " = " . $val . " ";
}

my $update = "UPDATE $users_list SET $str WHERE user_id = ?";
$sth = $self->dbh->prepare( $update );
$sth->execute( $new_id );

$self->dbh->commit;

BEGIN IMMEDIATE してから commit までが一連の transaction で、その間に execute があっても transactionは終了しない。

参考

• SQLite Query Language: BEGIN TRANSACTION
• DBD::SQLite 1.30_02 - Charsbar::Note