わいえむねっと

2014/06
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

2014/06/29 Sun

UPDLOCKメモ

環境

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

※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

sp_lock (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms187749.aspx

Mode nvarchar(8) The lock mode requested. Can be:

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.