Two phase commit and transaction log issue

[wburzyns]

Hi Thomas,

It looks like the two-phase commit has an issue. Namely, committing
(and perhaps rolling back too, but I've not checked that) a prepared
transaction does not truncate the transaction log. In turns this leads
up to enormously growing DB files and a "pageStore: Transaction log
could not be truncated" warning in *.trace.db.
Temporarily as a workaround I explicitly do CHECKPOINT after
committing/rolling back a prepared transaction. This resolves the
issue but I believe that checkpointing should be automatic.


Regards,
wburzyns

[Thomas Mueller]

Hi,

Could you post a reproducible test case please? I tried, but couldn't reproduce the problem so far.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[wburzyns]

Please see below for a standalone testcase. However after playing with
it I no longer claim that the issue is related to the two-phase
commit...





import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Random;
import org.h2.jdbcx.JdbcDataSource;


public class H2_TwoPhaseCommit_issue
{
private static final String DATA_TABLE_NAME = "MATDB_DATA";

private static final String DATA_STAMP_SEQUENCE_NAME =
"DATA_STAMP";

private static final int DATA_STAMP_SEQUENCE_CACHE_SIZE = 1024;

private static final String PREPARED_TXN_NAME = "BULK_ENSURED";


public static void main(String[] args) throws Exception
{
// create/open the DB
System.setProperty("h2.lobInDatabase", "true");
String dbUrl = "jdbc:h2:split:" +
System.getProperty("java.io.tmpdir") + "txn_log_issue";

JdbcDataSource ds = new JdbcDataSource();
ds.setURL(dbUrl);
Connection dbConn = ds.getConnection();

// create data table and a relevant precompiled insertion
statement
Statement st = null;
st = dbConn.createStatement();
st.executeUpdate("CREATE SEQUENCE IF NOT EXISTS " +
DATA_STAMP_SEQUENCE_NAME + " CACHE " +
DATA_STAMP_SEQUENCE_CACHE_SIZE);
st.executeUpdate("CREATE TABLE IF NOT EXISTS " +
DATA_TABLE_NAME + "("
+ "GLOBAL_STAMP BIGINT NOT NULL DEFAULT NEXT VALUE FOR
" + DATA_STAMP_SEQUENCE_NAME + " PRIMARY KEY, "
+ "DATA BLOB, "
+ "DESC BLOB)");
PreparedStatement tableDataInsertSt =
dbConn.prepareStatement("INSERT INTO " + DATA_TABLE_NAME + " VALUES("
+ "DEFAULT, "
+ "?, "
+ "?)", Statement.RETURN_GENERATED_KEYS);

Random random = new Random(0xBAD); // ensure full
repeatability
for (int i = 0; i < 2048; ++i) {
// start a transaction
dbConn.setAutoCommit(false);
String dbSpName = "a savepoint";
Savepoint dbSp = dbConn.setSavepoint(dbSpName);

// generate some data
byte[] data = new byte[(int)(random.nextDouble() *
10240)];
byte[] desc = new byte[(int)(random.nextDouble() *
10240)];
random.nextBytes(data);
random.nextBytes(desc);

// save the data in the DB
tableDataInsertSt.setBytes(1, data);
tableDataInsertSt.setBytes(2, desc);
tableDataInsertSt.executeUpdate();

// commit the transaction (using two-phase commit)
st.executeUpdate("PREPARE COMMIT " + PREPARED_TXN_NAME);
dbConn.commit();
dbConn.setAutoCommit(true);
//st.executeUpdate("CHECKPOINT"); // if uncommented this
prevents the transaction log from growing
}

// done
dbConn.close();
}
}

[Thomas Mueller]

Hi,

I didn't run the test so far. But what what is the problem (what result do you expect, and what result do you get)?

Regards,

Thomas

[wburzyns]

The problem is that the database is growing much more than it should
(as if there was an uncommitted, long-running transaction - but there
isn't any). This is accompanied by "pageStore: Transaction log could
not be truncated" warning in *.trace.db. The problem disappears if I
manually force CHECKPOINT after every commit. As far as I know (I read
it some time ago in one of your posts:
https://groups.google.com/group/h2-database/browse_thread/thread/b26bbac61cc2a185)
manual checkpointing should not be neccessary.

I simplified the testcase. Please find it attached below:


package H2_Growing_txn_log_issue;


import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.Statement;
import java.util.Random;
import org.h2.jdbcx.JdbcDataSource;

public class H2_Growing_txn_log_issue
{

private static final String DATA_TABLE_NAME = "MATDB_DATA";

public static void main(String[] args) throws Exception
{
// create/open the DB
System.setProperty("h2.lobInDatabase", "true");
String dbUrl = "jdbc:h2:split:" +
System.getProperty("java.io.tmpdir") + "txn_log_issue";

JdbcDataSource ds = new JdbcDataSource();
ds.setURL(dbUrl);
Connection dbConn = ds.getConnection();

// create data table and a relevant precompiled insertion
statement
Statement st = null;
st = dbConn.createStatement();

st.executeUpdate("CREATE TABLE IF NOT EXISTS " +

DATA_TABLE_NAME + "(DATA BLOB)");
st.close();

PreparedStatement tableDataInsertSt =
dbConn.prepareStatement("INSERT INTO " + DATA_TABLE_NAME + "

VALUES(?)");

// do the test

Random random = new Random(0xBAD); // ensure full
repeatability

for (int i = 0; i < 1024; ++i) {

// start a transaction
dbConn.setAutoCommit(false);

// generate some data
byte[] data = new byte[(int)(random.nextDouble() *

102400)];
random.nextBytes(data);

// save the data in the DB
tableDataInsertSt.setBytes(1, data);

tableDataInsertSt.executeUpdate();
tableDataInsertSt.clearParameters();

// commit the transaction
dbConn.commit();

//st.executeUpdate("CHECKPOINT"); // if uncommented this
prevents the transaction log from growing
}

tableDataInsertSt.close();

// done
dbConn.close();
}
}

[Thomas Mueller]

Hi,

Thanks for the test case! I can reproduce this now. The warning should not be logged, but it seems it doesn't prevent empty space in the transaction log to be re-used. Anyway, it doesn't work as expected, I will investigate.

Regards,

Thomas