Issue Details (XML | Word | Printable)

Key: NUCRDBMS-253
Type: New Feature New Feature
Status: Resolved Resolved
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: Wednesday 12:47 PM   Resolved: Wednesday 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.

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)

Yang ZHONG made changes - 02/Sep/09 11:11 PM
Field Original Value New Value
Attachment 253.zip [ 11011 ]
Andy Jefferson made changes - 03/Sep/09 05:01 AM
Issue Type Bug [ 1 ] New Feature [ 2 ]
Priority Blocker [ 1 ] Major [ 3 ]
Andy Jefferson made changes - 10/Dec/09 01:00 PM
Summary  SerializeRead/useUpdateLock "for update" shouldn't have been appended to all selects DB2 : SerializeRead/useUpdateLock "for update" shouldn't have been appended to all selects
Andy Jefferson made changes - 29/Oct/13 11:13 AM
Comment [ You were asked to raise feature request. As told many times before, this is no blocker ]
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.

Andy Jefferson made changes - 26/Nov/14 12:47 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.1.0.m1 [ 12311 ]
Resolution Fixed [ 1 ]