Comments
Description
Transcript
簡単シリーズ アプリケーションデザインのための ロックの基本 目次
簡単シリーズ アプリケーションデザインのための ロックの基本 2002/3 SS&WSCC#1 目次 第一部 分離レベルとロックの属性 １ 分離レベル ２ ロックの対象 ３ ロックのモードと互換性 ４ ロックの保持期間 ５ ロックの範囲 ６ ネクストキーロック 第二部 ロックを扱うプログラミング ７ FOR UPDATE OF付きカーソル ８ ロック変換 ９ 更新ロックの互換性 １０ 更新ロックによるシリアライズ １１ WITH HOLD付きカーソル １２ WITH RELEASE付きカーソルCLOSE １３ LOCK TABLE 第三部 ロックに関連する指定と例外事象 １４ 省略時ロック単位の変更 １５ ロックタイムアウト １６ デッドロック １７ ロックエスカレーション 終わりに １８ ロック保持に関する指針 １９ 付録 Ｑ/Ａ Systems Solution & Web Server Competence Center No.1 DM Group 1-2 概要 DB2アプリケーションがトランザクションを構成して稼動するためにDB2はロックの仕 組みを使っています。 第一部では分離レベルの考え方を説明し、それを実現するためにDB2が自動的に行って いるロックの仕組みの基本事項を紹介します 各分離レベルの性格を理解することでロックの仕組みの存在意義と機能の概略がわかります 第二部ではロックを制御するアプリケーションプロミングを紹介します アプリケーションプログラミングで制御できるロックの機能を理解しましょう 第三部ではロックに関連する基本的なデータベース構成パラメータの役割とロックに 関連する例外事象を紹介します 例外事象の内容とロックに関するパラメータを理解しましょう 最後にロック保持に関するアプリケーションデザインの指針・注意点を簡潔に説明し ます 長期間にわたるロックの保持をさけましょう 更新履歴 2002/3 FOR UPDATE使用上の注意点を追加 ロック・エスカレーション：強調点を修正 付録QA:Qの前提を削除、説明図を詳しく Systems Solution & Web Server Competence Center No.1 DM Group 空白ページ 3-4 第一部 分離レベルとロックの属性 Systems Solution & Web Server Competence Center No.1 DM Group トランザクション処理の分離属性（アイソレーション) DB2アプリケーションはトランザクションの属性を持っています。トランザクションの 属性のひとつにIsolation（分離性）があります。分離性とは、トランザクションの実 行結果が同時に他の実行されるトランザクションの影響を受けないという属性です。 ISOでは４つの分離レベルが規定されています、DB2は４つをすべてサポートしていま す ４つの分離レベルは３つの現象を使って定義されています 分離レベルを選ぶのはアプリケーション(指定およびプログラミング)です DB2では４つの分離レベルを実現するためにロックの仕組みを使っています Systems Solution & Web Server Competence Center No.1 DM Group 5-6 SQL標準分離レベルとDB2 UDBの分離レベル SQLトランザクション分離レベルと３つの現象 ISO SQLの分離レベルはDB2の分離レベルと対応していて３つの事象の発生有無で定義 されています。SERIALIZABLEは３つの現象が３つとも発生しない最も強いレベルです ISO SQL 標準 分離レベル DB2 分離レベル 非コ ミット 読み取 り 反復不 能読み 取り 幻像読 み取り SERIALIZABLE REPEATABLE READ (RR) 反復可能読み取り 発生 しない 発生 しない 発生 しない REPEATABLE READ READ STABILITY (RS) 読み取り固定 発生 しない 発生 しない 発生 する 発生 しない 発生 する 発生 する 発生 する 発生 する 発生 する READ COMMITED READ UNCOMMITTED CURSOR STABILITY (CS) カーソル固定 UNCOMMITTED READ (UR) 非コミット読み取り Systems Solution & Web Server Competence Center No.1 DM Group 分離レベルと３つの現象 RR(反復読み取り可能)：トランザクション中で何度同じSELECTを実行しても得られる 結果は行数、内容とも同じです 後述の幻像読み取りも発生しません、これは理想的な分離レベルです、トランザクションの実行結果は他の トランザクションの影響を全く受けません。これよりもゆるやかな分離レベルも以下のように３つ規定され ています。 RS(読み取り固定)：トランザクション内で一度SELECTできた行は再度SELECTしても同 じ内容です ただし、トランザクション中で同一SELECTを再度発行したら追加の行も返されて、結果の行数が増えている かもしれません。この現象を幻像読み取りと呼びます。RS以下では幻像読み取りが発生します CS(カーソル固定)：トランザクション内でSELECTできた行はCOMMITされたものだけで す ただし、トランザクション中で同一SELECTを再度発行したら内容が変わっていたり、削除されているかもし れません、この現象を反復不能読み取りと呼びます。CS以下では反復不能読み取りが発生します UR(非コミット読み取り)：トランザクション内ではCOMMITされていない行もSELECTで きます SELECTした行はその後ROLLBACKされてしまうかもしれません、これを非コミット読み取りと呼びます 次に実例で３つの現象の発生有無を比較してみましょう Systems Solution & Web Server Competence Center No.1 DM Group 7-8 幻像読み取りが発生する例 RS(ISOのREPEATABLE READ)での幻像読み取り CHANGE ISOLATION TO RS CONNECT TO SAMPLE SELECT * FROM T2 >= 99998 C1 -----99998 99999 C2 -----99998 99999 CHANGE ISOLATION TO CS CONNECT TO SAMPLE INSERT INTO T2 VALUES(20000,0) 表T2 SELECT条件に合 う行の追加 SELECT * FROM T2 >= 99998 COMMIT WORK 20000読み取 りで待ち C1 -----99998 99999 200000 C2 -----99998 99999 0 分離レベルRSでは幻像読み取りが発生します。 トランザクション内の２つのSELECTの間で他トランザク ションからのINSERTが許されて２回目のSELECTの際に 追加行が発生しえます 幻像行発生 COMMIT WORK Systems Solution & Web Server Competence Center No.1 DM Group 幻像読み取りが発生しない例 RR(ISOのSERIALIZABLE)では幻像読み取りが発生しない CHANGE ISOLATION TO RR CONNECT TO SAMPLE SELECT * FROM T2 WHERE C1 >= 99998 C1 -----99998 99999 C2 -----99998 99999 CHANGE ISOLATION TO CS CONNECT TO SAMPLE INSERT INTO T2 VALUES(20000,0) SELECT * FROM T2 WHERE C1 >= 99998 C1 -----99998 99999 C2 -----99998 99999 待ち 表T2 結果 一致 COMMIT WORK COMMIT WORK 分離レベルRRでは幻像読み取りが発生しません トランザクション内の２つのSELECTの間でトランザクション からのINSERTは待たされます。２回目のSELECTの際に 追加行は発生しません Systems Solution & Web Server Competence Center No.1 DM Group 9-10 反復不能読み取りが発生する例 CS(ISOのREAD COMMITED)での反復不能読み取り CHANGE ISOLATION TO CS CONNECT TO SAMPLE SELECT * FROM T2 WHERE C1=99999 CHANGE ISOLATION TO CS CONNECT TO SAMPLE UPDATE T2 SET C2=C2+1 WHERE C1=99999 C1 C2 ----------- ----------99999 99999 SELECT * FROM T2 WHERE C1=99999 COMMIT WORK 表T１ C1 C2 ----------- ----------99999 100000 結果が変 わる 分離レベルCSでは反復不能読み取りが発生します。 トランザクション内の２つのSELECTの間で他トランザク ションからのUPDATEが動き、２回目のSELECTの際に前 回のSELECT行の結果が変わりえます Systems Solution & Web Server Competence Center No.1 DM Group 反復不能読み取りが発生しない例 RS(ISOのREPEATABLE READ)では反復不能読み取りが発生しない CHANGE ISOLATION TO RS CONNECT TO SAMPLE SELECT * FROM T2 WHERE C1=99999 CHANGE ISOLATION TO CS CONNECT TO SAMPLE UPDATE T2 SET C2=C2+1 WHERE C1=99999 C1 C2 ----------- ----------99999 99999 SELECT * FROM T2 WHERE C1=99999 C1 C2 ----------- ----------99999 99999 待 ち 表T１ COMMIT COMMIT WORK OK! 分離レベルCSでは反復不能読み取りが発生しせん。 トランザクション内の２つのSELECTの間で他トランザク ションからのUPDATEは待たされます。１回目にSELECTし た行については２回目にSELECTしても結果は変わりませ ん Systems Solution & Web Server Competence Center No.1 DM Group 11-12 非コミット読み取りが発生する例 UR(ISOのREAD UNCOMMITED)での非コミット読み取り CHANGE ISOLATION TO CS CONNECT TO SAMPLE CHANGE ISOLATION TO UR CONNECT TO SAMPLE INSERT INTO T1 VALUES(3,30) SELECT * FROM T1 C1 C2 ------ -----1 10 2 20 3 30 行(3,30)を追加した が取り消した 表T１ ROLLBACK 非コミット行を 読んだ SELECT * FROM T1 C1 C2 ------ -----1 10 2 20 分離レベルURでは非コミット読み取りが発生します。 他トランザクションからの先にINSERTされていた行は COMMIT前でもSELECTできてしまいます、もし ROLLBACKされると２回目のSELECTの際にはなくなって いるかもしれません 非コミット行は ROLLBACKで消えた Systems Solution & Web Server Competence Center No.1 DM Group 非コミット読み取りが発生しない例 CS(ISOのREAD COMMITED)では非コミット読み取りが発生しない CHANGE ISOLATION TO CS CONNECT TO SAMPLE CHANGE ISOLATION TO CS CONNECT TO SAMPLE INSERT INTO T1 VALUES(3,30) (3,30)を追加し たが取り消した SELECT * FROM T1 ROLLBACK C1 C2 ------ -----1 10 2 20 COMMIT 表T１ OK! 分離レベルCSでは非コミット読み取りが発生しせん。 他トランザクションからの先にINSERTされていた行への SELECTはCOMMITまたはROLLBACKするまで待たされ ます。COMMITかROLLBACKが確定した後にSELECTの 結果が得られます。 Systems Solution & Web Server Competence Center No.1 DM Group 13-14 分離レベルの選択 分離レベル選択の考慮点 望ましくない３つの事象の防止と同時並行稼動性はトレードオフの関係です。アプリケーションデザイン者 はこれらを考慮して分離レベルを選択します DB2の省略時の分離レベルはカーソル固定(CS)です DB2 分離レベル 非コ ミット 読み取 り 反復不 能読み 取り 幻像読 み取り ロック 待ち 同時 並行 稼動 性 REPEATABLE READ (RR) 反復可能読み取り 発生 しない 発生 しない 発生 しない 多い 低い READ STABILITY (RS) 読み取り固定 発生 しない 発生 しない 発生 する 発生 しない 発生 する 発生 する 発生 する 発生 する 発生 する CURSOR STABILITY (CS) カーソル固定 UNCOMMITTED READ (UR) 非コミット読み取り 省略時 少ない 高い Systems Solution & Web Server Competence Center No.1 DM Group 分離レベルの指定方法 CLI型アプリケーションでの分離レベル指定 ODBC, CLI, JDBC, VB ADOなどCLI型のアプリケーションプログラムは、省略時の分離レベルをdb2cli.ini ファイルのTXNISOLATION=パラメータで指定します TXNISOLATION=1￨2￨4￨8￨32 （順にUR CS RS RR NC 省略時2=CS 32はAS/400のみ) 分離レベルの省略値を実行時にプログラム内で置き換えることができます、実行時に分離レベルを指定する apiやメソッドは各プログラミング方式で異なります。分離レベル属性名はISO分離レベル名に従っています CLI 実行時：SQLSetConnectAttr関数またはSQLSetStmtAttr関数のSQL_ATTR_TXN_ISOLATION属性 SQL_TXN_SERIALIZABLE, SQL_TXN_REPEATABLE_READ, SQL_TXN_READ_COMMITED, SQL_TXN_READ_UNCOMMITED JDBC 実行時：setTransactionIsolation メソッド TRANSACTION_SERIALIZABLE,TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMMITED, TRANSACTION_READ_UNCOMITTED VB ADO 実行時：con.IsolationLevelプロパティ adXactSerializable, adXactRepeatableRead, adXactReadCommited, adXactReadUncommited Systems Solution & Web Server Competence Center No.1 DM Group 15-16 分離レベルの指定方法 埋め込みSQLの分離レベル指定方法 ソースプログラムのプリコンパイル・バインドを必要とする埋め込みSQL型プログラミングでは、プリコン パイル・バインドしてパッケージを作る際のISOLATIONパラメータで分離レベルを指定します 埋め込みSQL PRECOMPILE/BINDコマンドのISOLATIONパラメータUR CS RS RR SQLプロシージャー SQLプロシージャはCREATE PROCEDUREの実行でプリコンパイルが実行されパッケージが作成されますが、 db2set DB2_SQLROUTINE_PREPOPTSで指定できるプリコンパイルオプションのひとつであるISOLATIONパラ メータで指定します DB2コマンドラインプロセッサー CHANGE ISOLATION TOというCLPコマンドでCONNECT前に分離レベルを変更します。DB2 CLPのパッケージは DB2が提供しています。各分離レベルのパッケージがあらかじめ提供されていて切り替えて使われます。 SQLJ db2profc コマンドの-prepoptionsのISOLATIONパラメーター Systems Solution & Web Server Competence Center No.1 DM Group 分離レベルの指定方法 SQL文レベルの分離レベル指定 DB2 V7.1 FIXPACK3またはDB2 V7.2から個々のSQL文に分離レベルを指定できるようになりました 次のSQL文に文レベルの分離レベルが指定できます WITH CS のようにWITHキーワードを使って指定します SELECT SELECT INTO DELETE (カーソルを使わない) INSERT UPDATE (カーソルを使わない) DECLARE CURSOR 次の注意点があります WITH を副照会に指定することはできません WITH を指定しない場合の省略時はSQLを発行するパッケージの分離レベルです WITH UR を読み取り専用SQL以外に指定してもWITH CSとして扱われます 使用例 SELECT * FROM TBL WHERE XXX=YYY WITH UR Systems Solution & Web Server Competence Center No.1 DM Group 17-18 ロックの対象 暗黙的に獲得されるロックの概要 DB2はデータの保全性を保つために暗黙的にロックを取得します。アプリケーションプログラム設計者は、DB2が暗黙的に行っ てくれるロックの操作の基本・概要を理解しておくべきです ロックの対象 行の更新は行単位のロックを使用すれば平行性が高まります。しかし、DROP TABLE中の表に対してはどんなアプリケーション からもSELECTすら許さないように制御すべきです、これは表ロックで制御できます。ユーティリティでは表スペースを占有使 用したいものもあります。このように行、表、表スペースの３種類のロックの対象があります。 アプリケーションは行ロックと表ロックを取ります ユーティリティは行ロック、表ロック、表スペースロックをとります ロックの対象：行、表、表スペース、の３種。(索引ロックはありません) 表スペース 表スペース ロック 表 表ロック 行 行ロック Systems Solution & Web Server Competence Center No.1 DM Group ロックのモードと互換性 ロックのモードと互換性 同一行の更新を行う２つのアプリケーションが同時に起動されても一方の更新が終わるまで他方の更新は待たされるべきで す。一方、読み取りアリケーション同士は待つ必要は互いにありません。DB2はこれらの同時平行稼動性の制御をロックのモー ドとその互換性（相手のロック開放を待つ、待たない）で制御しています。ロックモードは全部で１２種あります、いくつか のロックモードは表ロックだけのものです。 もっとも基本的な共有ロックと排他ロックについての互換性をあげてみましょう 主なロックモード 共用ロック (Sロック、NSロック) 照会で取得されます 排他ロック(Xロック) 更新処理で取得されます 主な互換性 要求されているロック 保持され ている ロック 共用(S/NS) 排他(X) 共用(S/NS) 待たない 待つ 排他(X) 待つ 待つ この表により各ロックの組み合わせの互換性は次のような結果になります 共用ロック<----共用ロック：互換（待たない） 共用ロック<----排他ロック：非互換（相手のロック開放を待つ） 排他ロック<----共用ロック：非互換（相手のロック開放を待つ） (この組み合わせは未コミット読み取りを防ぐ分離レベルで使われています) 排他ロック<----排他ロック：非互換（相手のロック開放を待つ） これら以外のロックモード(全12種)と組み合わせの互換性(12x12)は管理の手引きに記載されています Systems Solution & Web Server Competence Center No.1 DM Group 19-20 ロックの期間 ロックの期間 分離レベルRSでは反復不能読み取りが許されません、一度SELECTできた行はUOW終了まで他から更新されないようにトランザク ション終了まで保護する必要があります、DB2はトランザクション終了まで共用ロックを保持します。分離レベルに応じてロッ クの保持される期間が変わります。 分離レベルRS,RRは一度獲得した共用ロックを基本的にCOMMIT/ROLLBACKまで保持します 分離レベルCSで共用ロックは基本的にカーソルが別の行へ移動するまで保持します 更新のためのロックもトランザクション終了まで保持されます、更新ロックを保持することで他トランザクションからの更新 が防止されます。 更新タイプのロックはトランザクション終了まで保持されます 分離レベルRR RSの行ロックの期間 分離レベルCSの行ロックの期間 Open Cursor Fetch Fetch Fetch Fetch Close Cursor Update Update Commit Open Cursor Fetch Fetch Fetch Fetch Close Cursor Update Update Commit 開放 Systems Solution & Web Server Competence Center No.1 DM Group ロックの範囲 ロックの範囲 分離レベルRRではSELECTでDB2が読み取った検索行すべてに共用ロックが取られます 幻像読み取りを防止するためです、これに加えて索引の次の行もネクストキーロックとして共用ロックが取得されます 分離レベルRSではSELECTで得られた結果行すべてに共用ロックが取られます 反復不能読み取りを防止するためです、共用ロックが排他ロックを待たせます 分離レベルCSではカーソル位置の行に共用ロックが取られます 未コミット読み取りを防止するためです。SELECTを試みた行が他から更新されてコミット/ロールバックが未だだった場 合、相手のトランザクション終了までロック待ちします 分離レベルURでは共用ロックはとられません 未コミット行の読み取りを許すので行の共用ロックは取得しません 索引での 次の行 C1 1 2 3 4 5 6 7 8 9 10 索引 C2 10 20 30 40 50 60 70 80 90 100 元カーソル行 SELECT C1,C2 FROM 表１ WHERE C2=10 OR C2=90 結果 表１ 読み取った行と 索引の次の行 1 9 カーソルのある行 取り出した行 RRのロック範 囲 RSの ロック 範囲 カーソルのある行 CSの ロック 範囲 Systems Solution & Web Server Competence Center No.1 DM Group 21-22 10 90 ネクストキーロック ネクストキーロックは幻像読み取りを防止するためにDB2が使うロックです RR分離レベルでのSELECTでは読み取った検索行すべてと索引の次の行に共用ロックSロックが取られます 索引の次の行の共用ロックSロックをネクストキーロックと言います この例については読み取られた行の中にネクストキーも含まれています 幻像読み取り防止のためにはトランザクション終了までは、他のトランザクションからの INSERT INTO 表１ VALUES(1.6, 10) INSERT INTO 表１ VALUES(9.5, 90) といった値の追加は許されません、もしこれらが追加されてCOMMITされたとすると元のSELECT が再発行された時に幻像行として現れてしまいます DB2は幻像読み取り防止をINSERT側のネクストキーロックを組み合わせて使うことによって実現しています RR分離レベルを全く使わないお客様のためにネクストキーロックを使わないオプションDB2_RR_TO_RS=YESレジストリー変数もあります 索引での 次の行 C1 C2 1 2 3 4 5 6 7 8 9 10 10 20 30 40 50 60 70 80 90 100 索引 分離レベルRRのSELECTは読み取った行と索引の次の行にSロックを 取得 S S S S S S S S S S RRのロック範 囲 INSERT into 表１ values(1.6, 10) 索引の次キーのネクストキーロックをチェック ＆必要なら待ち SELECT C1,C2 FROM 表１ WHERE C2=10 OR C2=90 (INSERT用のネクストキーロックNWを使用) 結果 表１ 1 9 INSERT into 表１ values(9.5, 90) 索引の次キーのネクストキーロックをチェック ＆必要なら待ち (INSERT用のネクストキーロックNWを使用) Systems Solution & Web Server Competence Center No.1 DM Group 第二部 ロックを扱うプログラミング Systems Solution & Web Server Competence Center No.1 DM Group 23-24 １回目 10 90 ２回目 1 10 1.6 10 9 90 9.5 90 FOR UPDATE OF付きカーソル FOR UPDATE OF付きカーソル デッドロックの排除 行の列の現在値を読み取り、更新するタイプのアプリケーションではFOR UPDATE OF付きカーソルを使用しないとデッドロック がおきえます。これは共用ロック同士が互換で共用ロックと排他ロックが非互換なためです APPL1 1)SELECT 表１行1(共用ロック) APPL2 2)SELECT 表１行１(共用ロック) 3)UPDATE 表１行１(排他ロック) ロック待ち 4)UPDATE 表１行１(排他ロック) ロック待ち FOR UPDATE OF付きカーソルでUPDATE WHERE CURRENT OFカーソルを使うと更新ロック（Uロック）が使 われデッドロックが避けられます 更新ロック同士が非互換、更新ロックとと排他ロックが非互換なためです APPL1 1)SELECT 表１行1 FOR UPDATE OF (更新ロック) APPL2 2)SELECT 表１行１ FOR UPDATE OF (更新ロック) ロック待ち 3)UPDATE 表１行１(排他ロック) 4)COMMIT ロック開放 2') 5)UPDATE 表１行１(排他ロック) 6)COMMIT Systems Solution & Web Server Competence Center No.1 DM Group ロック変換 ロック変換とは連続して発行されるSQLによって取得されたロックのモードがより強いモードに代わる ことです FOR UPDATE OF付きカーソルによるFETCH後のUPDATEでは、更新ロック(Uロック)が排他ロック(Xロッ ク)に代わります。これがロック変換の例です。Uロックは一度開放されてXロックが取られるのではな く、ロックが保持されたたままでロックモードが排他ロック(Xロック)に代わります。 ロックのモードは代わりますがロック自体は保持されたままなのでこの間、他のトランザクションにロックを奪われることは ありません ロック変換 APPL1 APPL2 1)SELECT 表１行1 FOR UPDATE OF (更新ロック) 2)SELECT 表１行１ FOR UPDATE OF (更新ロック) ロック待ち 3)UPDATE 表１行１(排他ロック) 4)COMMIT ロック開放 2') 5)UPDATE 表１行１(排他ロック) 6)COMMIT Systems Solution & Web Server Competence Center No.1 DM Group 25-26 更新ロックの互換性 FOR UPDATE OF付きカーソルによるFETCH後のUPDATEの流れでは更新ロックと更新ロックが非互換なの で更新処理がうまくシリアライズされます。共用ロック、更新ロック、排他ロックの互換性をまとめ てみましょう。 要求されているロック 保持 されて いる ロック 共用(S/NS) 更新(U) 排他(X) 共用(S/NS) 待たない 待たない 待つ 更新(U) 待たない 待つ 待つ 排他(X) 待つ 待つ 待つ ロック変換 APPL1 APPL2 1)SELECT 表１行1 FOR UPDATE OF (更新ロック) 2)SELECT 表１行１ FOR UPDATE OF (更新ロック) ロック待ち 3)UPDATE 表１行１(排他ロック) 4)COMMIT ロック開放 2') 5)UPDATE 表１行１(排他ロック) 6)COMMIT Systems Solution & Web Server Competence Center No.1 DM Group 更新ロックによるトランザクションのシリアライズ 更新ロックの利用例 画面表示中はロックを保持しないよう、画面表示前にCOMMITを発行します 更新画面入力があった時、データベースの内容が前回表示した内容と変わっていないかチェックします ユーザー１ C1=1 A SELECT C2 INTO :C2 FROM T1 WHERE C1 = 1 表示 COMMIT S/NS S/NS COMMIT COMMIT SELECT C2 INTO :C2 FROM T1 WHERE C1 = 1 表示 COMMIT C1=1 B 表示したC1=1 C2=Aを保管 表示したC1=1 C2=Aを保管 SELECT C2 INTO :C2 FROM T1 A WHERE C1 A= KEY FOR UPDATE OF C2 ; B IF 今読んだ:C2 =保管してい たC2の値 THEN UPDATE T1 SET C2=更新値 WHERE C1=1; COMMIT ; ELSE MSG "先を越されました" ROLLBACK ; C1=1 A C2= C2= C2= ユーザー２ U待ち U SELECT C2 INTO :C2 FROM T1 WHERE C1 = 1 FOR UPDATE OF C2 ; B X U COMMIT もし前回画面表示した内容とデータベースが変わって いれば他ユーザーに先を越されたのでROLLBACKします ROLLBACK Systems Solution & Web Server Competence Center No.1 DM Group 27-28 A IF 今読んだ:C2 =保管していた C2の値 THEN UPDATE T1 SET C2=更新値 WHERE C1=1 ; COMMIT ; ELSE MSG "先を越されました" ROLLBACK ; C1=1 Z C2= FOR UPDATE 使用上の注意点 FOR UPDATEつきSELECTはできるだけ分離レベルCS (TRANSACTION_READ_COMMITED)で使いましょう 更新トランザクションではRRやRSを使う必要性がまずありません 次の注意点を守りやすくするためにもロックの開放が早い分離レベルCSを使いましょう FOR UPDATE 付きSELECTの発行前にはロックを開放しておきましょう 同じ行に対してFOR UPDATEなしSELECT、FOR UPDATE付きSELECT、UPDATEを行うトランザク ションが２つ並行に実行されるとデッドロックになりえます 理由は既出のFOR UPDATEつきカーソルのデッドロックの排除の所の悪い例と同様です 悪い例 良い例 共用ロックNS/S 共用ロックNS/S 共用ロックNS/S 更新ロックU (待ち) COMMIT 更新ロックU 更新ロックU 共用ロックNS/S COMMIT 更新ロックU 排他ロックX 排他ロックX 排他ロックX COMMIT COMMIT 排他ロックX COMMIT Systems Solution & Web Server Competence Center No.1 DM Group WITH HOLDオプション付きカーソル WITH HOLDオプション付きカーソル カーソル処理中のCOMMITの発行が可能 １つのUPDATE文で多量の更新を行うと１トランザクション内の更新量が非常に大きくなることがあります アクティブログが足りず、UPDATE文がSQLCODE -964で失敗する可能性があります カーソル処理で更新する場合にはWITH HOLDオプション付きのカーソルを使えば途中でCOMMITを発行してもカーソルがOPENのま ま次のカーソルを処理できます Declare s1 cursor with hold for select c1,c2 commit間隔が増え、アクティブログの所要量を削減できます from 表１ where c1 like '%X' for update of UPDATE 表１ SET C2=... WHRE C1 LIKE '%X%' commit work 表１ ログ COMMIT Systems Solution & Web Server Competence Center No.1 DM Group 29-30 c2 Open s1 fetch s1 update set c2=.... fetch s1 update set c2=.... commit work fetch s1 update set c2=.... fetch s1 update set c2=.... commit work fetch s1 update set c2=.... fetch s1 update set c2=.... commit work ....... Close s1 commit work where current of s1 where current of s1 ログ COMMIT where current of s1 where current of s1 ログ COMMIT where current of s1 where current of s1 ログ COMMIT ログ COMMIT WITH RELEASE付きCloseカーソル WITH RELEASE付きCloseカーソル RRとRS分離レベルではカーソルCLOSE時に共用ロックを開放させるオプションです RRやRS分離レベルはロックの期間が長い特徴があります。共用ロックを多く保持しなくない時、カーソルのCLOS時点で共用 ロックを開放してしまうオプションがあります。これは便利ですが使うと分離レベルには反する動きになります。 分離レベルRR RSのロックの期間 Close cursor With Relese付き 分離レベルRR RSのロックの期間 Open Cursor Fetch Fetch Fetch Fetch Close Cursor With Release Update Update Commit Open Cursor Fetch Fetch Fetch Fetch Close Cursor Update Update Commit Systems Solution & Web Server Competence Center No.1 DM Group プログラミングによるロックの制御 LOCK TABLE文 LOCK TABLE文を使えば明示的な表ロックの獲得が可能です IN SHARE MODE オプションで表の共用ロックを取得します IN EXCLUSIVE MODE オプションで表の排他ロックを取得します LOCKの開放はトランザクションの終了時(COMMIT/ROLLBACK)です 先に表を占有 表１ LOCK TABLE 表１ IN EXCLUSIVE MODE LOCK TABLE 表１ IN SHARE MODE 待ち Systems Solution & Web Server Competence Center No.1 DM Group 31-32 開放 第三部 ロックに関連する指定と例外事象 Systems Solution & Web Server Competence Center No.1 DM Group 省略時ロック単位の変更 ALTER TABLE LOCKSIZE ROW/TABLE ロックの単位の省略時は行ロックですが、ケースによっては意図的に表ロックに変更した方が有利な 場合があります 表が読み取り専用である 読み取り専用の表を表ロックで制御するとロックの量が少なくなります １ユーザーが表の保守のために占有使用することがある 更新アクセスが表の排他ロックを取りその間他ユーザーからのアクセスをブロックできます ALTER TABLE LOCKSIZE TABLE で特定表のロック単位を表ロックに変更可能です DDLを実行するとその表スペースのポイントインタイムリカバリーできる時刻はそれ以降に限定されます Systems Solution & Web Server Competence Center No.1 DM Group 33-34 ロックタイムアウト ロックタイムアウト ロック要求が許可されるまでロック要求側はロック待ちします。 ロック待ちの時間がデータベース構成パラメータLOCKTIMEOUT（秒）を越すと、ロックタイムアウトが 発生してロック要求側に制御が戻されます LOCTIMEOUTの省略時は無限待ち(-1)です ロックタイムアウトしたアプリケーションにはSQL0911N RC68が返されます、TWO PHASE COMMITの環境ではSQL0913N RC68が返 されます アプリケーションがロック待ちしているかどうかはLOCK SNAPSHOTモニターやdb2 list applicatios show detailで把握できます Systems Solution & Web Server Competence Center No.1 DM Group デッドロック デッドロック ２つ以上のトランザクションが互いにロック待ちし解決しない状態をデッドロックといいます デッドロックはデータベースマネジャー構成パラメータDLCHKTIME（ミリ秒）のサイクルで検査されま す デッドロックが発生すると一方のアプリケーションにSQL0911N RC2(デッドロック検知)が返され ROLLBACKされます、TWO PHASE COMMITの環境ではSQL0913N RC2が返されます どちら側のトランザクションがROLLBACK返されるかは予測できません、その代わりROLLBACK対象はすばやく決定されます デッドロックの原因究明の資料収集にはいくつかの方法があります デッドロック検知時間を長くして、デッドロック発生から検知までの間にLOCK SNAPSHOTモニターを収集する DEADLOCKのイベントモニターを利用する db2diag.logの情報を利用する APPL1 1)UPDATE 表１行1(排他ロック) APPL2 2)UPDATE 表２行２(排他ロック) 3)UPDATE 表２行２(排他ロック) ロック待ち 4)UPDATE 表１行１(排他ロック) ロック待ち Systems Solution & Web Server Competence Center No.1 DM Group 35-36 ロック・エスカレーション ロック・エスカレーションの目的 もし、一つでもアプリケーションが暴走してロックのメモリー資源を使いきってしまうとデータベー スの全体障害になりえます。アプリケーションの暴走に対する歯止めが望まれます １００万行のロックは７２ＭＢのメモリーを使います、これは稼動環境によっては大きすぎる場合もあるでしょう。 CPU負荷の面ではロックの処理はマイクロ秒単位であり、軽いものです。 ロック・エスカレーションは１アプリケーションが多量の行ロックでロック用メモリーを占有することを防ぐ一種の縮退運転 の機能です。多数の行ロックを１つの表ロックに代えてロックの量を一挙に減らします ガバナーによって多量のロックを取得したアプリケーションを強制終了させる方法もあります ロックエスカレーションに関連するパラメータ データベース構成パラメータ LOCKLIST ロックリストの記憶域の量：4KBページ 最大524288ページ(２ＧＢ ２９００万行ロック） １ロック７２バイト、ただしロック設定ずみのオブジェクトに対しては３６バイト データベース構成パラメータ MAXLOCKS (%) アプリケーションがLOCKLISTの何％使ったらロックエスカレーションするか 省略時のLOCKLISTは非常に小さいので大きい値に代えておきましょう MAXLOCKSは小さめでLOCKLISTを充分に大きくしましょう 表 LOCKLIST (大きさ) <= MAXLOCKS(%) X (行ロック) X (行ロック） X（行ロック） X（行ロック） X（表ロック) X (36 or 72) Systems Solution & Web Server Competence Center No.1 DM Group おわりに Systems Solution & Web Server Competence Center No.1 DM Group 37-38 ロック エスカレーション APPL ロック保持に関する指針 長期間にわたるロックの保持を避けましょう オンラインアプリケーションでは画面出力の前にかならず、COMMITかROLLBACKを発行しましょう 正常終了のCOMMITはもちろん、エラーの発生時にも忘れずにROLLBACKを発行しましょう SELECTのみのアプリケーションでもCOMMITかROLLBACKを発行し、カーソルもCLOSEしましょう ユーザーの更新画面入力中に更新予定データのLOCKを保持しておくデザインはやめましょう 人間のシンクタイムにロックをかけるデザインではサーバーの処理能力を上げてもシステムのスループットがあがらなく なります バッチの追加更新削除プログラムは適宜COMMITかROLLBACKしてUOWを大きくしないようにしましょう COMMIT・ROLLBACKの間隔が長いと保持されるロックの量が多くなりロックエスカレーションを起きて、表に排他ロックを取っ てしまい平行性が下がります COMMIT・ROLLBACKの間隔が長いとアクティブログが多く必要になります COMMIT/ROLLBACK COMMIT/ROLLBACK Systems Solution & Web Server Competence Center No.1 DM Group 付録：Q&A Systems Solution & Web Server Competence Center No.1 DM Group 39-40 Q:DB2で２つのアプリケーションが同じ表の異なる行をUPDATEする時はロック待ちは起 きますか？ A:２つのアプリケーションが索引経由で異なる行をアクセスする場合にはロック待ち は起きません。 しかし、索引が使われない場合にはロック待ちが発生しえます。これは実行結果の整合性を保証するためで す。順にご説明します。 索引アクセスにより２つのUPDATEがロック待ちしないケース 表１ C1 UPDATE 表1 SET C2=X WHERE C1=2 1 2 3 4 5 C2 A B C D E UPDATE 表1 SET C2=X WHERE C1=5 索引 列 Systems Solution & Web Server Competence Center No.1 DM Group 索引を使わず異なる行のUPDATEでロック待ちが発生するケース 後から実行された右側のUPDATEは表全体のC2列を読み取り、２番目の行のXロックの開 放までロック待ちします これは悪いことではありません、次のページのような効果があります 表スキャン 表１ C1 (先) UPDATE 表1 SET C2=X WHERE C2=B 1 2 3 4 5 C2 A B C D E 未コミット行に取れらた排他ロックの開放を待つ (後) UPDATE 表1 SET C2=X WHERE C2=E COMMIT 開放 Systems Solution & Web Server Competence Center No.1 DM Group 41-42 COMMIT 分離レベルCSの2つのトランザクションによる更新結果の厳密性 DB2で分離レベルCSで未コミット行の読み取りを許さないため、左と右は同じ結果が得られます DB2では分離レベルの定義に従った正しい結果を提供するためにロックの仕組みを使っています Oracle 8.1.6では左と右で同じ結果が得られません 処理前の表 1 2 3 UPDATE 1 -> 2 COMMIT UPDATE 2 -> 3 COMMIT 処理前の表 1 2 3 UPDATE 1 -> 2 COMMIT UPDATE 2 -> 3 COMMIT ２トランザクションが前後して処理した後の表 ２トランザクションが直列に処理した後の表 3 3 3 結果表,DB2 Oracle共同じ 3 3 3 DB2での結果 Systems Solution & Web Server Competence Center No.1 DM Group 43-44 2 3 3 Oracle8.1.6での結果