Issue Details (XML | Word | Printable)

Key: NUCRDBMS-253
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Yang ZHONG
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

DB2 : SerializeRead/useUpdateLock "for update" shouldn't have been appended to all selects

Created: 02/Sep/09 11:10 PM   Updated: 30/Nov/14 02:16 PM   Resolved: 26/Nov/14 12:47 PM
Component/s: None
Affects Version/s: 1.1.5
Fix Version/s: 4.1.0.m1

File Attachments: 1. Zip Archive 253.zip (3 kB)

Environment: Java 5, Linux

Forum Thread URL: HTTP://WWW.DataNucleus.org/servlet/forum/viewthread_thread,5745
Datastore: IBM DB2
Severity: Production


 Description  « Hide
setSerializeRead(HTTP://WWW.DataNucleus.org/products/accessplatform_1_1/jdo/transaction_types.html#Pessimistic__Datastore__Transactions)
or "datanucleus.rdbms.useUpdateLock"(HTTP://WWW.DataNucleus.org/products/accessplatform_1_1/rdbms/persistence_properties.html#RDBMS_Persistence_Properties) appends "for update" to selects some of which however don't allow "for update".

Thanks to Erik for having pointed out that in DB2, FOR UPDATE cannot be used under the following conditions:

On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:
    * The DISTINCT keyword
    * A column function in the SELECT list
    * A GROUP BY, HAVING, or ORDER BY clause
    * A FROM clause that identifies one of the following:
          o More than one table or view
          o A read-only view
          o An OUTER clause with a typed table or typed view
          o A table or view that is also identified in the FROM clause of a subquery of the SELECT statement. (NOTE: This is only a restriction on version of DB2 prior to Version 2).
    * A set operator (other than UNION ALL).
Note that these conditions do not apply to subqueries of the SELECT statement.

Sort Order: Ascending order - Click to sort in descending order
Yang ZHONG added a comment - 02/Sep/09 11:11 PM
The last Exception:

javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT 'org.datanucleus.test.A' AS NUCLEUS_TYPE,THIS.S,THIS.A_ID FROM A THIS WHERE THIS.S = ? UNION SELECT 'org.datanucleus.test.B' AS NUCLEUS_TYPE,THIS.S,THIS.A_ID FROM A THIS WHERE THIS.S = ? FOR UPDATE" : DB2 SQL Error: SQLCODE=-511, SQLSTATE=42829, SQLERRMC=null, DRIVER=3.53.95.
        at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:304)
        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:230)
        at org.datanucleus.test.Main.main(Main.java:77)
NestedThrowablesStackTrace:
com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-511, SQLSTATE=42829, SQLERRMC=null, DRIVER=3.53.95
        at com.ibm.db2.jcc.b.bd.a(bd.java:676)
        at com.ibm.db2.jcc.b.bd.a(bd.java:60)
        at com.ibm.db2.jcc.b.bd.a(bd.java:127)
        at com.ibm.db2.jcc.b.gm.c(gm.java:2484)
        at com.ibm.db2.jcc.b.gm.d(gm.java:2461)
        at com.ibm.db2.jcc.b.gm.a(gm.java:1962)
        at com.ibm.db2.jcc.t4.db.g(db.java:138)
        at com.ibm.db2.jcc.t4.db.a(db.java:38)
        at com.ibm.db2.jcc.t4.t.a(t.java:32)
        at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
        at com.ibm.db2.jcc.b.gm.bb(gm.java:1933)
        at com.ibm.db2.jcc.b.hm.ec(hm.java:2558)
        at com.ibm.db2.jcc.b.hm.e(hm.java:3259)
        at com.ibm.db2.jcc.b.hm.Rb(hm.java:583)
        at com.ibm.db2.jcc.b.hm.executeQuery(hm.java:557)
        at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:364)
        at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:450)
        at org.datanucleus.store.rdbms.query.SQLEvaluator.evaluate(SQLEvaluator.java:121)
        at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:287)
        at org.datanucleus.store.query.Query.executeQuery(Query.java:1489)
        at org.datanucleus.store.rdbms.query.JDOQLQuery.executeQuery(JDOQLQuery.java:244)
        at org.datanucleus.store.query.Query.executeWithArray(Query.java:1371)
        at org.datanucleus.store.query.Query.execute(Query.java:1344)
        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:221)
        at org.datanucleus.test.Main.main(Main.java:77)

Andy Jefferson added a comment - 26/Nov/14 12:47 PM
GitHub puts a check on addition of FOR UPDATE for several of those DB2 restrictions.