Hi,
the test program (sometimes) produces JdbcSQLExceptions due to
deadlocks. As I've read in other threads this seems to be not fixable?
Although I was wondering that I've never seen such a problem before in
other database systems?!
This is a VERY nasty problem because I cannot expect that all SQL
statements in my app must first be checked for possible deadlocks
which is a complex thing.
I had the same problem with MVCC but I cannot reproduce it at the
moment.
Any comments on that?
Uli
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.Random;
import org.h2.jdbcx.JdbcConnectionPool;
public class DeadlockTest
{
public static JdbcConnectionPool cp;
public static final Random r = new Random();
public static final int maxOid = 2000;
public static void createDb()
throws SQLException
{
Connection con = cp.getConnection();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.execute("DROP VIEW IF EXISTS v1;");
stmt.execute("DROP TABLE IF EXISTS t1;");
stmt.execute("DROP TABLE IF EXISTS t2;");
stmt.execute("DROP TABLE IF EXISTS tx;");
stmt.execute("CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY
KEY, oid INT NOT NULL, number INT);");
stmt.execute("CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY
KEY, oid INT NOT NULL, number INT);");
stmt.execute("CREATE TABLE tx (oid INT PRIMARY KEY, number2
INT);");
stmt.execute("CREATE VIEW v1 AS SELECT id, oid, number FROM t1
UNION SELECT id, oid, number FROM t2;");
// fill tx
PreparedStatement pstmt = con.prepareStatement("INSERT INTO tx
(oid, number2) VALUES(?,?)");
for (int oid = 0; oid < maxOid; oid++)
{
pstmt.setInt(1, oid);
pstmt.setInt(2, r.nextInt(999999));
pstmt.execute();
}
con.commit();
pstmt.close();
stmt.close();
con.close();
System.out.println("Db created");
}
public static void fillDb()
throws SQLException
{
System.out.println("Start filling DB");
Connection con = cp.getConnection();
con.setAutoCommit(false);
// fill database
PreparedStatement pstmtMove1 = con
.prepareStatement("INSERT INTO t2 (oid, number) SELECT
oid, number FROM t1 WHERE oid = ?");
PreparedStatement pstmtMove2 = con.prepareStatement("DELETE
FROM t1 WHERE oid = ?");
PreparedStatement pstmtNewInsert = con
.prepareStatement("INSERT INTO t1 (oid, number)
VALUES(?,?)");
for (int i = 0; i < 10000000; i++)
{
int nextOid = r.nextInt(maxOid);
pstmtMove1.setInt(1, nextOid);
pstmtMove2.setInt(1, nextOid);
pstmtNewInsert.setInt(1, nextOid);
pstmtNewInsert.setInt(2, r.nextInt(999999));
pstmtMove1.execute();
pstmtMove2.execute();
pstmtNewInsert.execute();
con.commit();
}
pstmtMove1.close();
pstmtMove2.close();
pstmtNewInsert.close();
con.close();
System.out.println("Db filled");
}
public static class Select
implements Runnable
{
public void run()
{
for (int i = 0; i < 200000; i++)
{
try
{
Connection con = cp.getConnection();
Statement stmt = con.createStatement();
int nOid = r.nextInt(maxOid);
ResultSet rs = stmt
.executeQuery("SELECT tx.number2, v1.number
FROM v1, tx WHERE v1.oid="
+ nOid + " AND v1.oid=tx.oid");
rs.last();
rs.getRow();
rs.close();
stmt.close();
con.close();
}
catch (SQLException e)
{
System.err.println(e);
}
}
}
}
public static void main(String[] args)
throws SQLException
{
cp = JdbcConnectionPool.create("jdbc:h2:~/
test;MULTI_THREADED=TRUE", "sa", "sa");
Connection con = cp.getConnection();
createDb();
new Thread()
{
@Override
public void run()
{
try
{
fillDb();
}
catch (SQLException e)
{
System.err.println(e);
}
}
}.start();
ArrayList<Thread> tl = new ArrayList<Thread>();
for (int t = 0; t < 1; t++)
{
Thread th = new Thread(new Select());
th.start();
tl.add(th);
}
System.out.println("All SELECT threads started");
for (Thread t : tl)
{
try
{
t.join();
}
catch (InterruptedException e)
{
}
System.out.println("Thread finished");
}
con.close();
cp.dispose();
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
