openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ritika Maheshwari" <ritikams...@gmail.com>
Subject Re: [jira] Commented: (OPENJPA-182) db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
Date Thu, 05 Apr 2007 20:55:12 GMT
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 <plinskey@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) <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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message