db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: (A)symmetry of Update and Shared locks in Derby
Date Wed, 02 Nov 2005 21:45:41 GMT
The current documentation of the expected behavior in derby is wrong in 
this case, it was not changed when the associated code change was made.
Let me know if you want to file the JIRA, or I will.

The current lock table is symmetric.
This behavior was changed as a result of customer input at the time
(pre-derby) and testing with the running the specj test
(http://www.spec.org/osg/jAppServer2001/).

Without this change Derby was seeing deadlocks, where other
databases were not.  Unfortunately I don't remember more details.


Dag H. Wanvik wrote:
> Hi,
> 
> I did some tests of Derby with Update and shared locks, to check the
> compatibility. To avoid deadlocks, these locks should be implemented
> asymmetrically, as shown in
> http://db.apache.org/derby/docs/10.1/devguide/rdevconcepts2462.html
> 
> As I read this matrix, once a transaction has an update lock
> (intention to update), a shared lock should not be granted to another
> transaction. My test, whoever, indicated that a shared lock was indeed
> granted (connection2 in the repro) after an update lock was taken by
> the first transaction. Is this a bug or am I missing something here?
> 
> Repro:
> 
> 
> ------------------------------------------------------------------------
> 
> /*
>  * Main.java
>  *
>  * Created on October 28, 2005, 2:28 PM
>  *
>  * To change this template, choose Tools | Options and locate the template under
>  * the Source Creation and Management node. Right-click the template and choose
>  *
>  * Derby seems to allow *both* R + U, and U + R, which can lead to more deadlocks
>  * cf. Gray, Reuter p 408, there should be asymmetry for these locks.
>  */
> 
> package forupdatelockingtest;
> 
> 
> import java.sql.*;
> 
> public class Main {
>     
>     /**
>      * @param args the command line arguments
>      */
>     public static void main(String[] args) {
> 
>         Statement updateStatement = null;
>         Statement selectStatement = null;
>         Statement selectStatement2 = null;
>         Statement ddlStatement = null;
>         Connection con = null;
>         Connection con2 = null;
>         ResultSet rs = null;
>         ResultSet rs2 = null;
>         
>         try {
> 	   Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
> 	   con = DriverManager.getConnection("jdbc:derby:testdb;create=true;territory=en_US");
>             
> 	   con.setAutoCommit(false);
>            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
> 	   // Create table
> 	   ddlStatement = con.createStatement();
> 	   ddlStatement.execute("CREATE TABLE myTable (id int primary key, name varchar(50))");
> 	}
> 	catch (Exception e) {
> 	   System.out.println(e);
>            return;
> 	}
> 
> 	try {
> 	   // Insert data
> 	   //
> 	   PreparedStatement ps = con.prepareStatement("INSERT INTO myTable VALUES (?, ?)");
> 	   for (int i=1; i<=10; i++) {
> 	      ps.setInt(1, i);
> 	      ps.setString(2, "Testing " + i);
> 	      ps.executeUpdate();
> 	   }
> 	   ps.close();
>            con.commit();
> 	   // Get ResultSet
> 	   //
> 	   selectStatement = con.createStatement (ResultSet.TYPE_FORWARD_ONLY,
> 						  ResultSet.CONCUR_UPDATABLE);
> 	   rs = selectStatement.executeQuery("select * from myTable for update");
> 	   // Position on first row
>            int i = 5;
> 	   while (i >= 0 ) {
>                rs.next();
>                System.out.println(rs.getInt(1));
>                i--;
>            };
>            System.out.println("trying to do getconnection2");
>            con2 = DriverManager.getConnection("jdbc:derby:testdb");
>            con2.setAutoCommit(false);
>            con2.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
>            System.out.println("trying to do createstatement2");
>            selectStatement2 = con2.createStatement (ResultSet.TYPE_FORWARD_ONLY,
>                                                    ResultSet.CONCUR_READ_ONLY); 
>            System.out.println("trying to do executeQuery2");
>            rs2 = selectStatement2.executeQuery("select * from myTable for update");
>            
>            System.out.println("trying to do next2");
>            
>            while (rs2.next() ) {
>                System.out.println(rs2.getInt(1));
>            };
> 
>         } catch (SQLException e) {
> 	    String s = e.getSQLState();
>             System.out.println(e.toString() + " state: " + s + " : code=" + e.getErrorCode());
>         } catch (Exception e) {
> 	   System.out.println(e.toString());
> 	} 
>         finally {
> 	   try {
> 	      if (selectStatement != null) {
> 		 System.out.println("trying to do close stmt1");
>                  selectStatement.close(); // closes rs, too
> 	      }
>               if (selectStatement2 != null) {
> 		 System.out.println("trying to do close stmt2");
>                  selectStatement2.close(); // closes rs, too
> 	      }
> 	      // Drop table and close
> 	      
> 
> 	      if (updateStatement != null) {
> 		 updateStatement.close();
> 	      }
>               con.commit();
>               con2.commit();
>               if (ddlStatement != null) {
>                  System.out.println("trying to do drop table"); 
> 		 ddlStatement.execute("DROP TABLE myTable");
>                  System.out.println("trying to do close ddlstmt");
> 		 ddlStatement.close();
> 	      }
>               
>               System.out.println("trying to close con1");
>               con.commit();
>               con.close();
>               
>               con2.commit();
>               con2.close();
>               
> 	   } catch (Exception e) {
> 	      System.out.println(e.toString());
> 	   }
>         } 
>     }
> }
> 
> 
> ------------------------------------------------------------------------
> 
> 
> 
> Thanks,
> Dag


Mime
View raw message