[
https://issues.apache.org/jira/browse/HIVE-26882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17823671#comment-17823671
]
Rui Li edited comment on HIVE-26882 at 3/5/24 3:50 PM:
-------------------------------------------------------
I tested again with MariaDB and there're fewer commit conflicts in HMS log than
in the test log. This is expected because iceberg checks for conflict itself
before it calls {{{}alter_table{}}}. The number of conflicts triggered by HMS
is the same as the number in HMS log.
I also tested with Postgres and the result is correct. I read the
[doc|https://www.postgresql.org/docs/14/transaction-iso.html#XACT-REPEATABLE-READ]
and I think it works because:
{quote}a repeatable read transaction cannot modify or lock rows changed by
other transactions after the repeatable read transaction began
{quote}
But I suspect this is stricter than the ANSI SQL standard. I checked SQL:2011,
and it says the following about {{SERIALIZABLE}} level:
{quote}The execution of concurrent SQL-transactions at transaction isolation
level SERIALIZABLE is guaranteed to be serializable. A serializable execution
is defined to be an execution of the operations of concurrently executing
SQL-transactions that produces the same effect as some serial execution of
those same SQL-transactions. A serial execution is one in which each
SQL-transaction executes to completion before the next SQL-transaction begins.
{quote}
Suppose we have these two concurrent transactions trying to update the
property. IIUC both transactions can commit and the result can be either {{v1}}
or {{{}v2{}}}, even for {{SERIALIZABLE}} level.
{code:sql}
txn1> update tbl set val = 'v1' where key = 'k';
txn2> update tbl set val = 'v2' where key = 'k';
{code}
Maybe another solution is to use direct SQL and checks for the number of
affected rows to detect conflict. We did a PoC for this and it provides correct
results with MariaDB. The pseudo code is like this:
{code:java}
String key = ...;
String expectedVal = ...;
Table oldTable = ...;
Table newTable = ...;
Connection connection = getConnection(Connection.TRANSACTION_REPEATABLE_READ);
try {
Statement statement = connection.createStatement();
if (!expectedVal.equals(oldTable.getParameters().get(key))) {
throw new MetaException("Table has been modified");
}
int affectedRows = statement.executeUpdate("UPDATE TABLE_PARAMS SET
PARAM_VALUE = 'new_val' WHERE TBL_ID = ... AND PARAM_KEY = 'key' AND
PARAM_VALUE = 'expected_val'");
if (affectedRows != 1) {
throw new MetaException("Table has been modified");
}
connection.commit();
} catch (Throwable t) {
connection.rollback();
throw t;
} finally {
connection.close();
}
{code}
A problem is each iceberg commit can modify multiple properties or even other
table fields. So it can be difficult to generate all the SQLs manually. Not
sure how (or whether possible) to do this with JDO.
was (Author: lirui):
I tested again with MariaDB and there're fewer commit conflicts in HMS log than
in the test log. This is expected because iceberg checks for conflict itself
before it calls {{{}alter_table{}}}. The number of conflicts triggered by HMS
is the same as the number in HMS log.
I also tested with Postgres and the result is correct. I read the
[doc|https://www.postgresql.org/docs/14/transaction-iso.html#XACT-REPEATABLE-READ]
and I think it works because:
{quote}a repeatable read transaction cannot modify or lock rows changed by
other transactions after the repeatable read transaction began
{quote}
But I suspect this is stricter than the ANSI SQL standard. I checked SQL:2011,
and it says the following about {{SERIALIZABLE}} level:
{quote}The execution of concurrent SQL-transactions at transaction isolation
level SERIALIZABLE is guaranteed to be serializable. A serializable execution
is defined to be an execution of the operations of concurrently executing
SQL-transactions that produces the same effect as some serial execution of
those same SQL-transactions. A serial execution is one in which each
SQL-transaction executes to completion before the next SQL-transaction begins.
{quote}
Suppose we have these two concurrent transactions trying to update the
property. IIUC both transactions can commit and the result can be either {{v1}}
or {{{}v2{}}}, even for {{SERIALIZABLE}} level.
{code:sql}
txn1> update tbl set val = 'v1' where key = 'k';
txn2> update tbl set val = 'v2' where key = 'k';
{code}
Maybe another solution is to use direct SQL and checks for the number of
affected rows to detect conflict. We did a PoC for this and it also provides
the correct results. The pseudo code is like this:
{code:java}
String key = ...;
String expectedVal = ...;
Table oldTable = ...;
Table newTable = ...;
Connection connection = getConnection(Connection.TRANSACTION_REPEATABLE_READ);
try {
Statement statement = connection.createStatement();
if (!expectedVal.equals(oldTable.getParameters().get(key))) {
throw new MetaException("Table has been modified");
}
int affectedRows = statement.executeUpdate("UPDATE TABLE_PARAMS SET
PARAM_VALUE = 'new_val' WHERE TBL_ID = ... AND PARAM_KEY = 'key' AND
PARAM_VALUE = 'expected_val'");
if (affectedRows != 1) {
throw new MetaException("Table has been modified");
}
connection.commit();
} catch (Throwable t) {
connection.rollback();
throw t;
} finally {
connection.close();
}
{code}
A problem is each iceberg commit can modify multiple properties or even other
table fields. So it can be difficult to generate all the SQLs manually. Not
sure how (or whether possible) to do this with JDO.
> Allow transactional check of Table parameter before altering the Table
> ----------------------------------------------------------------------
>
> Key: HIVE-26882
> URL: https://issues.apache.org/jira/browse/HIVE-26882
> Project: Hive
> Issue Type: Improvement
> Components: Standalone Metastore
> Reporter: Peter Vary
> Assignee: Peter Vary
> Priority: Major
> Labels: pull-request-available
> Fix For: 2.3.10, 4.0.0-beta-1
>
> Time Spent: 4h 40m
> Remaining Estimate: 0h
>
> We should add the possibility to transactionally check if a Table parameter
> is changed before altering the table in the HMS.
> This would provide an alternative, less error-prone and faster way to commit
> an Iceberg table, as the Iceberg table currently needs to:
> - Create an exclusive lock
> - Get the table metadata to check if the current snapshot is not changed
> - Update the table metadata
> - Release the lock
> After the change these 4 HMS calls could be substituted with a single alter
> table call.
> Also we could avoid cases where the locks are left hanging by failed processes
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
