You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik" <Da...@Sun.COM> on 2005/11/02 18:16:42 UTC
(A)symmetry of Update and Shared locks in Derby
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:
Re: (A)symmetry of Update and Shared locks in Derby
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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
>>
>>
>>
>
>
>
Re: (A)symmetry of Update and Shared locks in Derby
Posted by Andreas Korneliussen <An...@Sun.COM>.
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
>
>
Re: (A)symmetry of Update and Shared locks in Derby
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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