Generating transaction id for in-memory databases

At the time of this writing, TRANSACTION_ID() does not support in-memory databases. I can generate my own IDs using a sequence table but it's not clear how to communicate existing IDs to triggers. The first trigger should generate a new ID. Subsequent triggers (in the same transaction) should share the existing ID.

I could use thread-local variables to sh开发者_StackOverflow中文版are the existing ID but that seems fragile. Is there a better way to do this?

What about using sequences instead of transaction ids?

CREATE SEQUENCE SEQ;

The first operation in the transaction sets a session variable as follows:

SET @TID = SEQ.NEXTVAL;

The other operations within this transaction use the session variable:

CALL @TID;

I found a (very hacky) workaround:

/**
 * Invoked when a transaction completes.
 */
public abstract class TransactionListener extends Value
{
    private boolean invoked;

    /**
     * Invoked when the transaction completes.
     */
    protected abstract void onCompleted();

    @Override
    public String getSQL()
    {
        return null;
    }

    @Override
    public int getType()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public long getPrecision()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public int getDisplaySize()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public String getString()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public Object getObject()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public void set(PreparedStatement prep, int parameterIndex) throws SQLException
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    protected int compareSecure(Value v, CompareMode mode)
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public int hashCode()
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public boolean equals(Object other)
    {
        throw new AssertionError("Unexpected method invocation");
    }

    @Override
    public boolean isLinked()
    {
        return !invoked;
    }

    @Override
    public void close()
    {
        invoked = true;
        onCompleted();
    }
}

// -------------TRIGGER BELOW-----------

public void fire(final Connection connection, ResultSet oldRow, ResultSet newRow)
    throws SQLException
{
    Statement statement = connection.createStatement();
    long transactionId;
    ResultSet rs = statement.executeQuery("SELECT @TRANSACTION_ID");
    try
    {
        rs.next();
        transactionId = rs.getLong(1);
        if (transactionId == 0)
        {
            // Generate a new transaction id
            rs.close();
            JdbcConnection jdbcConnection = (JdbcConnection) connection;
            final Session session = (Session) jdbcConnection.getSession();
            session.unlinkAtCommit(new TransactionListener()
            {
                @Override
                protected void onCompleted()
                {
                    boolean oldAutoCommit = session.getAutoCommit();
                    session.setAutoCommit(false);
                    try
                    {
                        Statement statement = connection.createStatement();
                        statement.executeQuery("SELECT SET(@TRANSACTION_ID, NULL)");
                        statement.close();
                    }
                    catch (SQLException e)
                    {
                        throw new AssertionError(e);
                    }
                    finally
                    {
                        session.setAutoCommit(oldAutoCommit);
                    }
                }
            });
            rs = statement.executeQuery("SELECT SET(@TRANSACTION_ID, "
                + "audit_transaction_sequence.NEXTVAL)");
            rs.next();
            transactionId = rs.getLong(1);
        }
    }
    finally
    {
        rs.close();
    }
    assert (transactionId != 0);
    // ...
}

Here's how it works:

• We use Session.unlinkAtCommit() to listen for transaction commits (I assume this hooks rollbacks too but I haven't verified this yet)

• Since we cannot predict the number and order of trigger invocation we must do the following check in every single trigger:

  1. If @TRANSACTION_ID is null, register a new event listener and increment the sequence.
  2. If @TRANSACTION_ID is not null, grab the current transaction id from it.

The two major problems with this workaround is:

1. It is extremely fragile. If Session.unlinkAtCommit() changes in the future it will likely break the event listener.
2. We must repeat a lot of boilerplate code at the top of each trigger just to retrieve the transaction id.

It would be far easier to implement this as a built-in function TRANSACTION_LOCAL_ID(). This funciton would return a database instance-specific transaction id similar to HSQLDB.