(A)symmetry of Update and Shared locks in Derby

[Dag H. Wanvik]

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