Re: LOCK a executeQuery with JDBC

thanks a lot
it is completely the solution which I sought

"Spencer" <spencerp_at_swbell.net> a écrit dans le message news: WU6d6.1364$tW4.162606_at_nnrp2.sbc.net...
> i'm not exactly clear as to the scenario you are describing,
> or what problem you are experiencing, but it sounds to me
> as if your application is having a concurrency problem.
>
> a process obtains an exclusive lock on one or more rows in
> a table (for example. via the FOR UPDATE clause on a SELECT
> statement). the process will 'hold' the exclusive lock for
> the duration of the transaction, that is, until the session
> issues a commit or rollback.
>
> if a second process attempts to obtain an exclusive lock on
> a row that is already locked by another process, the second
> process will wait (by default) indefinitely for the other
> lock to be released. AFAIK, there is no parameter setting
> that will change this behavior. absent any locks obtained
> by the second process, this is NOT a deadlock... so deadlock
> detection does not come into play.
>
> (readers don't block writers and writers don't block readers,
> but writers CAN block other writers)
>
> AFAIK, there is no database "parameter" that changes this
> behavior.
>
> the only way (that i know of) to prevent the "wait" for
> an exclusive lock is to add the NOWAIT keyword after the
> FOR UPDATE clause on the SELECT. with NOWAIT specified,
> if an exclusive lock is not obtained immediately, Oracle
> raises an exception, which your application can handle.
>
> one of the design goals for your application should be
> to minimize the amount of time that transactions hold
> exclusive locks, and to control the amount of time that
> a transaction will wait to obtain an excluive lock.
>
> HTH
>
> "Noël BRUNE" <nbrune_at_clg.fr> wrote in message
> news:94sd3a$9co$1_at_reader1.imaginet.fr...
> > (sorry for my english)
> >
> > with oracle 8.0.5 and jdbc 7.3.4 or 8.0.5
> >
> > This is the Sun sample Employee.java with my own code
> > i try to lock a row on one side and to fetch the same row on the other
> > side to tun the lock timeout !!
> > I run employee.class twice and the class make a break (line #47)
 between
> > the 'select .... for update'(line #33)and the 'update ..' query (line
 #62)
> >
> > the first read is ok but the second read the timeout (line #33) is
> > neverending
> > I tried the oracleStatement with the setWaitOption(4) method
> > and the "distributed_lock_timeout = 5" in the init.ora file. but it
 didn't
> > works ,
> > the executeQuery() method never stop; it is waiting for the locked row.
> >
> > if anyone has an idea ???
> >
> > here is the class ------------------------------------------------------
> >
> > 1 import java.sql.*;
> > 2 import javax.swing.JOptionPane;
> > 3 import java.util.Properties;
> > 4 import oracle.jdbc.driver.OracleStatement;
> > 5 import oracle.jdbc.driver.OraclePreparedStatement;
> >
> > 6 class Employee
> > {
> > public static void main (String args [])
> > throws SQLException,Exception
> > 10 {
> > // Load the Oracle JDBC driver
> >
> > Class.forName("oracle.jdbc.driver.OracleDriver");
> > file://DriverManager.registerDriver(new
> > oracle.jdbc.driver.OracleDriver());
> >
> > System.out.println (" Driver manager initialisé");
> >
> > // Connect to the database
> > // You can put a database name after the @ sign in the connection
 URL.
> > 20 conn = DriverManager.getConnection
> > ("jdbc:oracle:thin:demo/demo_at_CLG0:1521:ORCL");
> >
> > System.out.println (" Connection OK ");
> >
> > // on met le autocommit a faux sinon il n'accepte pas la requete for
> > update
> > conn.setAutoCommit(false);
> >
> >
> > // Select the ENAME column from the EMP table
> > String req1="select alt_idx,modif_date,rowid ,empl_nbsal from
 alt_ej03
> > where alt_idx='0089406' for update ";
> > 30
> >
> > System.out.println (" Statement de lockage en cours ..... ");
> > 33 ResultSet rset = stmt.executeQuery (req1);
> >
> > System.out.println (" resultset cree en cours .... ");
> >
> > // Iterate through the result and print the employee names
> > while (rset.next ())
> > {
> > 40 String p_rox = rset.getString("rowid") ;
> > System.out.println (rset.getString ("alt_idx")+ " " +p_rox+" "+
> > rset.getString("empl_nbsal")+" "+rset.getString("modif_date")) ;
> >
> > // waiting ..
> > int rep9 = 0;
> > rep9 = JOptionPane.showConfirmDialog(null, "cliquez attente ", "attente
 ",
> > JOptionPane.YES_NO_OPTION,

 JOptionPane.WARNING_MESSAGE);
> >
> >
> >
> > 50 // ce bloc marche !!!
> > conn.setAutoCommit(true); // il excecutera immediatement le update
> > Statement stmt2 =conn.createStatement ();
> > System.out.println ("execution");
> >
> > // format par defaut :YYYY-MM-DD HH:MI:SS voir init.ora
> > // par defaut il attend une date DD-MMM(en lettre)-YYYY
> >
> > String stru="update alt_ej03 set modif_date='2001-03-20 15:30:51'
> > ,empl_nbsal=NULL where ROWID = '"+p_rox+"'";
> > 60 System.out.println (" upda str "+stru);
> >
> > 62 int idi = stmt2.executeUpdate(stru );
> >
> > System.out.println (" updater "+idi);
> > stmt2.close();
> > } // fin update
> >
> > stmt.close();
> >
> > 70 System.exit(1);
> > }
 

> > }
> >
> >
> >
> >
>
>
Received on Mon Jan 29 2001 - 07:54:03 CST