わいえむねっと

環境

• SQL Server 2008 R2
• JDK 6

クエリ発行時点でロックされる場合

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);

行参照時点でロックされる場合

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

2014/06/29 19:38:16

Referer

2014/06/27 01:15:27

2014/06/27 01:25:37