[
https://issues.apache.org/jira/browse/HIVE-26882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824303#comment-17824303
]
Rui Li edited comment on HIVE-26882 at 3/7/24 8:34 AM:
-------------------------------------------------------
[~pvary] Thanks for your comment. I ran some test for the scenarios mentioned
above. Suppose the table is initialized like this:
{code:sql}
select * from tbl;
key | val
-----+-----
k | v0
(1 row)
{code}
With Postgres:
{code:sql}
txn1> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
txn1> select * from tbl;
key | val
-----+-----
k | v0
(1 row)
txn1> update tbl set val = 'v1' where key = 'k' and val = 'v0';
UPDATE 1
txn1> select * from tbl;
key | val
-----+-----
k | v1
(1 row)
txn1> commit;
COMMIT
---------------------------------------------------------------------------------------------
txn2> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
txn2> select * from tbl;
key | val
-----+-----
k | v0
(1 row)
txn2> update tbl set val = 'v2' where key = 'k' and val = 'v0';
ERROR: could not serialize access due to concurrent update
txn2> select * from tbl;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
txn2> commit;
ROLLBACK
{code}
With MariaDB:
{code:sql}
txn1> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
txn1> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn1> update tbl set val = 'v1' where `key` = 'k' and val = 'v0';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v1 |
+------+------+
1 row in set (0.000 sec)
txn1> commit;
Query OK, 0 rows affected (0.001 sec)
---------------------------------------------------------------------------------------------
txn2> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
txn2> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn2> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn2> update tbl set val = 'v2' where `key` = 'k' and val = 'v0';
Query OK, 0 rows affected (20.548 sec)
Rows matched: 0 Changed: 0 Warnings: 0
txn2> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn2> commit;
Query OK, 0 rows affected (0.000 sec)
{code}
I understand the MariaDB behavior is not {{SERIALIZABLE}} because no serial
execution can produce the same result. But I'm not sure whether it violates
{{REPEATABLE_READ}} – the two select return same results and phantom read is
allowed.
So I also tested MariaDB at {{SERIALIZABLE}} level:
{code:sql}
txn1> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
txn1> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.001 sec)
txn1> update tbl set val = 'v1' where `key` = 'k' and val = 'v0';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v1 |
+------+------+
1 row in set (0.001 sec)
txn1> commit;
Query OK, 0 rows affected (0.001 sec)
---------------------------------------------------------------------------------------------
txn2> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
txn2> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn2> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.001 sec)
txn2> update tbl set val = 'v2' where `key` = 'k' and val = 'v0';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
{code}
The result is expected because txn2 is aborted. But I don't know why it didn't
give correct results when we changed HMS code to use SERIALIZABLE level. I'll
double check that.
was (Author: lirui):
[~pvary] Thanks for your comment. I ran some test for the scenarios mentioned
above. Suppose the table is initialized like this:
{code:SQL}
select * from tbl;
key | val
-----+-----
k | v0
(1 row)
{code}
With Postgres:
{code:SQL}
txn1> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
txn1> select * from tbl;
key | val
-----+-----
k | v0
(1 row)
txn1> update tbl set val = 'v1' where key = 'k' and val = 'v0';
UPDATE 1
txn1> select * from tbl;
key | val
-----+-----
k | v1
(1 row)
txn1> commit;
COMMIT
---------------------------------------------------------------------------------------------
txn2> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
txn2> select * from tbl;
key | val
-----+-----
k | v0
(1 row)
txn2> update tbl set val = 'v2' where key = 'k' and val = 'v0';
ERROR: could not serialize access due to concurrent update
txn2> select * from tbl;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
txn2> commit;
ROLLBACK
{code}
With MariaDB:
{code:SQL}
txn1> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
txn1> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn1> update tbl set val = 'v1' where `key` = 'k' and val = 'v0';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v1 |
+------+------+
1 row in set (0.000 sec)
txn1> commit;
Query OK, 0 rows affected (0.001 sec)
---------------------------------------------------------------------------------------------
txn2> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
txn2> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn2> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn2> update tbl set val = 'v2' where `key` = 'k' and val = 'v0';
Query OK, 0 rows affected (20.548 sec)
Rows matched: 0 Changed: 0 Warnings: 0
txn2> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.000 sec)
txn2> commit;
Query OK, 0 rows affected (0.000 sec)
{code}
I understand the MariaDB behavior is not {{SERIALIZABLE}} because no serial
execution can produce the same result. But I'm not sure whether it violates
{{REPEATABLE_READ}} -- the two select return same results and phantom read is
allowed.
So I also tested MariaDB at {{SERIALIZABLE}} level:
{code:SQL}
txn1> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
txn1> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.001 sec)
txn1> update tbl set val = 'v1' where `key` = 'k' and val = 'v0';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
txn1> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v1 |
+------+------+
1 row in set (0.001 sec)
txn1> commit;
Query OK, 0 rows affected (0.001 sec)
---------------------------------------------------------------------------------------------
txn2> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
txn2> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
txn2>MariaDB [test]> select * from tbl;
+------+------+
| key | val |
+------+------+
| k | v0 |
+------+------+
1 row in set (0.001 sec)
txn2> update tbl set val = 'v2' where `key` = 'k' and val = 'v0';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
{code}
The result is expected because txn2 is aborted. But I don't know why it didn't
give correct results when we changed HMS code to use SERIALIZABLE level. I'll
double check that.
> 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)
