JPAの同時実行制御とロック #ccc_r15 #jjug_ccc

JJUG CCC 2014 Spring Java EE 7対応！ JPAの同時実行制御とロック 2014/05/18 (株)ウチダ人材開発センタ 多田真敏 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 1

このセッションについて Twitterハッシュタグ : #ccc_r15 JPAの同時実行制御とロックについて、基礎から 詳しく説明します 同時実行制御とロックにおいて、JPA実装と DBMSを変えたときの、挙動の違いや注意点を 説明します Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 2

アジェンダ まずは自己紹介 同時実行制御とロックとは？ JPAにおける同時実行制御とロック 今回のサンプルプログラム EclipseLink×MySQLの場合 EclipseLink×PostgreSQLの場合 Hibernate×MySQLの場合 Hibernate×PostgreSQLの場合 まとめ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 3

まずは自己紹介 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 4

あんた、誰？ IT研修インストラクター、 社会人7年目。三十路。 専門はJava、.NET、ネット ワーク、業務知識など。 中小企業診断士、SJCWC、応用情報技術者。 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 5

Twitter・ブログ Twitter : @suke_masa ブログ : Java EE 事始め！ http://masatoshitada.hatenadiary.jp/ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 6

• http://masatoshitada.hatenadiary.jp/

同時実行制御とロックとは？ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 7

ロックとは？ 複数のトランザクションから同時に実行された とき、データの不整合が起こらないように行・表 ・DBにロックをかける、DBMSの機能 ○ T1 empno 101 ename Nishida sal 500000 102 103 Nohira Kiyama 285000 245000 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. × T2 8

ロックなしの場合に起こる不整合 2つのトランザクションか empno ename sal Nishida 500000 ら同時に、empno=101 101 Nohira 285000 のsalを10000プラスする 102 103 ① T1：101のsalを検索 （500000） ② T2：101のsalを検索 （500000） ③ T1：salを①+10000に更 新（510000） ④ T2：salを②+10000に更 新（510000） Kiyama 245000 本当は520000 にならないと おかしい！ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 9

READロックとWRITEロック ① READロック（共有ロック） 他のトランザクションはREADロックは取得できるが 、WRITEロックは取得できない ② WRITEロック（占有ロック、排他ロック） 他のトランザクションはREADロックもWRITEロック も取得できない 後のトランザ READロック クション WRITEロック 先のトランザクション READロック WRITEロック ○ × × × Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 10

JPAにおける 同時実行制御とロック Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 11

楽観的ロックと悲観的ロック 楽観的ロック（OPTIMISTIC） 更新対象の行に対して、他のトランザクションからの更新 は無いという前提に立つ パフォーマンスは悲観的ロックと比較して高い 悲観的ロック（PESSIMISTIC） 更新対象の行に対してロックをかけ、他のトランザクショ ンからの更新をDBMSが防ぐ パフォーマンスは楽観的ロックと比較して低い 楽観的ロック → DBMSのロック機能を利用しない 悲観的ロック → DBMSのロック機能を利用する Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 12

バージョニング 「バージョン」を表す列を対象テーブルに追加 エンティティのバージョンフィールドには@Version アノテーションを付加 バージョンの型はint（Integer）、short（Short）、long （Long）、java.sql.Timestampが使用可能 更新時にバージョンの値が更新（インクリメントなど ）される Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 13

LockModeType ロックモード OPTIMISTIC OPTIMISTIC_FORCE_ INCREMENT PESSIMISTIC_READ PESSIMISTIC_WRITE PESSIMISTIC_FORCE _INCREMENT NONE 説明 楽観的ロック 楽観的ロック。バージョンの値をインクリメ ントする 悲観的ロック。READロックを取得する 悲観的ロック。WRITEロックを取得する 悲観的ロック。WRITEロックを取得し、 バージョンの値をインクリメントする ロックしない（デフォルト） ※READ・WRITEは非推奨 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 14

LockModeTypeの指定方法 EntityManagerインターフェイス <T> T find(Class<T> entityClass, Object primaryKey, LockModeType lockMode) void lock(Object entity, LockModeType lockMode) void refresh(Object entity, LockModeType lockMode) Query（TypedQuery）インターフェイス Query setLockMode(LockModeType lockMode) NamedQueryアノテーション lockmode属性 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 15

金魚本では・・・ 「4.5 同時実行」は6ページしかない 悲観的ロックについては0.5ページ程度 Java EE 5までは楽観的ロックしかなかった Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 16

参考資料 Java EE 7 Tutorial 42. Controlling Concurrent Access to Entity Data With Rocking JSR-338 Java Persistence 2.1 3.4 Locking and Concurrency Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 17

SPECIAL THANKS!! O社認定講師＆データベーススペシャリストの Y部長 外語大卒の後輩Kちゃん Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 18

今回のサンプルプログラム Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 19

実験環境 JPA実装 EclipseLink 2.5.2-M1 Hibernate Entity Manager 4.3.1.Final DBMS PostgreSQL 8.4.19 MySQL 5.6.16 2×2 ＝4通り その他 Windows 7 SP1(x64) JDK 7u45 Eclipse 4.3.1 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 20

使用するテーブル emp2 empno=101のレコードを検索後、 そのレコードのsalを10000プラスする処理を、 2スレッドから同時実行 empno（PK） 101 102 103 ename Nishida Nohira Kiyama sal 500000 285000 245000 version 1 1 1 バージョン列 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 21

エンティティクラス @Entity public class Emp2 implements Serializable { @Id private Integer empno; private String ename; version列と private Integer sal; 対応するフィールド @Version private Integer version; // setter/getter, equals(), hashCode(), toString() } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 22

スレッドクラス public class UpdateThread extends Thread { ここでLockModeType @Override を指定 public void run() { String threadName = Thread.currentThread().getName(); EntityManagerFactory factory = Persistence.createEntityManagerFactory("emp"); EntityManager manager = factory.createEntityManager(); EntityTransaction tx = manager.getTransaction(); tx.begin(); Emp2 emp = manager.find(Emp2.class, 101, LockModeType.NONE); int sal = emp.getSal(); System.out.println(threadName + " 検索直後 " + emp); try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 23

スレッドクラス try { emp.setSal(sal + 10000); System.out.println(threadName + " flush直前 " + emp); manager.flush(); System.out.println(threadName + " flush直後 " + emp); tx.commit(); System.out.println(threadName + " commit."); System.out.println(threadName + " commit直後 " + emp); } catch (Exception e) { System.out.println(threadName + " 例外発生:" + e.getClass().getName()); e.printStackTrace(); tx.rollback(); System.out.println(threadName + " rollback."); } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 24

スレッドクラス } } System.out.println(threadName + " 終了 " + emp); manager.close(); factory.close(); Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 25

メインクラス public class Main { public static void main(String[] args) { Thread thread1 = new UpdateThread(); thread1.setName("T1"); Thread thread2 = new UpdateThread(); thread2.setName("T2"); thread1.start(); thread2.start(); マルチスレッドで } 同時実行 } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 26

EclipseLink×MySQLの場合 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 27

NONE ロックしない（デフォルト） バージョニングは不要なので、@Versionをコメン トアウト @Entity public class Emp2 implements Serializable { ・・・ // @Version コメントアウト private Integer version; ・・・ } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 28

NONEの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 29

実行の様子 ① T1が検索→sal=500000 ② T2が検索→sal=500000 ③ T1がsal=500000+10000で更新 ④ T2がsal=500000+10000で更新 salは最終的に510000になる Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 30

OPTIMISTIC 楽観的ロック バージョニング必須 バージョンが無い場合はPersistenceException @Entity public class Emp2 implements Serializable { ・・・ @Version // 必須 private Integer version; ・・・ } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 31

OPTIMISTICの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] （以下、スタックトレース） Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 32

実行の様子 ① T1が検索→sal=500000, version=1 ② T2が検索→sal=500000, version=1 ③ T1がsal=500000+10000, version=1+1 で更新→コミット ④ T2がsal=500000+10000, version=1+1 で更新 →バージョンの値が既に変更されているため、 OptimisticLockExceptionが発生してロー ルバック Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 33

実行後のバージョン値 ロールバックされたエンティティも、バージョン値 がインクリメントされる T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 34

バージョン値変更の検知方法 UPDATE文のWHERE句に主キーとバージョン列 を指定している →バージョン値が既に変更されていた場合、 更新行数はゼロになる [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 35

OPTIMISTIC_FORCE_INCREMENT 楽観的ロック。バージョン値を強制的にインクリメ ントする バージョニング必須 EclipseLinkの場合、挙動はOPTIMISTICと全く 同じ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 36

OPTIMISTIC_FORCE_INCREMENTの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 例外発生:javax.persistence.OptimisticLockException T1 rollback. T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 37

PESSIMISTIC_READ 悲観的ロック。READロックを取得 →EclipseLinkではSELECT FOR UPDATE文が 実行され、WRITEロックを取得する バージョニング不要 @Entity public class Emp2 implements Serializable { ・・・ // @Version コメントアウト private Integer version; ・・・ } Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 38

PESSIMISTIC_READの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [520000, 101] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 39

実行の様子 ① T1が検索、WRTIEロックを取得 →sal=500000 ② T2が検索しようとするが、T1がWRITEロックを取 得しているので、DB内の待ち行列に入る ③ T1がsal=500000+10000で更新 →コミットしたらロック解放 ④ T2が待ち行列から出て検索、WRITEロックを取得 →sal=500000 ⑤ T2がsal=500000+10000で更新 →コミットしたらロック解放 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 40

SQLログと実際の実行順序は違う SQLログは、JPAがDBMSに発行した「つもり」の SQLが表示される 実際には、発行されてもDBMS内の待ち行列に 入っている場合がある [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] ・・・ T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] ・・・ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 41

PESSIMISTIC_WRITE 悲観的ロック。WRITEロックを取得 →EclipseLinkではPESSIMISTIC_READと 挙動は同じ バージョニング不要 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 42

PESSIMISTIC_WRITEの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [520000, 101] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 43

PESSIMISTIC_FORCE_INCREMENT 悲観的ロック。バージョン値を強制的にインクリメ ントする チュートリアルにはWRITEロックかREADロック か記述されていない →JSRには「exclusive lock（＝WRITEロック）」 と書いてある バージョニング必須 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 44

PESSIMISTIC_FORCE_INCREMENTの実行ログ [EL Fine]: sql:SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=2] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [520000, 3, 101, 2] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] T2 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 45

実行の様子 ① T1が検索、WRTIEロックを取得 →sal=500000, version = 1 ② T2が検索しようとするが、T1がWRITEロックを取 得しているので、DB内の待ち行列に入る ③ T1がsal=500000+10000, version=1+1で更新 →コミットしたらロック解放 ④ T2が待ち行列から出て検索、WRITEロックを取得 →sal=510000, version=2 ⑤ T2がsal=510000+10000, version=2+1で更新 →コミットしたらロック解放 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 46

EclipseLink×PostgreSQL の場合 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 47

EclipseLink×MySQLと挙動は同じ 以下、ログのみ記載 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 48

NONEの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 49

OPTIMISTICの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] （以下、スタックトレース） Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 50

OPTIMISTIC_FORCE_INCREMENTの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 51

PESSIMISTIC_READの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [520000, 101] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 52

PESSIMISTIC_WRITEの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [510000, 101] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ? WHERE (EMPNO = ?) bind => [520000, 101] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 53

PESSIMISTIC_FORCE_INCREMENTの実行ログ [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] [EL Fine]: sql: SELECT EMPNO, ENAME, SAL, VERSION FROM EMP2 WHERE (EMPNO = ?) FOR UPDATE bind => [101] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [510000, 2, 101, 1] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=2] [EL Fine]: sql: UPDATE EMP2 SET SAL = ?, VERSION = ? WHERE ((EMPNO = ?) AND (VERSION = ?)) bind => [520000, 3, 101, 2] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] T1 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=3] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 54

Hibernate×MySQLの場合 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 55

NONE 発行されるSQLが微妙に異なるが、最終結果は EclipseLinkと同じ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 56

NONEの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 57

OPTIMISTIC ロールバックされたエンティティのバージョンはイ ンクリメントされない Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 58

OPTIMISTICの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: select version from Emp2 where empno =? T2 例外発生:javax.persistence.OptimisticLockException T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 59

実行後のバージョン値 ロールバックされたエンティティは、バージョン値 がインクリメントされない T2 例外発生:javax.persistence.OptimisticLockException T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 60

謎のSELECT文 T1がcommit時に発行している →コミット前に、他のトランザクションからバージ ョンが変更されていないかどうかを確かめるため と思われる T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: select version from Emp2 where empno =? T2 例外発生:javax.persistence.OptimisticLockException Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 61

OPTIMISTIC_FORCE_INCREMENT flush時とcommit時の2回、バージョンがインクリ メントされる 「謎のSELECT文」によるバージョン確認は無い Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 62

OPTIMISTIC_FORCE_INCREMENTの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: update Emp2 set version=? where empno=? and version=? T1 例外発生:javax.persistence.OptimisticLockException T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T1 rollback. T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 63

実行後のバージョン値 flush時・commit時の2回、バージョンがインクリメントされ る T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: update Emp2 set version=? where empno=? and version=? T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 64

PESSIMISTIC_READ SELECT・・・LOCK IN SHARE MODE文が実行さ れ、READロックが取得される →T1とT2が互いにロック解放待ちとなり、 デッドロック発生 →片方はロールバックされ、 もう片方のみがコミットされる Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 65

PESSIMISTIC_READの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? lock in share mode Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? lock in share mode T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] ERROR: Deadlock found when trying to get lock; try restarting transaction T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 例外発生:javax.persistence.PersistenceException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 66

PESSIMISTIC_WRITE SELECT FOR UPDATE文が実行され、WRITEロ ックが取得される →片方のトランザクションがコミット・ロック解放 後、もう片方のトランザクションも実行される Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 67

PESSIMISTIC_WRITEの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 68

PESSIMISTIC_FORCE_INCREMENT SELECT FOR UPDATE文が実行され、WRITEロ ックが取得される →片方のトランザクションがコミット・ロック解放 後、もう片方のトランザクションも実行される OPTIMISTIC_FORCE_INCREMENTと同様、 flush時・commit時の2回、バージョンがインクリ メントされる Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 69

PESSIMISTIC_FORCE_INCREMENTの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update Hibernate: update Emp2 set version=? where empno=? and version=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=2] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] Hibernate: update Emp2 set version=? where empno=? and version=? T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=4] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=4] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=5] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=5] Copyrightename=Nishida, UCHIDA HUMAN DEVELOPMENT all rights reserved. 70 T2 終了 Emp2 [empno=101, sal=520000, version=5]

Hibernate×PostgreSQLの場合 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 71

Hibernate×PostgreSQL PESSIMISTIC_FORCE_INCREMENTのみ、 MySQLの場合と挙動が異なる READロックはSELECT FOR SHARE文 上記以外は同じなので、ログのみ記載 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 72

NONEの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 73

OPTIMISTICの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: select version from Emp2 where empno =? T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 74

OPTIMISTIC_FORCE_INCREMENTの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: update Emp2 set version=? where empno=? and version=? T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T2 例外発生:javax.persistence.OptimisticLockException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 75

PESSIMISTIC_READの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for share Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for share T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] ERROR: ERROR: deadlock detected T2 例外発生:javax.persistence.PersistenceException T2 rollback. T2 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 76

PESSIMISTIC_WRITEの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=1] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 検索直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=1] T2 flush直前 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? T2 flush直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 commit. T2 commit直後 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] T2 終了 Emp2 [empno=101, ename=Nishida, sal=520000, version=1] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 77

PESSIMISTIC_FORCE_INCREMENT SELECT FOR UPDATE文にNOWAITオプション が付加される →後からのトランザクションは、待つことができな いため例外発生 NOWAITオプションはMySQLには無い Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 78

PESSIMISTIC_FORCE_INCREMENTの実行ログ Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update nowait ERROR: ERROR: could not obtain lock on row in relation "emp2" Hibernate: select emp2x0_.empno as empno1_0_0_, emp2x0_.ename as ename2_0_0_, emp2x0_.sal as sal3_0_0_, emp2x0_.version as version4_0_0_ from Emp2 emp2x0_ where emp2x0_.empno=? for update nowait Hibernate: update Emp2 set version=? where empno=? and version=? T1 検索直後 Emp2 [empno=101, ename=Nishida, sal=500000, version=2] T1 flush直前 Emp2 [empno=101, ename=Nishida, sal=510000, version=2] Hibernate: update Emp2 set ename=?, sal=?, version=? where empno=? and version=? T1 flush直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T1 commit. T1 commit直後 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] T1 終了 Emp2 [empno=101, ename=Nishida, sal=510000, version=3] Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 79

まとめ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 80

まとめ ロックモード OPTIMISTIC 相違点 ・EclipseLinkでは、ロールバックされたエン ティティのバージョン値もインクリメントされる OPTIMISTIC_FORC ・Hibernateでは、flush時とcommit時の2回、 E_INCREMENT バージョンがインクリメントされる PESSIMISTIC_REA ・EclipseLinkでは、READロックではなく D WRITEロックになる PESSIMISTIC_FOR ・Hibernateでは、flush時とcommit時の2回、 CE_INCREMENT バージョンがインクリメントされる ・Hibernate×PostgreSQLでは、SELECT FOR UPDATE文にNOWAITオプションが付加 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 81

開発時の注意 必ずSQLログを出力して、一通りの挙動を確認し ておきましょう！ JPAのSQLログの順番は、実際にDB内で実行さ れる順番とは異なる場合があるので、注意しま しょう！ Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 82

今回、入れられなかった内容 悲観的ロックのタイムアウト javax.persistence.lock.timeout 悲観的ロックのスコープ javax.persistence.lock.scope Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 83

技術の普及＝教育の普及 技術の 普及 教育の 普及 Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 84

Copyright UCHIDA HUMAN DEVELOPMENT all rights reserved. 85