Ultralite for Android - Rollback issue.

[Former Member]

I am facing some problems when executing a rollback operation in a unit test.

The unit test consists of the following steps:

1. Query for the last inserted id (primary key) in the table.
2. Creates a new connection (conn1) and executes a insert statement with the last id + 1.
3. Creates another connection (conn2) and executes a insert statement with the last id + 2.
4. Commits the first connection (conn1).
5. Rolls back the second connection (conn2).

The first execution works. But if you try to execute it again, this second execution throws a not unique primary key exception (UltraliteJ Error -193) when executing the second step, so the rollback operation (5th step) does not work propertly on the first execution.

If I executes a checkpoint operation at the end of the test all executions works fine. Is this really necessary?

Is there a way (UltraliteJ api) to always checkpoint after commit and rollback? I try to use setAutocheckpoint method in the ConfigFileAndroid object, but it does not work.

Do I need to set "commit-flush" property to "immediate"? How can I do this?

Thank you, Ericsen

[VolkerBarth]

Not as an answer, but have you considered to use a DEFAULT (GLOBAL) AUTOINCREMENT for the id column? This omits the manual querying of the maximum used value and solves concurreny issues...

[Former Member]

My application and all sync logic works with a pool of primary keys. Changing to the GLOBAL AUTOINCREMENT approach is not a viable option to me.

[philippefbertrand]

If you are using a pool, shouldn't you be doing something like:

Conn1 mutex{ SELECT FIRST id FROM Pool (into x), DELETE FROM Poll WHERE id = x }, INSERT INTO Table( id, ... ) VALUES( x, ... )

Conn2 mutex{ SELECT FIRST id FROM Pool (into y), DELETE FROM Poll WHERE id = y }, INSERT INTO Table( id, ... ) VALUES( y, ... )

Conn1 COMMIT

Conn2 ROLLBACK

Make sure Conn2 is running with ReadCommitted isolation level.

[Former Member]

Concurrent transactions (connections) will be common in my application and I run a lot of tests to validate this feature. This scenario is just a simplified way to reproduce a bug that I found during these tests. I need to be sure about Ultralite consistency and integrity with concurrent transactions.

[former_SQLA_member1694876]

Hi Ericsen,

commit-flush is immediate by default. That should not be the problem.

Can you post what connection strings you are using for all the connections?

[Former Member]

All connections use: ";DBF=/mnt/sdcard/Android/data/myapp/files/mSeries.udb"

[former_SQLA_member1694876]

But you also need a CON parameter to distinguish the connections, right? Anyway, it looks like the best thing at this point would be for you to post all your code which reproduces the problem, and we'll investigate from there.

[Former Member]

Tim, I don´t know the CON parameter. How can I set in an Android application? Can you send me a link with this parameter documentation?

To connect to the UL database I execute the following code:

ConfigFileAndroid config = DatabaseManager.createConfigurationFileAndroid("data.udb", this);

conn = DatabaseManager.connect(config);

Is there a better way?

(i am preparing the code to post here)

[Former Member]

According to my new tests, if I execute concurrentTransactions method many times it will work. But if I close the application and run again it fails. It is the same scenario of the original post when I execute this steps through an Android Unit Test framework. (again, it is a simplified way to reproduce the bug)

private int getLastId(Connection conn) {

int baseId = 0;
try {
        PreparedStatement ps = conn
                .prepareStatement("SELECT MAX(idState) as maxIdState FROM bzState");
        ResultSet rs = ps.executeQuery();
        rs.next();
        String lastId = rs.getString("maxIdState");
            if (lastId != null)
            baseId = Integer.parseInt(lastId);
        rs.close();
        ps.close();
    } catch (Exception ex) {
    }
    return baseId;
}

private Connection createConnection() throws ULjException {
    ConfigFileAndroid config = DatabaseManager
            .createConfigurationFileAndroid(
                    "mSeries_model.udb", this);
    return DatabaseManager.connect(config);
}

private void concurrentTransactions() throws Exception {
    int lastId = 0;
    Connection conn1;
    Connection conn2;
    Connection conn3;
    // conn1 used to check the current last id.
    conn1 = createConnection();
    lastId = getLastId(conn1);
    // conn2 used to create and commit a new row.
    conn2 = createConnection();
    // conn3 used to create and roll back a new row.
    conn3 = createConnection();
    // Insert a new row in the table: conn1 / lastId + 1
    conn2.prepareStatement(
            "INSERT INTO bzState VALUES (" + (lastId + 1)
                    + ", 'ZZ', 'ZZZZ')").execute();
    // Insert a new row in the table: conn2 / lastId + 2
    conn3.prepareStatement(
            "INSERT INTO bzState VALUES (" + (lastId + 2)
                    + ", 'ZZ', 'ZZZZ')").execute();
    // Check the last id.
    int tempLastId = getLastId(conn1);
    if (tempLastId != lastId) {
        throw new Exception("No committed transactions.");
    }
    conn2.commit();
    conn2.release();
    // Check the last id.
    tempLastId = getLastId(conn1);
    if (tempLastId != (lastId + 1)) {
        throw new Exception("First transaction committed.");
    }
     conn3.rollback();
    conn3.release();
    // Check the last id.
    tempLastId = getLastId(conn1);
    if (tempLastId != (lastId + 1)) {
        throw new Exception("Second transaction rolled back.");
    }
}

public void onClick(View v) {
    try {
        concurrentTransactions(); // OK.
        concurrentTransactions(); // OK.
        // If we close the application and run again it fails.
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

[Former Member]

Tim, do you have some news? Can I help you with more information?

[former_SQLA_member1694876]

We are looking at your repro code and will post back.

[former_SQLA_member1694876]

(My mistake about the CON parameter: you don't need it in this case.)

[chris_keating]

Can you confirm the version and build you are using. Testing with current software based on the sample code did not show the behaviour you described.

[Former Member]

Chris, according to JAR Manifest file: version 12.0.1 build 3505

[chris_keating]

I have tested with current software and 12.0.1 Build 3505 on an emulator and a Galaxy Tab and do not see the problem reported. I assume I should see the primary key error first reported and/or one of the thrown exceptions in the attached code. I do not see either of those cases even after running the application n number of times where n>2.

At this stage, you may want to work with technical support if you are still running into problems.

[Former Member]

I had exactly the same problem testing on a Nexus S Android 4.0.3 ICS, and I could reproduce the steps mentioned by Ericsen. Are you still trying to simulate the problem?

[chris_keating]

I was unable to reproduce this issue on an 4.03 based emulator. I do not have a Nexus S physical device for testing this. Can you comment on whether you see this behaviour in an simulator? If so, what specifically was used? If you are able to work with support, they can help to gather information needed to reproduced. If not, are you able to provide your repro via the free "report a bug" option on http:\\case-express.sybase.com?

[Former Member]

I was able to reproduce this problem. Thanks Renato.

There is currently a bug with DatabaseManager.release() - it finalizes an object in libultralitej12.so, making the shared library unusable, even after the application is closed and restarted. That is because the process that loaded the shared libray is still active even after the application is closed. I will fix that bug. In the meantime, I recommend not using DatabaseManager.release(), but release()-ing all database connections in the application's onDestroy(). If you see a problem when using this approach, please let us know.

[Former Member]

FYI: The problem with DatabaseManager.release() is fixed in 12.0.1.3713 or later. This method will be useful for releasing active connections in this build.

Question for Ericsen: Were you using DatabaseManager.release() in your application when you originally posted this issue? If not, we would need your complete application in order to see how the application closes and starts again.

Note: the ~db file is a temporary file, and it is expected that it will remain if the application closes without releasing its connections. The temp file remaining is not harmful, but we do recommend that applications release active connections in the onDestroy() method of the activity.

[Former Member]

Hi,

I created an Android project with Ericsen's test and when I launch it for the second time the exception occurs. But analyzing the code I checked that the Connection "conn1" wasn't released. After I inserted the conn1.release() the exception did not occur.

But I thought, if the developer forgot to close all connections? My alternative is to put the "DatabaseManager.release();" on the "onDestroy()" method of an activity. I have tested this solution and notice that the file with <db name="">.~db continuous to be on my file folder. If I released all the connections shouldn't my <db name="">.~db file be deleted? With this file the exception still occurs.

If one connection keeps holding the db resources, when I load my application again, the error will occur.

I could not attach my Android Project, don't have enough reputation.

tks

Renato

[chris_keating]

Thank you for this new information. I am reworking my test application with this new information in mind to see if I can reproduce. If I am still not able to see the problem, can you email me your project to <last_name>@sybase.com in lieu of posting. Alternatively, you can "Create a Bug Report Case" on http:\\case-express.sybase.com posting the case number here for reference. There is no cost for using this service.

[Former Member]

Hi Chris,

Do you need the project?

[chris_keating]

It would be helpful. We have not had much luck with repro'ing this issue and hope that a full project might identify what we are missing.

Thanks

If you can zip it up and send to <last_name>@sybase.com where last_name is my last name, I will investigate.

[Former Member]

E-mail sent.

tks

[philippefbertrand]

Just to clarify, let's assume the last id is 1.

After the first run, I would expect there to be a row with id = 2.

1. found id=1
2. Created Conn1, INSERT id=2
3. Create Conn2, INSERT id=3
4. Conn1 COMMIT -> now there is a row with id=2
5. Conn2 ROLLBACK

Then on the second run I would expect the following:

1. found id=2 (did you check this to make sure the id variable is not still 1?)
2. Conn1' created (did you close Conn1 from the first run or are you reusing it?), INSERT id=3 - this should work.
3. Conn2' created, INSERT id=4
4. Conn1' COMMIT -> now there is a row with id=3
5. Conn2' ROLLBACK

Are you creating Conn1 and Conn2 on separate threads?

Between runs, did you do a SELECT id FROM table to verify there is in fact no IDs?

Can you post the API you used for step 5?

[Former Member]

I am not creating conn1 and conn2 on separate threads.

I check results with SELECT statements after each INSERT statement. I execute a "SELECT MAX(id) FROM table" to get the last id. On the second run the last id is 2 (correctly). But when I try to insert id=3 I got the exception.

I create my connections right before the insert statements. And I close them right after the commit and rollback statements.

To execute the rollback on step 5 I execute: ulConnection.rollback(); ulConnection.release();

[Former Member]

I am not sure whether the isolation level for UltraLite on Android is ReadCommitted (1) or ReadUncommitted (0). If it is ReadCommitted then a query on connection 1 of "SELECT MAX(id) FROM table" would not see the row inserted (but not committed) on connection 2. Attempting to insert a row with that Max(id) + 1 would then fail.

[philippefbertrand]

Default is ReadUncommitted

[philippefbertrand]

Quick test. Try executing the SQL statement "ROLLBACK" instead of the the rollback method on the connection.

[Former Member]

Exactly the same result: first run OK (checking results) and second run fail (throws an exception) on the second step.

[former_SQLA_member1694876]

(Actually, the default is read_committed (1). "select db_property('isolation_level')" to verify.)