[pgsql-jp: 40828] Re: デッドロックについて

[nozawakz ＠ nttdata.co.jp]

高塚様

野沢です。

早速ご回答いただき、ありがとうございます。

ORDER BY 主キーを設定したところ
デッドロックは発生しなくなりました。
（テーブルロックに関しても実施してみます。）

デッドロックは発生しなくなりましたが
私が知識不足であるため、大変お手数ですが、
デッドロックが発生しなくなった理由について
私の理解が合っているか確認させていただけないでしょうか。

下記のように
トランザクション１のselect for update文で
４レコードの順序が
　A→B→C→Dの順になっているとする。

次にUpdate文でBのレコードを更新しようとする。
この際、2行目以外はロックを解放する。

トランザクション２では不定であるため、
　B→C→D→A
となってしまっているとすると、
トランザクション１で
Update文がcommitされる前に
トランザクション２のselect for update文で
1,3,4行目をロックしてしまい、Bをロックしてしまうため、
トランザクション１でBのレコードを更新完了できない。

上記が連鎖的にトランザクション３以降でも
起こるため、デッドロックが発生する。

[トランザクション１]
　　　　A
　　　　B
　　　　C
　　　　D

[トランザクション２]
　　　　B
　　　　C
　　　　D
　　　　A

[トランザクション３]
　　　　A
　　　　D
　　　　C
　　　　B


以上、よろしくお願いいたします。

________________________________________
差出人: pgsql-jp-bounces ＠ ml.postgresql.jp [pgsql-jp-bounces ＠ ml.postgresql.jp] は TAKATSUKA Haruka [harukat ＠ postgresql.jp] の代理
送信日時: 2011年6月28日 18:44
宛先: PostgreSQL Japanese Mailing List
件名: [pgsql-jp: 40827] Re: デッドロックについて

高塚 ＠ JPUG / SRA OSS,Inc. と申します。

これはデッドロックは出ます。
以下で出なくなると思います。

 (1') SQL1
        SELECT カラムＡ、カラムＢ、カラムｃ
        FROM 業務テーブルＡ
        ORDER BY 主キー
        FOR UPDATE;

行ロック順序が不定なので、行ロックがたすきがけになっているのでしょう。

# 小さいテーブルと分かっているならテーブルロックでもいんじゃない？
# とも思います。

On Tue, 28 Jun 2011 18:03:58 +0900
<nozawakz ＠ nttdata.co.jp> wrote:

> お世話になります。野沢と申します。
>
> デッドロックの現象が解決しないため、質問させてください。
> RHEL5.5 64bit,Postgresql8.4.5
>
> ○質問事項
> レコード件数が少ないテーブルに対して複数トランザクションで
> Update文を発行する場合、デッドロックが発生することは
> ありえますでしょうか。
> また、解析方法で有効な手段があればご教授お願いできないでしょうか。
>
> ○現象
> ＜ＳＱＬについて＞
> 下記(1)、(2)の順でSQLが発行されます。
> (1)SQL1
>       SELECT カラムＡ、カラムＢ、カラムｃ
>       FROM 業務テーブルＡ
>       FOR UPDATE;
>
> (2)SQL2
>       UPDATE 業務テーブルＡ
>       SET カラムＣ = SQL(1)で取得した値
>       WHERE カラムA = カラムAの値
>           AND カラムB = カラムBの値
>
> ＜業務テーブルＡ＞
> 合計4レコードのみ
>
> ＜デッドロックが発生した時の状況＞
> (1)SQL発行頻度
> 　　テストでは80ＴＰＳの負荷をかけて、上記SQL文を発行しました。
>
> (2)deadlock_timeoutの設定
> 　　当初はデフォルト設定（１秒）を設定しており、直ぐにデッドロックタイムアウトが返却されました。
> 　　ＳＱＬコード：40P01（デッドロックの検出）
> 　　デッドロック回避のために、当設定値がチューニング要と判断し、設定値を（１００秒）に変更して、再実行しました。
> 　　が、１００秒後にデットロックタイムアウトが検出されました。
> 　　deadlock_timeout　の値を増やす事で、デッドロックタイムアウトの回避は出来ず、
> 　　検知にかかる時間が大きくなるという結果でした。
> 　　数秒間実施で356/9500件の割合（3.7％）でデッドロックを検出しています。
>
> (3) 襷掛け確認
> 　　単一のテーブルの更新に対してのトランザクションの集中であり、
> 　　複数トランザクションによる、ロック解放待ちの襷掛け状態にはなっておりませんでした。