openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Patrick Linskey" <plins...@bea.com>
Subject RE: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
Date Thu, 05 Apr 2007 20:28:32 GMT
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) <jira@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.

Mime
View raw message