Hi,
I have a project that inserts documents records into a table.
Root documents does not have parent, other documents reference its parent
by a foreign key.
In attached test case the table DOCUMENT references itself by
PARENTDOCUMENTID field.
The test case inserts 150000 lines in DOCUMENT table and 150000 lines in
IMAGE table, so far so good.
Then connect that database with the console and try to execute the
following query :
UPDATE DOCUMENT SET STATE=4;
The query never ends in a reasonable time (I never tried to wait more that
few hours).
I think this is a bug in H2.
Regards,
Marc
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/m4eNnQIQYpgJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
package com.tessi.tests.h2;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
public class H2Tester {
/**
* @param args
* @throws SQLException
* @throws ClassNotFoundException
*/
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName(org.h2.Driver.class.getName());
Connection cnx = DriverManager.getConnection("jdbc:h2:~/testUpdate;MVCC=TRUE", "root", "r00t");
try {
Statement st = cnx.createStatement();
st.executeUpdate("DROP TABLE IMAGE");
st.executeUpdate("DROP TABLE DOCUMENT");
st.executeUpdate("CREATE TABLE IF NOT EXISTS DOCUMENT (" +
"id int primary key auto_increment," +
"cmc7 varchar(50)," +
"date1 datetime," +
"state int," +
"parentDocumentId int " +
")"
);
st.executeUpdate("CREATE TABLE IF NOT EXISTS IMAGE (" +
"id int primary key auto_increment," +
"name varchar(50)," +
"date1 datetime," +
"parentDocumentId int " +
")"
);
st.executeUpdate("ALTER TABLE IMAGE " +
"ADD FOREIGN KEY(parentDocumentId) " +
"REFERENCES DOCUMENT(ID)");
st.executeUpdate("ALTER TABLE DOCUMENT " +
"ADD FOREIGN KEY(parentDocumentId) " +
"REFERENCES DOCUMENT(ID)");
st.close();
PreparedStatement psDocument = cnx.prepareStatement("INSERT INTO DOCUMENT values (null, ?, ?, ?, ?)");
PreparedStatement psImage = cnx.prepareStatement("INSERT INTO IMAGE values (null, ?, ?, ?)");
Random rnd = new Random();
byte[] buffer = new byte[50];
cnx.setAutoCommit(false);
int rootId=-1;
for(int i=0; i<150000; ++i) {
int pos = 0;
rnd.nextBytes(buffer);
psDocument.setString(++pos, new String(buffer));
psDocument.setDate(++pos, new Date(System.currentTimeMillis()));
psDocument.setInt(++pos, /*(char)(rnd.nextInt(26) + 'A') + ""*/0);
if(i == 0) {
psDocument.setNull(++pos, java.sql.Types.INTEGER);
} else {
psDocument.setInt(++pos, rootId);
}
psDocument.executeUpdate();
ResultSet rs = psDocument.getGeneratedKeys();
Integer id = null;
while(rs.next()) {
id = rs.getInt(1);
}
pos = 0;
if(rootId == -1) rootId = id;
psImage.setString(++pos, new String(buffer));
psImage.setDate(++pos, new Date(System.currentTimeMillis()));
psImage.setInt(++pos, id);
psImage.executeUpdate();
if(i % 100 == 0) {
System.out.println(String.format("Inserted %d documents", i));
if(i % 10000 == 0) {
System.out.println("Commit");
cnx.commit();
}
}
}
psDocument.close();
psImage.close();
cnx.commit();
} finally {
cnx.rollback();
cnx.close();
}
}
}
