cancel
Showing results for 
Search instead for 
Did you mean: 

Ultralite for Android - Rollback issue.

Former Member
4,941

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

Accepted Solutions (0)

Answers (3)

Answers (3)

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
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
0 Kudos

Hi Chris,

Do you need the project?

chris_keating
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
0 Kudos

E-mail sent.

tks

philippefbertrand
Participant

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
Participant

Default is ReadUncommitted

philippefbertrand
Participant
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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