[
https://issues.apache.org/jira/browse/HIVE-30218?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Work on HIVE-30218 started by Raghav Aggarwal.
----------------------------------------------
> 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
> Labels: pull-request-available
>
> 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 *5 database commands instead of 7*
> (eliminating 2
> redundant round-trips on every transaction):
> *Before (HikariCP 4.0.3 - 7 commands):*
> {noformat}
> Query SELECT @@session.transaction_isolation
> Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED <--
> Redundant #1
> 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{noformat}
> *After (HikariCP 7.1.0 with READ_COMMITTED default - 5 commands):*
> {noformat}
> Query SELECT @@session.transaction_isolation
> Query SET autocommit=0
> Query SELECT COUNT FROM "TXNS" WHERE "TXN_STATE" = 'o' <-- Actual Query
> Query COMMIT
> Query SET autocommit=1{noformat}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
