LOCK a executeQuery with JDBC

From: Noël BRUNE <nbrune_at_clg.fr>
Date: Fri, 26 Jan 2001 18:46:15 +0100
Message-ID: <94sd3a$9co$1@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 Fri Jan 26 2001 - 11:46:15 CST