[Improvement] Improve time precision of deleteAt to reduce possible bugs
#3926
Comments
|
In H2, the UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) function does not seem to be able to retain data below seconds, and the results are all 0 after seconds, so conflicts may occur. |
yuqi1129
changed the title
deleteAt to reduce possible deleteAt to reduce possible bugs
I recall that we discussed this several months ago. Do you have any suggestions on how to solve it? |
The current method works well in our production environment, but I think the following research can be done:
|
|
From my observation, most RDBMSs cannot support time precision less than one millisecond. Using the built-in function to get the current time is far from ideal in terms of precision.
We don't need to limit our system to H2. We have left the interface to support other embedded databases like derby, sqlite, though it's not so necessary now or in the near future.
Yeah, I'm focusing on this point and will provide a TSO that is strongly related to current time but provides higher precision time. In this way, it can be database independent. I'm not entirely sure if the complexity is acceptable. |
My initial consideration was to calculate the time externally and save it to the database, but this means that the time may be client-dependent, which may lead to inaccurate time if the clocks of multiple clients are not synchronized. |
I see, there could be a time-skew problem if we use the time generated by the Gravitino server. let me think if it can be addressed. |
|
Resolved by #3954 |
|
The problem still exists, please see: https://github.com/apache/gravitino/actions/runs/10570943483/job/29286239153?pr=4694 |
|
Test code for (int i = 0; i < 10; i++) {
try {
ResultSet rs = statement.executeQuery("SELECT (UNIX_TIMESTAMP() * 1000.0), EXTRACT(MICROSECOND FROM CURRENT_TIMESTAMP(3)) / 1000");
rs.next();
String v1 = rs.getString(1);
String v2 = rs.getString(2);
LOG.info("Current timestamp-yq: {}, {}", v1, v2);
statement.execute("update schema_meta set deleted_at = 1000 where schema_id = 1");
} catch (Exception e) {
LOG.error("Failed to connect to H2 database. Retrying...", e);
Thread.sleep(1000);
}
} |
What would you like to be improved?
When we switch default entity store to embedded JDBC database and try to drop a entity, the following errors occur:
As the query response time(less that 1ms) is very fast compared to MySQL, the value of new
deleteAtis the same as old one, then error occur.How should we improve?
I believe we need to improve the precision or use other ways to solve it, or we can't use embedded JDBC database well.
The text was updated successfully, but these errors were encountered: