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
>>
>>
>>
>
>
>
|