You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "David Wisneski (JIRA)" <ji...@apache.org> on 2007/03/26 19:23:32 UTC
[jira] Created: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
------------------------------------------------------------------
Key: OPENJPA-182
URL: https://issues.apache.org/jira/browse/OPENJPA-182
Project: OpenJPA
Issue Type: New Feature
Components: jdbc
Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
Reporter: David Wisneski
Assigned To: David Wisneski
A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
RE: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Patrick Linskey <pl...@bea.com>.
> We were unaware that fetch plan properties could be set as Hints. Now
> that we look at the code we see this, but we did not find this when
> we read the OpenJPA documentation.
Yeah, it seems like we should probably do some documentation of some of
these newer configuration options.
> The only hint we found in the
> documentation was the OracleSelectHint and so our design was based on
> how OracleSelectHint was implemented. OracleSelectHint is not
> implemented as a FetchPlan property.
FWIW, OracleSelectHint is implemented as it is because it's a proper
hint -- it only affects how Oracle performs the selects, has no real
side effects, and is fully Oracle-specific.
> Why don't you integrate your patch and then we will rerun our tests to
> make sure the function works as we had originally intended.
Are there any test cases that you guys could make available so that I
could experiment with whether or not the lock level checks are
necessary, or if they're duplicate code? Also, we should address Abe's
concern about whether the isolation level stuff is useful to databases
other than just DB2. My assumption has been that it is, but it'd be good
to confirm that this could be used more widely before making it a
published API.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: David Wisneski [mailto:wisneskid@gmail.com]
> Sent: Thursday, April 05, 2007 11:35 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: [jira] Commented: (OPENJPA-182) db2 update lock
> syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
>
> Patrick,
>
> We were unaware that fetch plan properties could be set as Hints. Now
> that we look at the code we see this, but we did not find this when
> we read the OpenJPA documentation. The only hint we found in the
> documentation was the OracleSelectHint and so our design was based on
> how OracleSelectHint was implemented. OracleSelectHint is not
> implemented as a FetchPlan property.
>
> Why don't you integrate your patch and then we will rerun our tests to
> make sure the function works as we had originally intended.
>
>
> On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
> >
> > [
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atl
assian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_1248704
6 ]
> >
> > Patrick Linskey commented on OPENJPA-182:
> > -----------------------------------------
> >
> > > > Hopefully, this will be useful for applications where
> there are "hot" tables
> > > > that require pessimistic locking even though the rest
> of the application
> > > > does better with optimistic.
> > >
> > > That's what our lock levels and lock APIs are for. I'm
> still not clear on what this is
> > > adding to the mix for most DBs.
> >
> > Not really -- the lock levels allow the user to configure
> how locking should happen, not what the isolation level
> should be for the locks.
> >
> > I don't know about what levels of support non-DB2 databases
> have for per-query isolation level configuration. Does anyone
> have any experience with this in other databases?
> >
> > Oh, and regardless, we should change the base DBDictionary
> to throw an exception if this FetchPlan setting is set but
> not serviceable.
> >
> > One thing that we should test: I'm not convinced that the
> lock level override in the DB2Dictionary code is necessary.
> It's possible that the LockManager will already take into
> account the current JDBCFetchConfiguration's lock level
> settings when specifying the forUpdate setting for the
> toSelect() clause. Some test cases will make it easy to
> figure out the answer to this question.
> >
> > > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > > ------------------------------------------------------------------
> > >
> > > Key: OPENJPA-182
> > > URL:
> https://issues.apache.org/jira/browse/OPENJPA-182
> > > Project: OpenJPA
> > > Issue Type: New Feature
> > > Components: jdbc
> > > Environment: db2 database driver for zOS, AS400,
> Unix, Windows, Linux
> > > Reporter: David Wisneski
> > > Assigned To: David Wisneski
> > > Attachments: OPENJPA-182.patch, openJPA182.patch
> > >
> > >
> > > A while back we changed the syntax of update locking from
> FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS.
> Additional changes are required because
> > > 1. if isolation=serializable is configured, then the
> syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> > > 2. when using DB2/400 on iSeries machines, the syntax is
> WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP
> EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> > > 3. DB2 supports both a FETCH FIRST ROWS and update
> LOCKS clauses.
> > > So we change supportsLockingWithSelectRange = true in the
> AbstractDB2Dictionary class and change the DB2Dictionary to
> append the correct LOCKS syntax depending on vendor, release
> and isolation level.
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the issue online.
> >
> >
>
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
RE: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Patrick Linskey <pl...@bea.com>.
> Why don't you integrate your patch and then we will rerun our tests to
> make sure the function works as we had originally intended.
I just did this in r525997. I also added a couple of test cases.
TestSelectForUpdateOverride asserts that the DBDictionary is called with
the appropriate forUpdate setting based on fetch configuration and
transaction data; TestIsolationLevelOverride tests the behavior of the
new JDBCFetchPlan.setIsolationLevel() API.
I committed this on the assumption that Abe will agree that since a
number of databases support this concept, it makes sense to have a
common API for it.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: David Wisneski [mailto:wisneskid@gmail.com]
> Sent: Thursday, April 05, 2007 11:35 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: [jira] Commented: (OPENJPA-182) db2 update lock
> syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
>
> Patrick,
>
> We were unaware that fetch plan properties could be set as Hints. Now
> that we look at the code we see this, but we did not find this when
> we read the OpenJPA documentation. The only hint we found in the
> documentation was the OracleSelectHint and so our design was based on
> how OracleSelectHint was implemented. OracleSelectHint is not
> implemented as a FetchPlan property.
>
> Why don't you integrate your patch and then we will rerun our tests to
> make sure the function works as we had originally intended.
>
>
> On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
> >
> > [
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atl
assian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_1248704
6 ]
> >
> > Patrick Linskey commented on OPENJPA-182:
> > -----------------------------------------
> >
> > > > Hopefully, this will be useful for applications where
> there are "hot" tables
> > > > that require pessimistic locking even though the rest
> of the application
> > > > does better with optimistic.
> > >
> > > That's what our lock levels and lock APIs are for. I'm
> still not clear on what this is
> > > adding to the mix for most DBs.
> >
> > Not really -- the lock levels allow the user to configure
> how locking should happen, not what the isolation level
> should be for the locks.
> >
> > I don't know about what levels of support non-DB2 databases
> have for per-query isolation level configuration. Does anyone
> have any experience with this in other databases?
> >
> > Oh, and regardless, we should change the base DBDictionary
> to throw an exception if this FetchPlan setting is set but
> not serviceable.
> >
> > One thing that we should test: I'm not convinced that the
> lock level override in the DB2Dictionary code is necessary.
> It's possible that the LockManager will already take into
> account the current JDBCFetchConfiguration's lock level
> settings when specifying the forUpdate setting for the
> toSelect() clause. Some test cases will make it easy to
> figure out the answer to this question.
> >
> > > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > > ------------------------------------------------------------------
> > >
> > > Key: OPENJPA-182
> > > URL:
> https://issues.apache.org/jira/browse/OPENJPA-182
> > > Project: OpenJPA
> > > Issue Type: New Feature
> > > Components: jdbc
> > > Environment: db2 database driver for zOS, AS400,
> Unix, Windows, Linux
> > > Reporter: David Wisneski
> > > Assigned To: David Wisneski
> > > Attachments: OPENJPA-182.patch, openJPA182.patch
> > >
> > >
> > > A while back we changed the syntax of update locking from
> FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS.
> Additional changes are required because
> > > 1. if isolation=serializable is configured, then the
> syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> > > 2. when using DB2/400 on iSeries machines, the syntax is
> WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP
> EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> > > 3. DB2 supports both a FETCH FIRST ROWS and update
> LOCKS clauses.
> > > So we change supportsLockingWithSelectRange = true in the
> AbstractDB2Dictionary class and change the DB2Dictionary to
> append the correct LOCKS syntax depending on vendor, release
> and isolation level.
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the issue online.
> >
> >
>
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
Re: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by David Wisneski <wi...@gmail.com>.
Patrick,
We were unaware that fetch plan properties could be set as Hints. Now
that we look at the code we see this, but we did not find this when
we read the OpenJPA documentation. The only hint we found in the
documentation was the OracleSelectHint and so our design was based on
how OracleSelectHint was implemented. OracleSelectHint is not
implemented as a FetchPlan property.
Why don't you integrate your patch and then we will rerun our tests to
make sure the function works as we had originally intended.
On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
>
> [ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487046 ]
>
> Patrick Linskey commented on OPENJPA-182:
> -----------------------------------------
>
> > > Hopefully, this will be useful for applications where there are "hot" tables
> > > that require pessimistic locking even though the rest of the application
> > > does better with optimistic.
> >
> > That's what our lock levels and lock APIs are for. I'm still not clear on what this is
> > adding to the mix for most DBs.
>
> Not really -- the lock levels allow the user to configure how locking should happen, not what the isolation level should be for the locks.
>
> I don't know about what levels of support non-DB2 databases have for per-query isolation level configuration. Does anyone have any experience with this in other databases?
>
> Oh, and regardless, we should change the base DBDictionary to throw an exception if this FetchPlan setting is set but not serviceable.
>
> One thing that we should test: I'm not convinced that the lock level override in the DB2Dictionary code is necessary. It's possible that the LockManager will already take into account the current JDBCFetchConfiguration's lock level settings when specifying the forUpdate setting for the toSelect() clause. Some test cases will make it easy to figure out the answer to this question.
>
> > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > ------------------------------------------------------------------
> >
> > Key: OPENJPA-182
> > URL: https://issues.apache.org/jira/browse/OPENJPA-182
> > Project: OpenJPA
> > Issue Type: New Feature
> > Components: jdbc
> > Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> > Reporter: David Wisneski
> > Assigned To: David Wisneski
> > Attachments: OPENJPA-182.patch, openJPA182.patch
> >
> >
> > A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> > 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> > 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> > 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> > So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
RE: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Patrick Linskey <pl...@bea.com>.
> So Essentially we need to pass this subselect flag to
> getForUpdateClause
> method.I am not sure how would we do that without overriding
> the method in
> DB2Dictionary or chnaging the signatures in DBDictionary
> which would affect
> many other files
Understood. I think that this is a bit of a tough question. On the one
hand, I hate to see lots of code duplication. On the other hand, it's
annoying to have unneeded concepts in other DBDictionaries.
Personally, I think that I prefer adding the boolean to the DBDictionary
method signature, or otherwise changing the DBDictionary method
signature to pass along a select or something from which many of the
boolean values could probably be inferred, or some other
DBDictionary-level refactoring.
Does anyone else have an opinion?
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: Ritika Maheshwari [mailto:ritikamster@gmail.com]
> Sent: Wednesday, April 18, 2007 9:57 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: [jira] Commented: (OPENJPA-182) db2 update lock
> syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
>
> 1. OK I think I will open a new issue for the bug
> 2. I will redo the formatting
> 3.To be able to use DBDictionary method we will have to
> change the signature
> of the
>
> toSelect(SQLBuffer selects, JDBCFetchConfiguration
> fetch,SQLBuffer from,
> SQLBuffer where, SQLBuffer group,SQLBuffer having, SQLBuffer
> order,*boolean*distinct,
> *boolean* forUpdate, *long* start, *long* end)
>
> to include a boolean flag subselect which will be computed in the
>
> SQLBuffer toSelect(Select sel, *boolean*
> forUpdate,JDBCFetchConfiguration
> fetch)
>
> since we have the handle to the Select here.This needs to be
> done only for
> DB2 because only in case of DB2 even if forUpdate is false(
> which is set to
> false correctly for subselects by
> SQLBuffer.resolveSubselects()) we need to
> append a FOR READ ONLY clause except in case of subselects.In other
> databases if forUpdate is false we do not append any update
> or FOR READ ONLY
> CLAUSE. So this situaton is unique to db2.
>
> So Essentially we need to pass this subselect flag to
> getForUpdateClause
> method.I am not sure how would we do that without overriding
> the method in
> DB2Dictionary or chnaging the signatures in DBDictionary
> which would affect
> many other files
>
>
>
>
> On 4/17/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
> >
> >
> > [
> >
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atl
assian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_1248955
8]
> >
> > Patrick Linskey commented on OPENJPA-182:
> > -----------------------------------------
> >
> > Some comments:
> >
> > 1. I don't think that we should be doing work on resolved
> issues. So, this
> > should be re-opened, or (preferably) a new issue should be
> opened for this
> > new bug.
> >
> > 2. The patch you attached does not use OpenJPA-style
> formatting. We don't
> > have a style guide spelled out as well as we probably
> should, but we always
> > put spaces after commas, we indent 4 spaces on continuation
> lines, and we
> > put a space between an 'if' and the open paren.
> >
> > 3. It's a shame to have to do all this code duplication between
> > DBDictionary and DB2Dictionary. To what extent can we refactor
> > DBDictionary's methods to make this concept work out better for
> > DB2Dictionary?
> >
> > > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > > ------------------------------------------------------------------
> > >
> > > Key: OPENJPA-182
> > > URL:
> https://issues.apache.org/jira/browse/OPENJPA-182
> > > Project: OpenJPA
> > > Issue Type: New Feature
> > > Components: jdbc
> > > Environment: db2 database driver for zOS, AS400,
> Unix, Windows,
> > Linux
> > > Reporter: David Wisneski
> > > Fix For: 0.9.7
> > >
> > > Attachments: JIRA182-subselect.patch, OPENJPA-182.patch,
> > OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
> > >
> > >
> > > A while back we changed the syntax of update locking from
> FOR UPDATE
> > OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional
> changes are required
> > because
> > > 1. if isolation=serializable is configured, then the
> syntax should
> > be WITH RR USE AND KEEP UDPATE LOCKS
> > > 2. when using DB2/400 on iSeries machines, the syntax is
> WITH RS USE
> > AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE
> LOCKS because
> > DB2/400 only supports read or exclusive locks.
> > > 3. DB2 supports both a FETCH FIRST ROWS and update
> LOCKS clauses.
> > > So we change supportsLockingWithSelectRange = true in the
> > AbstractDB2Dictionary class and change the DB2Dictionary to
> append the
> > correct LOCKS syntax depending on vendor, release and
> isolation level.
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the issue online.
> >
> >
>
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
Re: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Ritika Maheshwari <ri...@gmail.com>.
1. OK I think I will open a new issue for the bug
2. I will redo the formatting
3.To be able to use DBDictionary method we will have to change the signature
of the
toSelect(SQLBuffer selects, JDBCFetchConfiguration fetch,SQLBuffer from,
SQLBuffer where, SQLBuffer group,SQLBuffer having, SQLBuffer
order,*boolean*distinct,
*boolean* forUpdate, *long* start, *long* end)
to include a boolean flag subselect which will be computed in the
SQLBuffer toSelect(Select sel, *boolean* forUpdate,JDBCFetchConfiguration
fetch)
since we have the handle to the Select here.This needs to be done only for
DB2 because only in case of DB2 even if forUpdate is false( which is set to
false correctly for subselects by SQLBuffer.resolveSubselects()) we need to
append a FOR READ ONLY clause except in case of subselects.In other
databases if forUpdate is false we do not append any update or FOR READ ONLY
CLAUSE. So this situaton is unique to db2.
So Essentially we need to pass this subselect flag to getForUpdateClause
method.I am not sure how would we do that without overriding the method in
DB2Dictionary or chnaging the signatures in DBDictionary which would affect
many other files
On 4/17/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
>
>
> [
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12489558]
>
> Patrick Linskey commented on OPENJPA-182:
> -----------------------------------------
>
> Some comments:
>
> 1. I don't think that we should be doing work on resolved issues. So, this
> should be re-opened, or (preferably) a new issue should be opened for this
> new bug.
>
> 2. The patch you attached does not use OpenJPA-style formatting. We don't
> have a style guide spelled out as well as we probably should, but we always
> put spaces after commas, we indent 4 spaces on continuation lines, and we
> put a space between an 'if' and the open paren.
>
> 3. It's a shame to have to do all this code duplication between
> DBDictionary and DB2Dictionary. To what extent can we refactor
> DBDictionary's methods to make this concept work out better for
> DB2Dictionary?
>
> > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > ------------------------------------------------------------------
> >
> > Key: OPENJPA-182
> > URL: https://issues.apache.org/jira/browse/OPENJPA-182
> > Project: OpenJPA
> > Issue Type: New Feature
> > Components: jdbc
> > Environment: db2 database driver for zOS, AS400, Unix, Windows,
> Linux
> > Reporter: David Wisneski
> > Fix For: 0.9.7
> >
> > Attachments: JIRA182-subselect.patch, OPENJPA-182.patch,
> OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
> >
> >
> > A while back we changed the syntax of update locking from FOR UPDATE
> OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required
> because
> > 1. if isolation=serializable is configured, then the syntax should
> be WITH RR USE AND KEEP UDPATE LOCKS
> > 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE
> AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because
> DB2/400 only supports read or exclusive locks.
> > 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> > So we change supportsLockingWithSelectRange = true in the
> AbstractDB2Dictionary class and change the DB2Dictionary to append the
> correct LOCKS syntax depending on vendor, release and isolation level.
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
Re: [jira] Updated: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Ritika Maheshwari <ri...@gmail.com>.
>Finally, I replicated the logic in DB2Dictionary, but I noticed that
sometimes the logic checked >for "serializable" and sometimes it checked for
"read-uncommitted". I preserved these checks, >but was this intentional? I'm
not all that clear about what the optimizations are, so just wanted >to
ensure that that wasn't a typo.
No it is not a typo.It was intended.
On 4/4/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
>
>
> [
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel]
>
> Patrick Linskey updated OPENJPA-182:
> ------------------------------------
>
> Attachment: OPENJPA-182.patch
>
> For the sake of discussion, I've attached an alternate patch that uses a
> new JDBCFetchPlan.setIsolationLevel() instead of a hint for isolation
> level, and uses JDBCFetchConfiguration.getReadLockLevel() to determine
> whether or not to do a SELECT ... FOR UPDATE.
>
> If the read lock level is set to LockLevels.LEVEL_WRITE, then the FOR
> UPDATE is included; if the read lock level is set to LockLevels.LEVEL_READ,
> then no FOR UPDATE is used. If the read lock level is
> LockLevels.LEVEL_NONE, then the default behavior is used. (This is
> possibly not the best use of LEVEL_NONE -- I'm not sure if LEVEL_NONE means
> 'default' or something else. But for the purposes of demonstration, it
> seemed expedient to use it. Adding a new LEVEL_DEFAULT constant might make
> more sense.)
>
> Also, I directly reused the java.sql.Connection constants, which is
> possibly non-ideal; we might want to discuss making our own constants. Or
> not.
>
> So, in this model, if there were a test case for this stuff, I would have
> changed the test case to do:
>
> Query q = em.createQuery(...);
> JDBCFetchPlan plan = (JDBCFetchPlan) ((OpenJPAQuery)
> query).getFetchPlan();
> plan.setIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
> plan.setReadLockMode(LockModeType.WRITE); // force a FOR UPDATE
> List l = q.getResultList();
>
> Note also that this model allows the isolation level and read lock mode to
> be configured on the EM itself, for use in find() calls and relationship
> lookups, and as the default settings for the fetch plans of queries created
> from the EM.
>
> Finally, I replicated the logic in DB2Dictionary, but I noticed that
> sometimes the logic checked for "serializable" and sometimes it checked for
> "read-uncommitted". I preserved these checks, but was this intentional? I'm
> not all that clear about what the optimizations are, so just wanted to
> ensure that that wasn't a typo.
>
> > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > ------------------------------------------------------------------
> >
> > Key: OPENJPA-182
> > URL: https://issues.apache.org/jira/browse/OPENJPA-182
> > Project: OpenJPA
> > Issue Type: New Feature
> > Components: jdbc
> > Environment: db2 database driver for zOS, AS400, Unix, Windows,
> Linux
> > Reporter: David Wisneski
> > Assigned To: David Wisneski
> > Attachments: OPENJPA-182.patch, openJPA182.patch
> >
> >
> > A while back we changed the syntax of update locking from FOR UPDATE
> OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required
> because
> > 1. if isolation=serializable is configured, then the syntax should
> be WITH RR USE AND KEEP UDPATE LOCKS
> > 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE
> AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because
> DB2/400 only supports read or exclusive locks.
> > 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> > So we change supportsLockingWithSelectRange = true in the
> AbstractDB2Dictionary class and change the DB2Dictionary to append the
> correct LOCKS syntax depending on vendor, release and isolation level.
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
Re: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Ritika Maheshwari <ri...@gmail.com>.
I think I mentioned the wrong place but in SQLServer the isolation level can
be specified as a table hint. The Table hint specifies that the query
optimizer use a table scan, one or more indexes, or a locking method with
this table or view and for this SELECT, INSERT, UPDATE, or DELETE statement.
The table hint is specified in the FROM Clause. The Table hint syntax is
[image: Syntax]Syntax<http://msdn2.microsoft.com/en-us/library/ms187373.aspx#>
<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
On 4/5/07, Patrick Linskey <pl...@bea.com> wrote:
>
> But both of those settings are per-transaction things, not per-query
> things. The DB2 isolation level syntax differs in that it is part of the
> SQL statement issued, rather than a separate configuration for the
> transaction-wide isolation level.
>
> Theoretically, the JDBC transaction-level isolation level settings that
> OpenJPA performs should be equivalent to these SET TRANSACTION SQL
> statements, right?
>
> -Patrick
>
> --
> Patrick Linskey
> BEA Systems, Inc.
>
> _______________________________________________________________________
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
> > -----Original Message-----
> > From: Ritika Maheshwari [mailto:ritikamster@gmail.com]
> > Sent: Thursday, April 05, 2007 1:18 PM
> > To: open-jpa-dev@incubator.apache.org
> > Subject: Re: [jira] Commented: (OPENJPA-182) db2 update lock
> > syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> >
> > Informix has the following
> >
> > SET TRANSACTION
> > <http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/co
> > m.ibm.sqls.doc/sqls02.htm#ToC_987>
> > Use
> > the SET TRANSACTION statement to define the isolation level
> > and to specify
> > whether the access mode of a transaction is read-only or read-write.
> > Syntax
> >
> > [image: Read syntax diagram][image: Skip visual syntax diagram]
> > <http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/co
> > m.ibm.sqls.doc/sqls815.htm?resultof=%22%74%72%61%6e%73%61%63%7
> 4%69%6f%6e%22%20%22%74%72%61%6e%73%61%63%74%22%20%22%69%73%6f%>
> 6c%61%74%69%6f%6e%22%20%22%69%73%6f%6c%22%20#skipsyn-276>>>-SET
> > TRANSACTION---------------------------------------------->
> >
> > .-,------------------------------------------------.
> > V (1) |
> > >----+--------+-READ WRITE-+------------------------+-+--------><
> > | '-READ ONLY--' |
> > | (1) |
> > '--------ISOLATION LEVEL--+-READ COMMITTED---+-'
> > +-REPEATABLE READ--+
> > +-SERIALIZABLE-----+
> > '-READ UNCOMMITTED-'
> >
> >
> >
> >
> > SQLServer has the following
> >
> > [image:
> > Syntax]Syntax<http://msdn2.microsoft.com/en-us/library/ms173763.aspx#>
> >
> > SET TRANSACTION ISOLATION LEVEL
> > { READ UNCOMMITTED
> > | READ COMMITTED
> > | REPEATABLE READ
> > | SNAPSHOT
> > | SERIALIZABLE
> > }
> > [ ; ]
> >
> > On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
> > >
> > >
> > > [
> > >
> > https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atl
> assian.jira.plugin.system.issuetabpanels:comment->
> tabpanel#action_12487086]
> > >
> > > Patrick Linskey commented on OPENJPA-182:
> > > -----------------------------------------
> > >
> > > > > I know that Oracle allows you to add a FOR UPDATE clause
> > > > > to a query, and this affects the results of that query. In Sun
> > > > > appserver CMP we use this to set exclusive locks on rows
> > > > > where we want pessimistic locking behavior just for
> > certain tables.
> > > >
> > > > Again, this is exactly what our existing lock levels and APIs do.
> > >
> > > The current patches in this issue encompass two separate bits of
> > > functionality:
> > >
> > > 1. an update-override setting. (In the context of my patch, I'm not
> > > convinced that this is necessary, since I suspect that the
> > code that calls
> > > toSelect() might set the forUpdate boolean based on the
> > values in the
> > > JDBCFetchConfiguration anyways.)
> > >
> > > 2. an isolation-override setting.
> > >
> > > Currently, this patch implements all of this only in the
> > DB2Dictionary. As
> > > of right now, the first feature is something that is useful
> > for all sorts of
> > > databases, via syntax like Oracle's SELECT ... FOR UPDATE.
> > However, we only
> > > know how to implement the second feature for DB2, and not
> > for any other
> > > database. In Oracle, "ALTER SESSION" can be used to change
> > the isolation
> > > level of a given session, but I'm not sure of the semantics of this
> > > operation. I believe that Abe's question is: Do other
> > databases (Sybase?
> > > Derby?) also have semantics for changing the isolation
> > level of a particular
> > > query?
> > >
> > > > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > > > ------------------------------------------------------------------
> > > >
> > > > Key: OPENJPA-182
> > > > URL:
> > https://issues.apache.org/jira/browse/OPENJPA-182
> > > > Project: OpenJPA
> > > > Issue Type: New Feature
> > > > Components: jdbc
> > > > Environment: db2 database driver for zOS, AS400,
> > Unix, Windows,
> > > Linux
> > > > Reporter: David Wisneski
> > > > Assigned To: David Wisneski
> > > > Attachments: OPENJPA-182.patch, openJPA182.patch
> > > >
> > > >
> > > > A while back we changed the syntax of update locking from
> > FOR UPDATE
> > > OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional
> > changes are required
> > > because
> > > > 1. if isolation=serializable is configured, then the
> > syntax should
> > > be WITH RR USE AND KEEP UDPATE LOCKS
> > > > 2. when using DB2/400 on iSeries machines, the syntax is
> > WITH RS USE
> > > AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE
> > LOCKS because
> > > DB2/400 only supports read or exclusive locks.
> > > > 3. DB2 supports both a FETCH FIRST ROWS and update
> > LOCKS clauses.
> > > > So we change supportsLockingWithSelectRange = true in the
> > > AbstractDB2Dictionary class and change the DB2Dictionary to
> > append the
> > > correct LOCKS syntax depending on vendor, release and
> > isolation level.
> > >
> > > --
> > > This message is automatically generated by JIRA.
> > > -
> > > You can reply to this email to add a comment to the issue online.
> > >
> > >
> >
>
> Notice: This email message, together with any attachments, may contain
> information of BEA Systems, Inc., its subsidiaries and affiliated
> entities, that may be confidential, proprietary, copyrighted and/or
> legally privileged, and is intended solely for the use of the individual or
> entity named in this message. If you are not the intended recipient, and
> have received this message in error, please immediately return this by email
> and then delete it.
>
RE: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Patrick Linskey <pl...@bea.com>.
But both of those settings are per-transaction things, not per-query
things. The DB2 isolation level syntax differs in that it is part of the
SQL statement issued, rather than a separate configuration for the
transaction-wide isolation level.
Theoretically, the JDBC transaction-level isolation level settings that
OpenJPA performs should be equivalent to these SET TRANSACTION SQL
statements, right?
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: Ritika Maheshwari [mailto:ritikamster@gmail.com]
> Sent: Thursday, April 05, 2007 1:18 PM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: [jira] Commented: (OPENJPA-182) db2 update lock
> syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
>
> Informix has the following
>
> SET TRANSACTION
> <http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/co
> m.ibm.sqls.doc/sqls02.htm#ToC_987>
> Use
> the SET TRANSACTION statement to define the isolation level
> and to specify
> whether the access mode of a transaction is read-only or read-write.
> Syntax
>
> [image: Read syntax diagram][image: Skip visual syntax diagram]
> <http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/co
> m.ibm.sqls.doc/sqls815.htm?resultof=%22%74%72%61%6e%73%61%63%7
4%69%6f%6e%22%20%22%74%72%61%6e%73%61%63%74%22%20%22%69%73%6f%>
6c%61%74%69%6f%6e%22%20%22%69%73%6f%6c%22%20#skipsyn-276>>>-SET
> TRANSACTION---------------------------------------------->
>
> .-,------------------------------------------------.
> V (1) |
> >----+--------+-READ WRITE-+------------------------+-+--------><
> | '-READ ONLY--' |
> | (1) |
> '--------ISOLATION LEVEL--+-READ COMMITTED---+-'
> +-REPEATABLE READ--+
> +-SERIALIZABLE-----+
> '-READ UNCOMMITTED-'
>
>
>
>
> SQLServer has the following
>
> [image:
> Syntax]Syntax<http://msdn2.microsoft.com/en-us/library/ms173763.aspx#>
>
> SET TRANSACTION ISOLATION LEVEL
> { READ UNCOMMITTED
> | READ COMMITTED
> | REPEATABLE READ
> | SNAPSHOT
> | SERIALIZABLE
> }
> [ ; ]
>
> On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
> >
> >
> > [
> >
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atl
assian.jira.plugin.system.issuetabpanels:comment->
tabpanel#action_12487086]
> >
> > Patrick Linskey commented on OPENJPA-182:
> > -----------------------------------------
> >
> > > > I know that Oracle allows you to add a FOR UPDATE clause
> > > > to a query, and this affects the results of that query. In Sun
> > > > appserver CMP we use this to set exclusive locks on rows
> > > > where we want pessimistic locking behavior just for
> certain tables.
> > >
> > > Again, this is exactly what our existing lock levels and APIs do.
> >
> > The current patches in this issue encompass two separate bits of
> > functionality:
> >
> > 1. an update-override setting. (In the context of my patch, I'm not
> > convinced that this is necessary, since I suspect that the
> code that calls
> > toSelect() might set the forUpdate boolean based on the
> values in the
> > JDBCFetchConfiguration anyways.)
> >
> > 2. an isolation-override setting.
> >
> > Currently, this patch implements all of this only in the
> DB2Dictionary. As
> > of right now, the first feature is something that is useful
> for all sorts of
> > databases, via syntax like Oracle's SELECT ... FOR UPDATE.
> However, we only
> > know how to implement the second feature for DB2, and not
> for any other
> > database. In Oracle, "ALTER SESSION" can be used to change
> the isolation
> > level of a given session, but I'm not sure of the semantics of this
> > operation. I believe that Abe's question is: Do other
> databases (Sybase?
> > Derby?) also have semantics for changing the isolation
> level of a particular
> > query?
> >
> > > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > > ------------------------------------------------------------------
> > >
> > > Key: OPENJPA-182
> > > URL:
> https://issues.apache.org/jira/browse/OPENJPA-182
> > > Project: OpenJPA
> > > Issue Type: New Feature
> > > Components: jdbc
> > > Environment: db2 database driver for zOS, AS400,
> Unix, Windows,
> > Linux
> > > Reporter: David Wisneski
> > > Assigned To: David Wisneski
> > > Attachments: OPENJPA-182.patch, openJPA182.patch
> > >
> > >
> > > A while back we changed the syntax of update locking from
> FOR UPDATE
> > OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional
> changes are required
> > because
> > > 1. if isolation=serializable is configured, then the
> syntax should
> > be WITH RR USE AND KEEP UDPATE LOCKS
> > > 2. when using DB2/400 on iSeries machines, the syntax is
> WITH RS USE
> > AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE
> LOCKS because
> > DB2/400 only supports read or exclusive locks.
> > > 3. DB2 supports both a FETCH FIRST ROWS and update
> LOCKS clauses.
> > > So we change supportsLockingWithSelectRange = true in the
> > AbstractDB2Dictionary class and change the DB2Dictionary to
> append the
> > correct LOCKS syntax depending on vendor, release and
> isolation level.
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the issue online.
> >
> >
>
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
Re: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH USE AND KEEP UPDATE LOCKS
Posted by Ritika Maheshwari <ri...@gmail.com>.
Informix has the following
SET TRANSACTION
<http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls02.htm#ToC_987>
Use
the SET TRANSACTION statement to define the isolation level and to specify
whether the access mode of a transaction is read-only or read-write.
Syntax
[image: Read syntax diagram][image: Skip visual syntax diagram]
<http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls815.htm?resultof=%22%74%72%61%6e%73%61%63%74%69%6f%6e%22%20%22%74%72%61%6e%73%61%63%74%22%20%22%69%73%6f%6c%61%74%69%6f%6e%22%20%22%69%73%6f%6c%22%20#skipsyn-276>>>-SET
TRANSACTION---------------------------------------------->
.-,------------------------------------------------.
V (1) |
>----+--------+-READ WRITE-+------------------------+-+--------><
| '-READ ONLY--' |
| (1) |
'--------ISOLATION LEVEL--+-READ COMMITTED---+-'
+-REPEATABLE READ--+
+-SERIALIZABLE-----+
'-READ UNCOMMITTED-'
SQLServer has the following
[image: Syntax]Syntax<http://msdn2.microsoft.com/en-us/library/ms173763.aspx#>
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
On 4/5/07, Patrick Linskey (JIRA) <ji...@apache.org> wrote:
>
>
> [
> https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487086]
>
> Patrick Linskey commented on OPENJPA-182:
> -----------------------------------------
>
> > > I know that Oracle allows you to add a FOR UPDATE clause
> > > to a query, and this affects the results of that query. In Sun
> > > appserver CMP we use this to set exclusive locks on rows
> > > where we want pessimistic locking behavior just for certain tables.
> >
> > Again, this is exactly what our existing lock levels and APIs do.
>
> The current patches in this issue encompass two separate bits of
> functionality:
>
> 1. an update-override setting. (In the context of my patch, I'm not
> convinced that this is necessary, since I suspect that the code that calls
> toSelect() might set the forUpdate boolean based on the values in the
> JDBCFetchConfiguration anyways.)
>
> 2. an isolation-override setting.
>
> Currently, this patch implements all of this only in the DB2Dictionary. As
> of right now, the first feature is something that is useful for all sorts of
> databases, via syntax like Oracle's SELECT ... FOR UPDATE. However, we only
> know how to implement the second feature for DB2, and not for any other
> database. In Oracle, "ALTER SESSION" can be used to change the isolation
> level of a given session, but I'm not sure of the semantics of this
> operation. I believe that Abe's question is: Do other databases (Sybase?
> Derby?) also have semantics for changing the isolation level of a particular
> query?
>
> > db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> > ------------------------------------------------------------------
> >
> > Key: OPENJPA-182
> > URL: https://issues.apache.org/jira/browse/OPENJPA-182
> > Project: OpenJPA
> > Issue Type: New Feature
> > Components: jdbc
> > Environment: db2 database driver for zOS, AS400, Unix, Windows,
> Linux
> > Reporter: David Wisneski
> > Assigned To: David Wisneski
> > Attachments: OPENJPA-182.patch, openJPA182.patch
> >
> >
> > A while back we changed the syntax of update locking from FOR UPDATE
> OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required
> because
> > 1. if isolation=serializable is configured, then the syntax should
> be WITH RR USE AND KEEP UDPATE LOCKS
> > 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE
> AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because
> DB2/400 only supports read or exclusive locks.
> > 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> > So we change supportsLockingWithSelectRange = true in the
> AbstractDB2Dictionary class and change the DB2Dictionary to append the
> correct LOCKS syntax depending on vendor, release and isolation level.
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487099 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
Between Ritika's SQLServer email on the dev list and Mike's Sybase research, it sounds like this feature is supported by enough databases that I think it's useful to expose as an API, rather than just a DB2-specific hint.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Ritika Maheshwari (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12485312 ]
Ritika Maheshwari commented on OPENJPA-182:
-------------------------------------------
1. The updateFlag is set to true when the JPA query is running in pessimistic mode.Where as with hint even when u are in optimistic mode you can get the update flags.This way user can use the standard JPA api setHint and get the lock clause.Where as in the other case they need to use OpenJPA specific api.Besides hint is per query basis.We also needed the hints for Static SQL and Named Queries.
2. I do not understand what u mean by rule versus hint.Isolation level hint tells us whther to add WITH RR or WITH RS.And again this is per query basis where as the isolation hint through the persistence.xml is to broad applying to all the entities.Then again we need the hints for NamedQuery and Static SQL.
3. Agreed
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Abe White (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487085 ]
Abe White commented on OPENJPA-182:
-----------------------------------
> I know that Oracle allows you to add a FOR UPDATE clause to a query, and this affects the results of that query. In Sun appserver CMP we use this to set exclusive locks on rows where we want pessimistic locking behavior just for certain tables.
Again, this is exactly what our existing lock levels and APIs do.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey resolved OPENJPA-182.
-------------------------------------
Resolution: Fixed
Assignee: (was: David Wisneski)
I think that, aside from documentation and DB2 test cases and impls for other data stores, we can put this issue to rest.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Fix For: 0.9.7
>
> Attachments: OPENJPA-182.patch, OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12485103 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
Wow... that's a lot more DB2 locking knowledge than I've ever seen in one place. Neat.
Some comments on the patch:
1. How does the openjpa.hint.updateClause hint differ from the value of the forUpdate flag passed in to the DBDictionary call? It looks like the existing OpenJPA per-transaction read / write lock level configuration could be used instead.
2. Is openjpa.hint.isolationLevel really a hint, or more of a rule? Again, I have a hunch that maybe we could do something with the read / write lock levels, or maybe some other means of controlling isolation level. In any event, it seems like isolation level isn't really a hint, but rather is more of a rule.
3. You introduced a number of public boolean fields for determining what type of DB2 instance is being used. Based on code inspection, it looks like you expect that it should always be possible to automatically determine the type; maybe these should be private fields instead? We only have public fields in DBDictionaries for user-configurable settings. Also, since it looks like only one of the booleans can meaningfully be true, I'd rather see a single private db2ServerType field that will be set to one of several symbolic constant values. This will let you replace the if-else block with a switch block if you prefer that sort of thing, also.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487086 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
> > I know that Oracle allows you to add a FOR UPDATE clause
> > to a query, and this affects the results of that query. In Sun
> > appserver CMP we use this to set exclusive locks on rows
> > where we want pessimistic locking behavior just for certain tables.
>
> Again, this is exactly what our existing lock levels and APIs do.
The current patches in this issue encompass two separate bits of functionality:
1. an update-override setting. (In the context of my patch, I'm not convinced that this is necessary, since I suspect that the code that calls toSelect() might set the forUpdate boolean based on the values in the JDBCFetchConfiguration anyways.)
2. an isolation-override setting.
Currently, this patch implements all of this only in the DB2Dictionary. As of right now, the first feature is something that is useful for all sorts of databases, via syntax like Oracle's SELECT ... FOR UPDATE. However, we only know how to implement the second feature for DB2, and not for any other database. In Oracle, "ALTER SESSION" can be used to change the isolation level of a given session, but I'm not sure of the semantics of this operation. I believe that Abe's question is: Do other databases (Sybase? Derby?) also have semantics for changing the isolation level of a particular query?
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Ritika Maheshwari (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ritika Maheshwari updated OPENJPA-182:
--------------------------------------
Attachment: openJPA182.patch
here is a patch for this feature
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Craig Russell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487082 ]
Craig Russell commented on OPENJPA-182:
---------------------------------------
I know that Oracle allows you to add a FOR UPDATE clause to a query, and this affects the results of that query. In Sun appserver CMP we use this to set exclusive locks on rows where we want pessimistic locking behavior just for certain tables.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Craig Russell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486997 ]
Craig Russell commented on OPENJPA-182:
---------------------------------------
Hopefully, this will be useful for applications where there are "hot" tables that require pessimistic locking even though the rest of the application does better with optimistic. Take the example of an Order/OrderLine where there are lots of updates of the Order and/or associated OrderLine. If several threads get the same Order, they will ultimately conflict and waste time. If the query to retrieve the Order is marked as pessimistic (WRITE) then these threads will serialize and all of their work will complete.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487098 ]
Michael Dick commented on OPENJPA-182:
--------------------------------------
I have no practical experience with Sybase, but I did find this in their TransactSQL user's guide :
"Changing the isolation level for a query
You can change the isolation level for a query by using the at isolation clause with the select or readtext statements. The at isolation clause supports isolation levels 0, 1, and 3. It does not support isolation level 2. The read uncommitted, read committed, and serializable options of at isolation represent isolation levels as listed below:
at isolation option Isolation level
read uncommited 0
read committed 1
serializable 3
For example, the following two statements query the same table at isolation levels 0 and 3, respectively:
select *
from titles
at isolation read uncommitted
select *
from titles
at isolation serializable"
There's more information online here: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/53911;hf=0
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey updated OPENJPA-182:
------------------------------------
Attachment: OPENJPA-182.patch
For the sake of discussion, I've attached an alternate patch that uses a new JDBCFetchPlan.setIsolationLevel() instead of a hint for isolation level, and uses JDBCFetchConfiguration.getReadLockLevel() to determine whether or not to do a SELECT ... FOR UPDATE.
If the read lock level is set to LockLevels.LEVEL_WRITE, then the FOR UPDATE is included; if the read lock level is set to LockLevels.LEVEL_READ, then no FOR UPDATE is used. If the read lock level is LockLevels.LEVEL_NONE, then the default behavior is used. (This is possibly not the best use of LEVEL_NONE -- I'm not sure if LEVEL_NONE means 'default' or something else. But for the purposes of demonstration, it seemed expedient to use it. Adding a new LEVEL_DEFAULT constant might make more sense.)
Also, I directly reused the java.sql.Connection constants, which is possibly non-ideal; we might want to discuss making our own constants. Or not.
So, in this model, if there were a test case for this stuff, I would have changed the test case to do:
Query q = em.createQuery(...);
JDBCFetchPlan plan = (JDBCFetchPlan) ((OpenJPAQuery) query).getFetchPlan();
plan.setIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
plan.setReadLockMode(LockModeType.WRITE); // force a FOR UPDATE
List l = q.getResultList();
Note also that this model allows the isolation level and read lock mode to be configured on the EM itself, for use in find() calls and relationship lookups, and as the default settings for the fetch plans of queries created from the EM.
Finally, I replicated the logic in DB2Dictionary, but I noticed that sometimes the logic checked for "serializable" and sometimes it checked for "read-uncommitted". I preserved these checks, but was this intentional? I'm not all that clear about what the optimizations are, so just wanted to ensure that that wasn't a typo.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey updated OPENJPA-182:
------------------------------------
Attachment: OPENJPA-182.patch
Remove forUpdate from DBDictionary.toOperation()'s signature; use a null check on forUpdateClause instead.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Fix For: 0.9.7
>
> Attachments: OPENJPA-182.patch, OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486821 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
It's probably worth noting that everything in the FetchPlan is configurable via the Query.setHint() APIs. My earlier theoretical test case would become:
Query q = em.createQuery(...);
q.setHint("openjpa.FetchPlan.IsolationLevel", Connection.TRANSACTION_SERIALIZABLE);
q.setHint("openjpa.FetchPlan.ReadLockMode", LockModeType.WRITE);
List l = q.getResultList();
In other words, if we use the patch that I attached (or something like it), we will end up with strong typing, API alignment with similar concepts, and dynamic accessibility both in terms of Query.setHint() and @QueryHints annotations / query-hint XML.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Abe White (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487000 ]
Abe White commented on OPENJPA-182:
-----------------------------------
> Hopefully, this will be useful for applications where there are "hot" tables that require pessimistic locking even though the rest of the application does better with optimistic.
That's what our lock levels and lock APIs are for. I'm still not clear on what this is adding to the mix for most DBs.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486819 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
In the hint-based approach, you could set the hints on a particular query.
In my approach, you could configure the FetchPlan for a particular query (get the Query's fetch plan), or for a particular entity manager (get the EM's fetch plan). To do different fetch plans for different queries, you'd just set the different query fetch plans differently.
Query fetch plans are a copy-on-write semantic -- they start off just delegating through to the EM's fetch plan, and if you make changes, then they immediately make a copy of the EM's fetch plan, and remain isolated from the EM's fetch plan from then on out.
If you wanted to do different isolation levels for different find() calls or different relationship traversals, in my impl, you'd need to set the EM's fetch plan, do the work, then set it back. In the hint-based impl, you would not be able to do any configuration for find() or relationship traversals.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Craig Russell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486812 ]
Craig Russell commented on OPENJPA-182:
---------------------------------------
A use-case for the isolation level is to support different locking semantics for different classes and possibly for different queries. To use Patrick's patch would I need to use a different fetch plan before issuing a find or a query and then set it back after that method call? Or can I specify an isolation level in the FetchConfiguration per class?
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Ritika Maheshwari (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ritika Maheshwari updated OPENJPA-182:
--------------------------------------
Attachment: openjpa182TestCase.jar
Here is a jar containing the 2 entities on whcih I run my testcases.
em.getTransaction().begin()
Query qryA = em.createQuery("select d from DeptBean2 d where d.no = 1");
qryA.setHint("openjpa.hint.updateClause",true);
qryA.setHint("openjpa.hint.isolationLevel", "serializable");
List rsA = qryA.getResultList();
The SQL Output looks like
13109 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing prepstmnt 694036830 SELECT t0.no, t1.empid, t2.no, t2.mgr_empid, t2.cdname, t1.ceemp_ts, t1.cehireDate, t1.ceismanager, t1.cename, t1.cesalary, t0.cdname FROM deptab2 t0 LEFT OUTER JOIN emptab2 t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN deptab2 t2 ON t1.dept_no = t2.no WHERE (CAST(t0.no AS BIGINT) = CAST(? AS BIGINT)) WITH RR USE AND KEEP UPDATE LOCKS [params=(long) 1]
13119 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [10 ms] spent
27420 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing prepstmnt 2102295886 SELECT t0.empid, t1.no, t2.empid, t2.dept_no, t2.ceemp_ts, t2.cehireDate, t2.ceismanager, t2.cename, t2.cesalary, t1.cdname, t0.ceemp_ts, t0.cehireDate, t0.ceismanager, t0.cename, t0.cesalary FROM emptab2 t0 LEFT OUTER JOIN deptab2 t1 ON t0.dept_no = t1.no LEFT OUTER JOIN emptab2 t2 ON t1.mgr_empid = t2.empid WHERE t0.dept_no = ? WITH RR USE AND KEEP UPDATE LOCKS [params=(int) 1]
27430 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [10 ms] spent
Query qryB = em.createQuery("select e from EmpBean2 e where e.empid = 1");
qryB.setHint("openjpa.hint.updateClause",true);
qryB.setHint("openjpa.hint.isolationLevel", "read-uncommitted");
List rsB = qryB.getResultList();
The SQL Output looks like
47969 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing prepstmnt 1530944320 SELECT t0.empid, t1.no, t2.empid, t2.dept_no, t2.ceemp_ts, t2.cehireDate, t2.ceismanager, t2.cename, t2.cesalary, t1.cdname, t0.ceemp_ts, t0.cehireDate, t0.ceismanager, t0.cename, t0.cesalary FROM emptab2 t0 LEFT OUTER JOIN deptab2 t1 ON t0.dept_no = t1.no LEFT OUTER JOIN emptab2 t2 ON t1.mgr_empid = t2.empid WHERE (CAST(t0.empid AS BIGINT) = CAST(? AS BIGINT)) WITH RS USE AND KEEP UPDATE LOCKS [params=(long) 1]
47969 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [0 ms] spent
Query qryC = em.createQuery("select d from DeptBean2 d where d.no = 1");
DeptBean2 deptC = (DeptBean2)qryC.getSingleResult();
The SQL Out put is
72695 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing prepstmnt 742009914 SELECT t0.no, t1.empid, t2.no, t2.mgr_empid, t2.cdname, t1.ceemp_ts, t1.cehireDate, t1.ceismanager, t1.cename, t1.cesalary, t0.cdname FROM deptab2 t0 LEFT OUTER JOIN emptab2 t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN deptab2 t2 ON t1.dept_no = t2.no WHERE (CAST(t0.no AS BIGINT) = CAST(? AS BIGINT)) FOR READ ONLY optimize for 1 row [params=(long) 1]
72695 dwtest TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [0 ms] spent
In my persistence.xml I had the following properties
<property name="openjpa.LockManager" value="pessimistic" />
<property name="openjpa.jdbc.TransactionIsolation" value="read-committed" />
Essentially if we are running against DB2 8.2 or Later then if update lock is true then for all the isolation levels other than "serializable" WITH RS USE AND KEEP UPDATE LOCKS clause is appended to the query.In case of "serializable" isolation level "WITH RR USE AND KEEP UPDATE LOCK" is appended.
If the updateLock is false then FOR READ ONLY clause is appended to all queries.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487046 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
> > Hopefully, this will be useful for applications where there are "hot" tables
> > that require pessimistic locking even though the rest of the application
> > does better with optimistic.
>
> That's what our lock levels and lock APIs are for. I'm still not clear on what this is
> adding to the mix for most DBs.
Not really -- the lock levels allow the user to configure how locking should happen, not what the isolation level should be for the locks.
I don't know about what levels of support non-DB2 databases have for per-query isolation level configuration. Does anyone have any experience with this in other databases?
Oh, and regardless, we should change the base DBDictionary to throw an exception if this FetchPlan setting is set but not serviceable.
One thing that we should test: I'm not convinced that the lock level override in the DB2Dictionary code is necessary. It's possible that the LockManager will already take into account the current JDBCFetchConfiguration's lock level settings when specifying the forUpdate setting for the toSelect() clause. Some test cases will make it easy to figure out the answer to this question.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-182:
---------------------------------
Fix Version/s: 0.9.7
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Fix For: 0.9.7
>
> Attachments: OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Abe White (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486987 ]
Abe White commented on OPENJPA-182:
-----------------------------------
What is setting the isolation level this way actually doing? For anything other than DB2 right now, it looks like it doesn't do anything. And even for DB2, it's unclear to me exactly what the isolation level hint is doing, and why the information can't be gleaned from the global setting or the connection. It seems very misleading to me to have a setIsolationLevel API (or generic "IsolationLevel" hint) that doesn't actually change the connection's isolation level.
If we can determine that this API is actually useful for more than DB2, and if we can name it appropriately for what it actually does, then I like Patrick's proposal of a FetchPlan API. The fact that all FetchPlan properties can be expressed as hints should make everyone happy. But if this is just a DB2 thing, then we should rename the hint to have a DB2-specific name and be done with it IMO.
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Attachments: OPENJPA-182.patch, openJPA182.patch
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12489558 ]
Patrick Linskey commented on OPENJPA-182:
-----------------------------------------
Some comments:
1. I don't think that we should be doing work on resolved issues. So, this should be re-opened, or (preferably) a new issue should be opened for this new bug.
2. The patch you attached does not use OpenJPA-style formatting. We don't have a style guide spelled out as well as we probably should, but we always put spaces after commas, we indent 4 spaces on continuation lines, and we put a space between an 'if' and the open paren.
3. It's a shame to have to do all this code duplication between DBDictionary and DB2Dictionary. To what extent can we refactor DBDictionary's methods to make this concept work out better for DB2Dictionary?
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Fix For: 0.9.7
>
> Attachments: JIRA182-subselect.patch, OPENJPA-182.patch, OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-182) db2 update lock syntax WITH
USE AND KEEP UPDATE LOCKS
Posted by "Ritika Maheshwari (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ritika Maheshwari updated OPENJPA-182:
--------------------------------------
Attachment: JIRA182-subselect.patch
we just discovered that there is a problem with this fix.The problem is that the FOR READ ONLY flag is getting generated for subselects as well.
Basically for DB2 if forUpdate was false we append FOR READ ONLY we need another check to see that it is not a subselect.To me it appears that in the toSelect method when SelectImpl is passed we check that the parent is null or not to figure out that it is a subselect and then pass another flag subselect to the getForUdateClause method where we say
if(!forUpdate && !subselect)
forUpdateString.append(forReadOnlyClause)
to achieve this we would probably have to override the toSelect methods in DB2Dictionary again unless there is a better way.
I am attaching a patch to specify the changes
> db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
> Key: OPENJPA-182
> URL: https://issues.apache.org/jira/browse/OPENJPA-182
> Project: OpenJPA
> Issue Type: New Feature
> Components: jdbc
> Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
> Reporter: David Wisneski
> Fix For: 0.9.7
>
> Attachments: JIRA182-subselect.patch, OPENJPA-182.patch, OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF to WITH RS USE AND KEEP UPDATE LOCKS. Additional changes are required because
> 1. if isolation=serializable is configured, then the syntax should be WITH RR USE AND KEEP UDPATE LOCKS
> 2. when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE LOCKS or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive locks.
> 3. DB2 supports both a FETCH FIRST ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release and isolation level.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.