db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Korneliussen <Andreas.Kornelius...@Sun.COM>
Subject Re: (A)symmetry of Update and Shared locks in Derby
Date Thu, 03 Nov 2005 16:20:21 GMT
I thought that asymmetric behaviour of updatelocks would reduce the 
probability of deadlocks.

Anyway, I also found another related issue w.r.t update locks:

According to the documentation, transactions using the 
TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation level 
should downgrade the update locks to shared locks when the transaction 
steps through to the next row: 
http://db.apache.org/derby/docs/10.1/devguide/cdevconcepts842385.html

This does not seem to happen, instead it seems that the update locks are 
not downgrade to shared locks when using rep. read:

I ran this test code to check this:

     /**
      * Test that update locks are downgraded to shared locks
      * after repositioning.
      */
     public void testUpdateLockDownGrade1()
         throws SQLException
     {
         Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = s.executeQuery("select * from t1 for update");

         // After navigating through the resultset, presumably all rows 
are locked with shared locks
         while (rs.next());

         // Now open up a new connection
         Connection con2 = getNewConnection();
         Statement s2 = 
con2.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_UPDATABLE);

         ResultSet rs2 = s2.executeQuery("select * from t1 for update");
         try {
            rs2.next(); // We should be able to get a update lock here.
         } finally {
             con2.rollback();
         }
     }

(Both Connections con and con2 have isolation level REP.READ and 
autocommit off).

The test fails in rs2.next() with:
ERROR 40XL1: A lock could not be obtained within the time requested


--Andreas


Mike Matrigali wrote:
> 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