[
https://issues.apache.org/jira/browse/HIVE-30218?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Raghav Aggarwal updated HIVE-30218:
-----------------------------------
Description:
All Hive Metastore database operations run at {*}READ_COMMITTED{*}:
* {*}ACID Transactions ({{{}TxnHandler{}}}){*}: {{TransactionContextManager}}
explicitly requests {{{}Isolation.READ_COMMITTED{}}}.
* {*}Metadata / JDO ({{{}ObjectStore{}}}){*}: DataNucleus defaults to
{{{}datanucleus.transactionIsolation = "read-committed"{}}}.
However, {{HikariCPDataSourceProvider}} leaves the pool's default isolation
unconfigured. On {*}MySQL and MariaDB{*}, database connections therefore arrive
with *REPEATABLE READ*
----
Because the pooled connections arrive with {*}REPEATABLE READ{*}, Spring's
transaction manager ({{{}DataSourceTransactionManager{}}}) detects an isolation
mismatch on {*}every single transaction{*}:
# {*}At Transaction Start{*}: Spring detects the connection is at
{*}REPEATABLE READ{*}, saves it, and sends an extra network call to change it:
{code:sql}
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Round-trip #1
{code}
# {*}At Transaction End (Commit/Rollback){*}: Spring restores the connection
back to *REPEATABLE READ* before returning it to the pool:
{code:sql}
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Round-trip #2
{code}
On a simple HMS and HS2 startup, in the MySQL query logs a single query like
{{{}SELECT COUNT(\*) FROM TXNS{}}} produces {*}7 database commands instead of
4{*}:
{noformat}
Query SELECT @@session.transaction_isolation
Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED <-- Redundant
#1 (Extra round-trip)
Query SET autocommit=0
Query SELECT COUNT(*) FROM "TXNS" WHERE "TXN_STATE" = 'o' <-- Actual
Query
Query COMMIT
Query SET autocommit=1
Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ <-- Redundant
#2 (Extra round-trip) {noformat}
was:
All Hive Metastore database operations run at {*}READ_COMMITTED{*}:
* {*}ACID Transactions ({{{}TxnHandler{}}}){*}: {{TransactionContextManager}}
explicitly requests {{{}Isolation.READ_COMMITTED{}}}.
* {*}Metadata / JDO ({{{}ObjectStore{}}}){*}: DataNucleus defaults to
{{{}datanucleus.transactionIsolation = "read-committed"{}}}.
However, {{HikariCPDataSourceProvider}} leaves the pool's default isolation
unconfigured. On {*}MySQL and MariaDB{*}, database connections therefore arrive
with *REPEATABLE READ*
----
Because the pooled connections arrive with {*}REPEATABLE READ{*}, Spring's
transaction manager ({{{}DataSourceTransactionManager{}}}) detects an isolation
mismatch on {*}every single transaction{*}:
# {*}At Transaction Start{*}: Spring detects the connection is at
{*}REPEATABLE READ{*}, saves it, and sends an extra network call to change it:
{code:sql}
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Round-trip #1
{code}
# {*}At Transaction End (Commit/Rollback){*}: Spring restores the connection
back to *REPEATABLE READ* before returning it to the pool:
{code:sql}
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Round-trip #2
{code}
Even on an *idle* HiveServer2 instance, background threads
({{{}HouseKeeperService{}}}, {{{}CompactionCleaner{}}}, {{{}TxnCleaner{}}}) run
every second. A single simple query like
{{{}SELECT COUNT(*) FROM TXNS{}}} produces {*}7 database commands instead of
4{*}:
{noformat}
Query SELECT @@session.transaction_isolation
Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED <-- Redundant
#1 (Extra round-trip)
Query SET autocommit=0
Query SELECT COUNT(*) FROM "TXNS" WHERE "TXN_STATE" = 'o' <-- Actual
Query
Query COMMIT
Query SET autocommit=1
Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ <-- Redundant
#2 (Extra round-trip) {noformat}
> Eliminate redundant transaction isolation round-trips in Metastore and
> upgrade HikariCP to 7.1.0
> ------------------------------------------------------------------------------------------------
>
> Key: HIVE-30218
> URL: https://issues.apache.org/jira/browse/HIVE-30218
> Project: Hive
> Issue Type: Improvement
> Reporter: Raghav Aggarwal
> Assignee: Raghav Aggarwal
> Priority: Major
>
> All Hive Metastore database operations run at {*}READ_COMMITTED{*}:
> * {*}ACID Transactions ({{{}TxnHandler{}}}){*}:
> {{TransactionContextManager}} explicitly requests
> {{{}Isolation.READ_COMMITTED{}}}.
> * {*}Metadata / JDO ({{{}ObjectStore{}}}){*}: DataNucleus defaults to
> {{{}datanucleus.transactionIsolation = "read-committed"{}}}.
> However, {{HikariCPDataSourceProvider}} leaves the pool's default isolation
> unconfigured. On {*}MySQL and MariaDB{*}, database connections therefore
> arrive with *REPEATABLE READ*
> ----
> Because the pooled connections arrive with {*}REPEATABLE READ{*}, Spring's
> transaction manager ({{{}DataSourceTransactionManager{}}}) detects an
> isolation mismatch on {*}every single transaction{*}:
> # {*}At Transaction Start{*}: Spring detects the connection is at
> {*}REPEATABLE READ{*}, saves it, and sends an extra network call to change it:
> {code:sql}
> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Round-trip #1
> {code}
> # {*}At Transaction End (Commit/Rollback){*}: Spring restores the connection
> back to *REPEATABLE READ* before returning it to the pool:
> {code:sql}
> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Round-trip #2
> {code}
> On a simple HMS and HS2 startup, in the MySQL query logs a single query like
> {{{}SELECT COUNT(\*) FROM TXNS{}}} produces {*}7 database commands instead
> of 4{*}:
> {noformat}
> Query SELECT @@session.transaction_isolation
> Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED <--
> Redundant #1 (Extra round-trip)
> Query SET autocommit=0
> Query SELECT COUNT(*) FROM "TXNS" WHERE "TXN_STATE" = 'o' <-- Actual
> Query
> Query COMMIT
> Query SET autocommit=1
> Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ <--
> Redundant #2 (Extra round-trip) {noformat}
>
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
