SQLite Forum

Stale data returned on subsequent select query
Login

Stale data returned on subsequent select query

(1) By nandita biswas (nanditab) on 2022-01-24 15:44:02 [link] [source]

Hello,

i am relatively new to SQLite and java. I am using SQLite db with Hikari. Data updated is not reflected in the subsequent select statement.

` try (Connection conn = getDatabaseConnection(); PreparedStatement updateStatement = conn.prepareStatement(PromotionSQLiteQueries.UPDATEDATA)) {

        updateStatement.executeUpdate();

    } catch (SQLException sqlException) {
        throw catchSqlException(context, sqlException);
    }

'

Using JDBC io.micronaut.configuration:micronaut-jdbc-hikari:1.1.0

Adding sleep of 1 secs make the data available.

(2) By Jim Morris (jimmorris33215) on 2022-01-24 16:35:56 in reply to 1 [source]

Did yo ensure your transaction was committed?

(3) By nandita biswas (nanditab) on 2022-01-25 10:14:11 in reply to 2 [link] [source]

Yes. Tried with setting Autocommit and with explicit coo=nnection.commit() Still takes 1 sec to reflect the changes.

(4) By Stephan Beal (stephan) on 2022-01-25 10:19:37 in reply to 3 [link] [source]

Tried with setting Autocommit and with explicit coo=nnection.commit() Still takes 1 sec to reflect the changes.

That's a property/misfeature of your environment. This project is only responsible for the C-level sqlite3 library and its immediate partners (namely the sqlite3 CLI client and the dot-net sqlite3 wrapper), and the C library certainly does not exhibit the behavior you're describing. All Java wrappers for sqlite are maintained by third parties not represented in this forum. My recommendation is to get in touch with the Hikari folks.

(5) By Simon Slavin (slavin) on 2022-01-25 10:43:34 in reply to 1 [link] [source]

I agree with Stephan Beal's reply. This can't happen with bare SQLite or with a SQLite shim which just gives access to the SQLite API. It must be something to do with the language-based library you're using.

Can I ask where you found the 'sleep of 1 secs' idea ? Can you point us to some documentation that mentions it ? I'm curious because there is no such idea in SQLite. SQLite has no concept of waiting a fixed time for a change to become visible. Knowing where it is implemented may point us to the cause of your problem.

(6.1) By nandita biswas (nanditab) on 2022-01-25 12:36:30 edited from 6.0 in reply to 5 [link] [source]

'1 sec' is based on the my testing. Adding a Thread.Spleep(1000) seems to always work. Without it I get stale data.

My two methods her given below

---------------------Update----------------------------

try (Connection conn = getDatabaseConnection(); PreparedStatement updateStatement = conn.prepareStatement(PromotionSQLiteQueries.UPDATE_STATE)) { updateStatement.setString(1, newState.toString()); updateStatement.executeUpdate();

    } catch (SQLException sqlException) {
        throw catchSqlException(context, sqlException);
    }

-----------------------------Select ------------------

try (Connection conn = getDatabaseConnection(); PreparedStatement statement = conn.prepareStatement(FIND_LATEST_BY_ID)) { statement.setString(1, id); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) {

            SomeObject object = CallMethod();
            return object;
        }
        return Optional.empty();
    } catch (SQLException ex) {
        throw catchSqlException(context, ex);
    }

I can reproduce this on other laptops.

(7) By Stephan Beal (stephan) on 2022-01-25 11:47:40 in reply to 6.0 [link] [source]

I can reproduce this on other laptops.

But it cannot be reproduced in other programming environments, most notably the sqlite C library. The issue is in the technology stack you are using, somewhere between sqlite and your code. The authors of the JDBC driver are using are your best bet in terms of finding a solution.

(8) By nandita biswas (nanditab) on 2022-01-25 12:37:19 in reply to 7 [link] [source]

Thanks I will reach out to Hikari devs.