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
