[Improvement] Improve the current SQL of JDBCBackend

[jerryshao]

What would you like to be improved?

In the current implementation of xxxMetaMapper and xxxMetaService, we seldom use JOIN and transactions, for example:

The list catalog operation will issue two SQL queries, the first one is to get a metalake id, then using this id to get the catalog list.

Instead of issuing two queries, we can use JOIN to join catalog and metalake table to get catalog list by one query, this will save the IO time and avoid inconsistent problem.

Also for updateCatalog

We have several steps:

1. get metalake id.
2. get catalog PO.
3. update the catalog object.
4. update the catalog in RDBMS.

These 4 steps are not in the transaction, which will potentially meet the inconsistent problem, and we highly leverage the lock to avoid the concurrent problem. A better solution is to put these 4 steps into a transaction.

How should we improve?

So basically, we can improve the current SQL to:

1. reduce the queries and increase the performance.
2. Put multiple step actions into one transaction to leverage DB's transaction to keep consistent.

[jerryshao]

CC @xloya @yuqi1129 , please take a look when you have time.

[jerryshao]

It's a big work to change everything, we can separate it into small tasks to iterate one by one. Currently, there's no functionality issue here since we have locks to guarantee the concurrency.

[xloya]

1. I think there is no problem with using JOIN to reduce the number of queries.
2. As for whether to put all operations in the same transaction, I may have some doubts, because this may cause a database transaction to be too large and cause deadlock and other problems. This concern comes from the implementation of Hive Metastore, which encapsulates all database operations into one transaction, which has a great impact on performance.
3. I think we can consider whether it is enough to ensure transactionality with upper-level locks (later, while supporting horizontal expansion of the server, ensuring transactionality), or whether a large database transaction is really needed.

[jerryshao]

I have no clear answer for now. I think we can do some tests to see if transaction is OK or not. But for join I think we can do some changes somehow. For example, like delete operation, we can use join to do it and check the return value, you can check my code #4019 , I use join to handle some cases. @xloya

[zhengkezhou1]

Hi @jerryshao Can I work on: using JOIN to reduce the number of queries?

[yuqi1129]

Hi @jerryshao Can I work on: using JOIN to reduce the number of queries?

Sure, please go ahead if you are interested in this issue.