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 Thu, 03 Nov 2005 18:04:48 GMT
You are right, this behavior was changed at the same time - for the
same reasons.  In Serializable and RR U locks remain until end 
transaction, in read committed they are released before end
transaction.

I have filed JIRA-678 and JIRA-679  to address the problems you
have found.

I did a search on the documentation for update locks and scanned
the developer guid sections and did not see
anything else, but google does return a lot of hits.  If you see
any other references let me know.

Andreas Korneliussen wrote:
> 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