Hello,
I discovered something I think is a bug and then I wrote a test to
verify this. However, now I'm slightly confused :)
Can anyone help me check if the test is correct, and maybe more
important, what's the expected behavior?
The tests are basically doing the following, using variations over
isolation levels, autocommit state (when selecting) and result set
getter methods:
- insert an integer and CLOB value (using a stream, longer than one page)
- select the row, access the CLOB somehow
- open a second connection, try to delete the CLOB row
With the current version of the test, 4 out of 24 test cases fail. The
causes are not identical, and there are differences between the embedded
and the client driver. With an earlier revision of the test I verified
[at least some of] the failures back to 10.2 (didn't try 10.1). I think
I understand some of the failures, but I'd like to clarify the expected
behavior before I do anything more.
There are some TODOs in the test. A few questions:
a) Should a LOB be protected by an "extra" lock even in
READ_UNCOMMITTED to keep it valid until the end of the transaction?
b) When doing rs.getCharacterStream(2) on a CLOB column, do we still
want to set the "extra" lock?
FYI; the test goes into
java/testing/org/apache/derbyTesting/functionTests/tests/store/
Thanks,
--
Kristian
/*
Derby - Class org.apache.derbyTesting.functionTests.tests.store.LOBLocksTest
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
*/
package org.apache.derbyTesting.functionTests.tests.store;
import java.io.EOFException;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import junit.framework.Test;
import junit.framework.TestSuite;
import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
/**
* Tests basic locking behavior on rows with LOBs.
*/
public class LOBLocksTest
extends BaseJDBCTestCase {
private static final Object KEY_SYNC = new Object();
/** Unique key for rows, only valid within a single run. */
//@GuardedBy("KEY_SYNC");
private static int RECORD_KEY = 1;
/** List of secondary connections, used for cleanup. */
private static List CONNECTIONS = new ArrayList();
public LOBLocksTest(String name) {
super(name);
}
public void setUp()
throws Exception {
super.setUp();
setAutoCommit(true);
}
public void tearDown()
throws Exception {
Iterator conIter = CONNECTIONS.iterator();
while (conIter.hasNext()) {
Connection con = (Connection)conIter.next();
if (!con.isClosed()) {
con.rollback();
con.close();
}
conIter.remove();
}
// Continue cleanup.
super.tearDown();
}
/**
* Returns a suite running the test with both the embedded and the client
* driver, and with a decorator lowering the lock timeout(s) and one for
* creating the required tables.
*
* @return A test suite.
*/
public static Test suite() {
Properties lockProps = new Properties();
lockProps.setProperty("derby.locks.waitTimeout", "3");
lockProps.setProperty("derby.locks.deadlockTimeout", "2");
TestSuite embeddedSuite =
new TestSuite(LOBLocksTest.class, "LOB Locks Embedded");
TestSuite clientSuite =
new TestSuite(LOBLocksTest.class, "LOB Locks Client/Server");
TestSuite suite = new TestSuite("LOB Locks Suite");
suite.addTest(new DatabasePropertyTestSetup(embeddedSuite, lockProps));
suite.addTest(TestConfiguration.clientServerDecorator(
new DatabasePropertyTestSetup(clientSuite, lockProps)));
return new CleanDatabaseTestSetup(suite) {
protected void decorateSQL(Statement s)
throws SQLException {
// Create the tabels we need.
s.executeUpdate("create table CLOB_LOCK_TEST_NO_IDX (" +
"id int," +
"dClob clob" +
")");
}
};
}
public void testRsGetIntBase_Serializable()
throws IOException, SQLException {
doRsGetInt(false, Connection.TRANSACTION_SERIALIZABLE);
}
public void testRsGetIntBase_RepeatableRead()
throws IOException, SQLException {
doRsGetInt(false, Connection.TRANSACTION_REPEATABLE_READ);
}
public void testRsGetIntBase_ReadCommitted()
throws IOException, SQLException {
doRsGetInt(false, Connection.TRANSACTION_READ_COMMITTED);
}
public void testRsGetIntBase_ReadUncommitted()
throws IOException, SQLException {
doRsGetInt(false, Connection.TRANSACTION_READ_UNCOMMITTED);
}
public void testClobRsGetClobNoIDX_Serializable()
throws IOException, SQLException {
doRsGetClob(false, Connection.TRANSACTION_SERIALIZABLE, true);
doRsGetClob(false, Connection.TRANSACTION_SERIALIZABLE, false);
}
public void testClobRsGetClobNoIDX_RepeatableRead()
throws IOException, SQLException {
doRsGetClob(false, Connection.TRANSACTION_REPEATABLE_READ, true);
doRsGetClob(false, Connection.TRANSACTION_REPEATABLE_READ, false);
}
public void testClobRsGetClobNoIDX_ReadCommitted()
throws IOException, SQLException {
doRsGetClob(false, Connection.TRANSACTION_READ_COMMITTED, true);
doRsGetClob(false, Connection.TRANSACTION_READ_COMMITTED, false);
}
public void testClobRsGetClobNoIDX_ReadUncommitted()
throws IOException, SQLException {
doRsGetClob(false, Connection.TRANSACTION_READ_UNCOMMITTED, true);
doRsGetClob(false, Connection.TRANSACTION_READ_UNCOMMITTED, false);
}
public void testClobRsGetCharacterStreamNoIDX_Serializable()
throws IOException, SQLException {
doRsGetCharStream(false, Connection.TRANSACTION_SERIALIZABLE);
}
public void testClobRsGetCharacterStreamNoIDX_RepeatableRead()
throws IOException, SQLException {
doRsGetCharStream(false, Connection.TRANSACTION_REPEATABLE_READ);
}
public void testClobRsGetCharacterStreamNoIDX_ReadCommitted()
throws IOException, SQLException {
doRsGetCharStream(false, Connection.TRANSACTION_READ_COMMITTED);
}
public void testClobRsGetCharacterStreamNoIDX_ReadUncommitted()
throws IOException, SQLException {
doRsGetCharStream(false, Connection.TRANSACTION_READ_UNCOMMITTED);
}
private void doRsGetCharStream(boolean withIndex, int isolation)
throws IOException, SQLException {
final int length = 77*1024+1;
int key = insertClob(length, true, withIndex);
// Select the CLOB and obtain a char stream, but don't move off the row
// nor close the stream.
ResultSet rs = getClob(key, withIndex, isolation);
assertTrue("No rows selected", rs.next());
Reader reader = rs.getCharacterStream(2);
// Obtain a second connection and try to delete the CLOB.
boolean deleted = deleteRow(key, withIndex);
if (isolation == Connection.TRANSACTION_READ_UNCOMMITTED) {
assertTrue("Unable to delete row", deleted);
// Except this to fail, we deleted the underlying value.
try {
drain(reader);
fail("Draining stream should have failed");
} catch (EOFException eofe) {
// No state for us to check.
}
} else {
assertFalse("Deleted row", deleted);
assertEquals(length, drain(reader));
}
assertFalse("More than one row selected", rs.next());
}
private void doRsGetClob(boolean withIndex, int isolation, boolean autoC)
throws IOException, SQLException {
setAutoCommit(autoC);
final int length = 61*1024+7;
int key = insertClob(length, true, withIndex);
// Select the CLOB, but don't move off the row
ResultSet rs = getClob(key, withIndex, isolation);
assertTrue("No rows selected", rs.next());
Clob clob = rs.getClob(2);
// Obtain a second connection and try to delete the CLOB.
boolean deleted = deleteRow(key, withIndex);
// TODO: Is the outcome dependent on whether the CLOB got
// materialized or not?
if (isolation == Connection.TRANSACTION_READ_UNCOMMITTED) {
// TODO: What is supposed to happen here?
assertTrue("Unable to delete row", deleted);
} else {
assertFalse("Deleted row", deleted);
assertEquals(length, clob.length());
}
assertFalse("More than one row selected", rs.next());
// We moved off the row. With autocommit on the CLOB should now be
// invalid, with autocommit off it should still be accessible.
if (autoC) {
try {
clob.length();
fail("Clob should have been invalidated");
} catch (SQLException sqle) {
assertSQLState("XJ215", sqle);
}
assertTrue(deleteRow(key, withIndex));
} else {
assertEquals(length, (int)clob.length());
}
rollback();
}
private void doRsGetInt(boolean withIndex, int isolation)
throws IOException, SQLException {
final int length = 61*1024+7;
int key = insertClob(length, true, withIndex);
ResultSet rs = getIntOnly(key, withIndex, isolation);
assertTrue("No rows selected", rs.next());
rs.getInt(1);
// Obtain a second connection and try to delete the row.
boolean deleted = deleteRow(key, withIndex);
if (isolation == Connection.TRANSACTION_READ_COMMITTED ||
isolation == Connection.TRANSACTION_READ_UNCOMMITTED) {
assertTrue("Unable to delete row", deleted);
} else {
assertFalse("Deleted row", deleted);
}
assertFalse("More than one row selected", rs.next());
}
private int insertClob(int length, boolean useStream, boolean withIndex)
throws IOException, SQLException {
int key = getRecordKey();
String tbl = withIndex ? "CLOB_LOCK_TEST_IDX" : "CLOB_LOCK_TEST_NO_IDX";
PreparedStatement psIns = prepareStatement(
"insert into " + tbl + " values (?, ?)");
psIns.setInt(1, key);
if (useStream) {
psIns.setCharacterStream(
2, new LoopingAlphabetReader(length), length);
} else {
throw new UnsupportedOperationException("not yet implemented");
}
assertEquals(1, psIns.executeUpdate());
psIns.close();
return key;
}
/**
* Returns the CLOB value with the specified key.
*
* @param key the unique key for the CLOB
* @param withIndex determines in which table to do the lookup
* @param iso the transaction isolation level to use
* @return The {...@code ResulSet} from the query.
* @throws SQLException if the query fails
*/
private ResultSet getClob(int key, boolean withIndex, int iso)
throws SQLException {
String tbl = withIndex ? "CLOB_LOCK_TEST_IDX" : "CLOB_LOCK_TEST_NO_IDX";
getConnection().setTransactionIsolation(iso);
PreparedStatement psSel = prepareStatement("select id, dClob " +
"from " + tbl + " where id = ?");
psSel.setInt(1, key);
return psSel.executeQuery();
}
private ResultSet getIntOnly(int key, boolean withIndex, int iso)
throws SQLException {
String tbl = withIndex ? "CLOB_LOCK_TEST_IDX" : "CLOB_LOCK_TEST_NO_IDX";
getConnection().setTransactionIsolation(iso);
PreparedStatement psSel = prepareStatement("select id " +
"from " + tbl + " where id = ?");
psSel.setInt(1, key);
return psSel.executeQuery();
}
/**
* Returns a unique record key that can be used for record access.
*
* @return A unique integer (within this test).
*/
private static int getRecordKey() {
synchronized (KEY_SYNC) {
return RECORD_KEY++;
}
}
private boolean deleteRow(int key, boolean withIndex)
throws SQLException {
Connection con = getSecondaryConnection();
String tbl = withIndex ? "CLOB_LOCK_TEST_IDX" : "CLOB_LOCK_TEST_NO_IDX";
PreparedStatement ps = con.prepareStatement(
"delete from " + tbl + " where id = ?");
ps.setInt(1, key);
boolean deleted = false;
int attempts = 0;
// For now try only once.
// TODO: Is it worth the extra time to do more than one attempt?
while (!deleted && attempts <= 1) {
try {
int affected = ps.executeUpdate();
assertEquals(1, affected);
deleted = true;
} catch (SQLException sqle) {
assertSQLState("40XL1", sqle);
}
attempts++;
}
return deleted;
}
/**
* Returns a secondary connection to the default database.
* <p>
* Connections obtained with this method will be closed during tear-down.
*
* @return A connection.
* @throws SQLException if opening the connection fails
*/
private Connection getSecondaryConnection()
throws SQLException {
Connection con = openDefaultConnection();
CONNECTIONS.add(con);
return con;
}
/**
* Drains the stream.
*
* @param r the reader to drain
* @return The number of characters found in the reader.
* @throws IOException if reading from the reader fails
*/
private static int drain(Reader r)
throws IOException {
int charsRead = 0;
char[] buf = new char[4*1024];
while (true) {
int read = r.read(buf);
if (read == -1) {
return charsRead;
} else {
charsRead += read;
}
}
}
}
