At present, the popular implementation of open source database sharding is based on THE rewriting, distribution and result merging of SQL. This paper tries to explain the origin of these defects and provide a new idea for the traditional database sharding implementation.

Popular sharding framework implementation analysis

Currently, the open source Relation Database (RDB) sharding framework is popular in China. Whether sharding is carried out at the application end or using middleware, it is based on SQL. The main process is as follows:

  1. Parse the upper-level incoming SQL
  2. Combined with the corresponding sub-table and sub-library configuration, the incoming SQL is rewritten and distributed to the corresponding standalone database
  3. After obtaining the result of each stand-alone database, the result expected by the original SQL is returned based on the original SQL merging result

This implementation is intended to provide a solution that shields the underlying sharding logic from the SQL layer, with only one RDB for the upper application, so that the application can transparently access multiple databases.

Related Manufacturer content

Document detection based on convolutional neural network in mobile phone

Alibaba Group hundred billion level store system architecture platform technology practice

Soft load SLB practice for the fourth generation architecture of Ctrip

Interpretation of Baidu PB-level data warehouse Palo open source architecture

Get the latest tools, frameworks for artificial intelligence, machine learning, and deep learning

Related Sponsors

Explore 2017 forward-looking hot technologies with 100+ domestic and foreign technical experts

However, this is just a beautiful goal. For a variety of reasons, the current popular open source SQL layer sharding scheme does not provide the same functionality as the native database:

  • The A in ACID is not guaranteed
  • The C in ACID can be broken
  • The I (isolation) in ACID is inconsistent with native
  • Due to the complexity of SQL parsing and performance considerations, many SQL databases are not supported

In addition to the above differences from native databases, read/write separation is not perfectly implemented in SQL-based frameworks.

Let’s take a look at the reasons why sqL-based sharding frameworks don’t meet the above requirements.

Note:

Some commercial database middleware with a lot of magic modification can implement AC features consistent with stand-alone database, and I features can reach RR (Reapeatable Read) level. However, its implementation is relatively complex, and a lot of processing logic has been migrated to middleware, and it is not open source, so it will not be discussed in this article.

atomic

Sharding frameworks have two options if they want to ensure atomicity across sharding.

2PC(Two-phase Commit)

It uses blocking wait mechanisms such as WriteAheadLog and locks to ensure strong consistency of distributed transactions.

The final agreement

It does not require multiple distributed systems to immediately reach a consistent state for a cross-library transaction, as long as they eventually reach a consistent state at a certain point in time.

Using 2PC, as everyone has heard, degrades performance (reportedly around 20%) due to the need to synchronize and coordinate data processing and maintain the locks used.

If a shard node goes down, all transactions related to the shard will slow down (other shards wait for feedback from the shard and roll back after time), or even cause an avalanche effect that makes the entire cluster unusable.

At the same time, the implementation complexity of 2PC is also high. As far as I know, there is no transaction support for 2PC in the current open source implementation.

Final consistency enables the ultimate atomicity of large transactions across shards. As long as the originator shard works, the client operation can proceed. However, achieving ultimate consistency at the SQL layer is not appropriate.

First, the ability to use final consistency is determined by the business and is strongly coupled to the business design. Second, final consistency based on the SQL layer requires that cross-shard SQL must be repeatable (idempotent), such as

__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__update account set account.money=1000; __Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__Copy the code

And can’t be

__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__update account set account.money =  account.money + 1000; __Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__Copy the code

At the same time, if the final agreement is not reached, the record is read, and the judgment and record modification are carried out depending on this, the wrong data will be generated. The meaning of ACID in a standalone database is gone.

We can at least infer that this sort of final consistency is offside at the SQL level, where it cannot be elegantly implemented, and that final consistency is a service level (business level) affair.

Therefore, open source sharding frameworks basically do not guarantee A. To implement cross-library Update operations, SQL-based sharding frameworks have coined the concept of “weak XA”. Its main implementation process is as follows:

  • Execute A sharded SQL

  • Execute SQL for shard B

  • Perform the commit of library A

  • Perform the commit of library B

This can be done: the whole transaction can be perfectly rolled back if there are any exceptions before the COMMIT.

However, there is A disadvantage: when library A commits, if library B fails, the whole transaction will be in an inconsistent state.

Of course, when trading volume is low, there is less chance of this happening. But when the volume of transactions changes, there will always be errors due to network jitter and other reasons.

consistency

In fact, consistency is related to atomicity. As long as atomicity cannot be guaranteed, consistency cannot be guaranteed.

Isolation level

The isolation level is defined in SQL92 as dirty read, read committed, repeatable read, and serializable.

They are not trivial database parameters. Different isolation levels correspond to different code logic in our application to achieve business consistency.

However, the transaction isolation level provided by SQL-based sharding frameworks for cross-shard access is not defined in the native database, but a new and not widely understood transaction isolation level.

Failure to understand this distinction can lead to errors in program execution that the programmer himself does not understand.

SQL compatibility

Due to the complexity and performance of SQL parsing, many SQL parsing is not supported by SQL-based sharding frameworks. And because SQL parsing is really complex, there is a lot of potential for bugs in the SQL that it claims to support.

As for the specific SQL is not supported, this article will not analyze, you can read the relevant framework documentation.

Reading and writing separation

Read/write separation is not supported in native databases, but a good read/write separation implementation should be able to run read transactions in the library.

Many people, however, consider adding transactions pointless for read-only operations. If you know someone like that, I don’t think you understand the I in ACID very well.

For example, an interface needs to display a customer’s balance and his transfer records. If the balance and transfer record query is not a RR level transaction, then the query results may appear balance and transfer records do not match the situation!

If it’s a customer UI query, it’s ok to refresh it. But if it’s to generate reconciliation documents, isn’t that a big problem?

However, in a SHARding implementation based on the SQL layer, there is no way to achieve perfect read and write separation. Since there is no way to know whether the transaction is a read or write transaction, there is no way to determine whether a walk or write library is required during the first SQL execution.

Therefore, the approach in SQL layer based sharding framework is basically:

  • If there is a transaction, go directly to the write library

  • If there is no transaction, the thread accesses the library directly. If an Update operation occurs, the thread accesses the library in its subsequent operations

Therefore, this separation of reads and writes is not perfect. To achieve RR level reads, only the master library can be used, or only a data set with chaotic isolation level can be returned.

New sharding idea – service layer sharding

Because the ACID properties provided by THE SQL-based sharding framework are different from those provided by the native one, it is necessary to clarify the ACID properties encapsulated by the sharding framework in the upper-layer APPLICATION of SQL coding, and write the corresponding adjusted SQL according to the characteristics of the framework to obtain correct and reliable code logic. The nature of the sharding framework has always influenced the actual form of SQL.

Therefore, SQL-based sharding schemes are not transparent to the application layer.

If you want to write sound and reliable code based on the FRAMEWORK of the SQL layer, in most cases you need to follow the principle that all transactions (including read and write) should not cross libraries. (Some businesses that do not require a high isolation level may allow reads across libraries)

To follow the guidelines above, in fact, most of the time the sharding framework is simply a matter of selecting an appropriate data source.

Combining the previous analysis of read/write separation, if the sharding implementation is moved to the Service layer, it is clear that there are two benefits:

  • The Service layer has explicit read/write transaction information to achieve the desired RR level of read/write separation

  • The input of the Service layer has the information to select shards, which can be selected without complicated SQL parsing, and accomplish most of the requirements of our sharding framework

Based on the above two advantages, I tried to write a sharding framework based on Service layer. The following shows how I use a framework to realize sharding in Service layer, and we will analyze the advantages and disadvantages of this implementation in more detail.

Basic usage

The Service layer

__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__@Service
@ShardingContext(dataSourceSet="orderSet",
shardingKeyEls="[user].userId",
shardingStrategy="@modUserId",
generateIdStrategy="@snowflaker",
generateIdEls="[user].userId")
public class UserServceImpl {
@Autowired
private UserDaoImpl userDao;
@Transactional
@SelectDataSource
public void updateUser(User user){
userDao.updateUser(user);
}
@Transactional
@SelectDataSource
@GenerateId
public void saveUser(User user){
userDao.saveUser(user);
}
@Transactional(readOnly=true)
@SelectDataSource(keyNameEls="[userId]")
public User findUser(int userId){
return userDao.findUser(userId);
}
public List<User> findAllUsers(){
return userDao.findAllUsers();
}
    public double calcUserAvgAge(){
    List<User> allUsers = userDao.findAllUsers();
    return allUsers.stream().mapToInt(u->u.getAge())
.average().getAsDouble();
    }__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__Copy the code

ShardingContext indicates the ShardingContext of the current Service. That is, if some parameters are not specified, such as data source selection, result reduction from Map to database, and ID generation, All from the configuration inside this ShardingContext

@selectdatasource indicates that the SQL executed in the method selects a shard data source based on the shard policy. The shard data source cannot be changed until the method returns

@GenerateID generates the ID and assigns it to the specified position of the argument

The logic corresponding to @GenerateId is executed first, then to @selectdatasource, then to @Transaction

The @Transactional(readOnly= True) tag specifies that the transaction is read-only, so the framework automatically selects read libraries (if any) based on the readOnly flag.

It can be seen from the calcUserAvgAge method that under the LAMBADA expression and Stream functions of JDK8, JAVA analysis and processing of set data becomes very simple, and the complexity is basically the same as SQL statements, which will greatly reduce the complexity of our own processing of fragment data.

DAO layer

__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__@Component public class UserDaoImpl { @Autowired private JdbcTemplate jdbcTemplate; public void updateUser(User user){ int update = jdbcTemplate.update ("UPDATE `user` SET `name`=? WHERE `user_id`=? ;" ,user.getName(),user.getUserId()); Assert.isTrue(update == 1,"it should be updated!" ); } public User findUser(int userId){ return jdbcTemplate.queryForObject ("SELECT * FROM user WHERE user_id = ?" , new Object[]{userId}, rowMapper); } @Transactional @MapReduce public List<User> findAllUsers(){ return jdbcTemplate.query ("SELECT * FROM user", rowMapper); } @Transactional(readOnly=true) @MapReduce public void findAllUsers (ReduceResultHolder resultHolder){ List<User> shardingUsers = jdbcTemplate.query ("SELECT * FROM user", rowMapper); resultHolder.setShardingResult(shardingUsers); } }__Tue Sep 26 2017 11:05:21 GMT+0800 (CST)____Tue Sep 26 2017 11:05:21 GMT+0800 (CST)__Copy the code

@mapReduce indicates that this method is executed once for each data fragment, and then returns after data aggregation.

Methods that return the same data type before and after the aggregation can be called to get the aggregation result directly from the return value.

For methods that return inconsistent data types before and after the aggregation, an object ReduceResultHolder needs to be passed in, and the aggregation results can be obtained through this object after the call is completed.

By default, the framework provides a generic Reduce strategy that returns a sum if it is a number, a merge if it is Collection and its subclasses, and a merge if it is MAP.

If the Reduce policy is not suitable, users can design and specify Reduce policies.

@transaction indicates that the SQL executed by each shard is in a Transaction, not that the aggregate operation is a whole Transaction. Therefore, MapReduce is better off not doing updates (considering the framework hierarchy limit MapReduce only allows ReadOnly transactions, which is still unrestricted).

The operations performed by @MapReduce precede @Transaction.

Advantages and disadvantages analysis

In view of the above implementation, we can analyze the pros and cons of such an implementation.

advantages

  • The framework is simple to implement, no SQL parsing rewriting and other complex logic, fewer bugs in theory, but also easy to check bugs

    Personally, I think it is important that this simple framework is at least within the user’s control.

  • Full database, full SQL compatibility

    SQL layer sharding cannot be done.

  • Perfect read-write separation

    Service layer sharding Determines that a Service is a read transaction before it starts. The entire read transaction is completed in a read library. The isolation level is the same as that of the database, achieving perfect RR read and write separation.

  • Sharding is completed using annotations without business logic intrusion

    If annotations are intrusive, then Spring is thoroughly immersed in our business code.

    Annotations can be used to explicitly remind programmers and REVIEWER whether this is a single or cross-library access, unlike SQL-based sharding, which implicitly does an unsafe cross-library operation.

  • Isolation level and transaction atomicity are consistent with the database used, no additional learning burden, easy to write the correct program

    The framework restricts all transactions to a single repository.

  • No additional burden of maintaining DBProxy availability

  • No SQL parsing cost, higher performance

disadvantages

  • Cross-library queries require their own aggregation of results

    • It’s a weakness and a strength

    • Disadvantages: Additional aggregation code to complete

    • Advantages: It explicitly tells the programmer that this is an aggregation operation, and it can be better tuned. Using JDK8’s Stream and Lambada expressions, related collection processing can be done as easily as writing SQL. Also, in properly designed code, aggregation operations should only exist in a small number of non-core operations, so it doesn’t add too much work. The aggregation of the results can actually be scaled up to the logic of the Service layer (apart from the strict domain design model).

  • Cross-repository transactions require self-assurance

    • It’s a weakness and a strength

    • Disadvantages: Additional self-implementation of cross-library transactions

    • Advantages: All open source sharding frameworks currently implement cross-library transactions with limitations or limitations. Therefore, it may be better to adopt explicit transaction control on your own and combine it with the business to achieve ultimate consistency. See EasyTransaction, another framework I wrote.

  • A single library sub-table cannot be implemented

  • In fact, a single database table is not necessary, which can be used to achieve the original table partition data, the same performance, use more convenient

  • If you refuse to use the database’s native table partitioning and use the overwritten table name for internal sharding, you’d better know what the meaning is and not just assume it.

conclusion

In fact, I’m not a fan of SQL-based sharding frameworks, and I really admire the people who have contributed to these open source frameworks, so this article just gives you some new ideas for sharding. If this article can give you some small inspiration, I hope you can help me add stars to my two projects, thank you.

Service layer sharding framework.

Distributed flexible transaction framework.

The authors introduce

Xu Deyou is the cloud platform architect of Credit Card Center of Citic Bank. He once worked in China Merchants Bank Software Center (China Merchants Bank Technology) and Qiniu Internet Finance. I have always been a financial coder, skilled in microservices, distributed transactions, data sharding, data consistency theory and so on.

Thanks to Yuta Tian guang for correcting this article.

To contribute or translate InfoQ Chinese, please email [email protected]. You are also welcome to follow us on Sina Weibo (@InfoQ, @Ding Xiaoyun) and wechat (wechat id: InfoQChina).

Related topics:
  • Language & Development Language & Development focus on 23 of his fans
  • Architecture & Design Architecture & Design focus 149 his fans
  • Data sharding data sharding concerns 0 his fans
  • Shard Shard follows 0 his fans

The related content

NGINX releases previews of its microservices platform, OpenShift Ingress Controller, and Service Mesh

How is continuous delivery measured

Apple’s iPhone X has a custom-built neural engine processor

Preparation before a speech

Use modern Agility and OKR to think outside the “feature factory” mindset

Hello, friend!

Register an InfoQ account
The login


Get more experiences from InfoQ.

Tell us what you think

Watch Thread
supportPosted by Duanmuxuan 1 hour ago

Supported 1 hour ago by Duanmuxuan

Shut down
Shut down
Shut down