Locking, for update ......
Jesus M. Salvo Jr.
ASE version:
Adaptive Server Enterprise/12.5.0.3/EBF 10972 ESD#1/P/Sun_svr4/OS
5.8/rel12503/1919/32-bit/FBO/Tue Mar 25 02:54:09 2003
JConnect Version:
jConnect (TM) for JDBC(TM)/5.5(Build 25164)/P/EBF11098/JDK12/Wed Apr 16
18:28:00 2003
JDBC URL:
jdbc:sybase:Tds:192.168.0.80:4100?BE_AS_JDBC_COMPLIANT_AS_POSSIBLE=true&DYNAMIC_PREPARE=true&SELECT_OPENS_CURSOR=false&JCONNECT_VERSION=6
According to the Transact-SQL User's Guide, 'select ... for update' only
works inside a cursor or stored procedure.
However, the JDBC user's guide have the SELECT_OPENS_CURSOR option,
which accroding to the docs:
"If set to true, calls to Statement.executeQuery( ) will automatically
generate a cursor when the query contains a “FOR UPDATE” clause".
Now what I am trying to do is to exclusive-lock a row using the
following SQL in Java:
select id from test_lock_table holdlock where id = ? for update
The table test_lock_table is created via lock datarows.
1)
Notice that I used "for update" in the SQL ... and set
"SELECT_OPEN_CURSOR=false" in the JDBC connection. Although
SELECT_OPEN_CURSOR=false, the above SQL still seems to work ... and it
actually locks the row until the transaction is either rolled back or
committed. ( Verified via sp_lock and sp_who )
To be honest, I am surprised, as I have thought it would have failed
since a cursor will not be created ... and "for update" is specified.
2)
If SELECT_OPEN_CURSOR is set to true, then a strange exception is generated:
java.lang.StringIndexOutOfBoundsException: String index out of range: 51
at java.lang.String.substring(String.java:1522)
at
com.sybase.jdbc2.jdbc.ParamManager.processParamMarkers(ParamManager.java:827)
at com.sybase.jdbc2.tds.TdsCursor.tdsCursor(TdsCursor.java:514)
at com.sybase.jdbc2.tds.TdsCursor.open(TdsCursor.java:183)
at
com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1528)
at
com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:85)
at SybaseLockTest.run(SybaseLockTest.java:99)
3)
If SELECT_OPEN_CURSOR is set back to false, and "for update" is __not__
included in the SQL, 2 processes obtain a shared lock ( verified via
sp_lock ) on the table even though holdlock is specified... which is
really not what I want. I want an exclusive row lock or an update row lock.
4)
If SELECT_OPEN_CURSOR is set back to false, and "for update" is included
back .. but holdlock is __not__ included in the SQL, there are actually
no locks made ( verified via sp_lock ) ... which is I guess is expected
behaviour, since holdlock is removed from the SQL, but which is again
what not I wanted.
Is what I am seeing for all 4 cases the correct / intended behaviour ?
If anyone wants to, I have the test source code.
J
<jms...@yahoo.com.au> wrote:
I don't have the answers but some comments are made inline.
J
>
>
>ASE version:
>
>Adaptive Server Enterprise/12.5.0.3/EBF 10972 ESD#1/P/Sun_svr4/OS
>5.8/rel12503/1919/32-bit/FBO/Tue Mar 25 02:54:09 2003
>
>
>JConnect Version:
>
>jConnect (TM) for JDBC(TM)/5.5(Build 25164)/P/EBF11098/JDK12/Wed Apr 16
>18:28:00 2003
>
>JDBC URL:
>jdbc:sybase:Tds:192.168.0.80:4100?BE_AS_JDBC_COMPLIANT_AS_POSSIBLE=true&DYNAMIC_PREPARE=true&SELECT_OPENS_CURSOR=false&JCONNECT_VERSION=6
>
>
>
>According to the Transact-SQL User's Guide, 'select ... for update' only
>works inside a cursor or stored procedure.
>
I suspect they are referring to server-side cursors, not client
cursors.
>However, the JDBC user's guide have the SELECT_OPENS_CURSOR option,
>which accroding to the docs:
>
>"If set to true, calls to Statement.executeQuery( ) will automatically
>generate a cursor when the query contains a “FOR UPDATE” clause".
>
>
>
>
>
>Now what I am trying to do is to exclusive-lock a row using the
>following SQL in Java:
>
> select id from test_lock_table holdlock where id = ? for update
>
>The table test_lock_table is created via lock datarows.
>
>
>1)
>
>Notice that I used "for update" in the SQL ... and set
>"SELECT_OPEN_CURSOR=false" in the JDBC connection. Although
>SELECT_OPEN_CURSOR=false, the above SQL still seems to work ... and it
>actually locks the row until the transaction is either rolled back or
>committed. ( Verified via sp_lock and sp_who )
>
>To be honest, I am surprised, as I have thought it would have failed
>since a cursor will not be created ... and "for update" is specified.
>
I would agree with you but it appears the "for update" is just ignored
as no cursor (server-side) is being declared. I would think the
parsing should generate an error but perhaps it is parsed since it is
proper in some contexts and just ignored.
The "holdlock" is going to prevent anyone else from altering the value
since it guarantees repeatable read.
>
>
>2)
>
>If SELECT_OPEN_CURSOR is set to true, then a strange exception is generated:
>
>
>java.lang.StringIndexOutOfBoundsException: String index out of range: 51
> at java.lang.String.substring(String.java:1522)
> at
>com.sybase.jdbc2.jdbc.ParamManager.processParamMarkers(ParamManager.java:827)
> at com.sybase.jdbc2.tds.TdsCursor.tdsCursor(TdsCursor.java:514)
> at com.sybase.jdbc2.tds.TdsCursor.open(TdsCursor.java:183)
> at
>com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1528)
> at
>com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:85)
> at SybaseLockTest.run(SybaseLockTest.java:99)
>
>
>3)
>
>If SELECT_OPEN_CURSOR is set back to false, and "for update" is __not__
>included in the SQL, 2 processes obtain a shared lock ( verified via
>sp_lock ) on the table even though holdlock is specified... which is
>really not what I want. I want an exclusive row lock or an update row lock.
>
I think the holdlock is meant to guarantee the same value can be
re-read = repeatable read.
>
>4)
>
>If SELECT_OPEN_CURSOR is set back to false, and "for update" is included
>back .. but holdlock is __not__ included in the SQL, there are actually
>no locks made ( verified via sp_lock ) ... which is I guess is expected
>behaviour, since holdlock is removed from the SQL, but which is again
>what not I wanted.
>
I think this is just the same case as #1.