UPDLOCKメモ
環境
クエリ発行時点でロックされる場合
PKを全て指定(※1)
Connection con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
String sql = "SELECT foo FROM foobar WITH(UPDLOCK, NOWAIT)"
+ " WHERE foo = '1' AND bar = '2'";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
※1)ただし、暗黙の型変換が発生するような指定だと行参照までロックされない。(例だと「foo = 1」ないし「bar = 2」になっていた場合)
行参照時点でロックされる場合
PKの指定が一部(もしくは指定なし)
Connection con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
String sql = "SELECT foo FROM foobar WITH(UPDLOCK, NOWAIT)" +
+ " WHERE foo = '1'";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
rs.getString(1);
}
パラメータ化クエリ
Connection con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
String sql = "SELECT foo FROM foobar WITH(UPDLOCK, NOWAIT)"
+ " WHERE foo = ? AND bar = ?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "1");
st.setString(2, "2");
ResultSet rs = st.executeQuery();
while (rs.next()) {
rs.getString(1);
}
この場合のロック状況
executeQuery実行後
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
54 5 0 0 DB S GRANT
54 5 2130106629 0 TAB IX GRANT
next実行後
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
54 5 0 0 DB S GRANT
54 5 2130106629 2 KEY (04e6ade71aad) U GRANT
54 5 2130106629 2 PAG 1:1087 IU GRANT
54 5 2130106629 0 TAB IX GRANT
U = Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.