[
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 *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}
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}
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
{nofomat}
> 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 *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)
