Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: LOCK a executeQuery with JDBC

Re: LOCK a executeQuery with JDBC

From: Spencer <spencerp_at_swbell.net>
Date: Sun, 28 Jan 2001 23:08:13 -0600
Message-ID: <WU6d6.1364$tW4.162606@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 Sun Jan 28 2001 - 23:08:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US