# Merging and Migrating Data from Sharded Tables

+ `TiDB Data Migration` can merge and migrate the data from multiple upstream sharded tables into one table in `TiDB`
+ Set global parameter **`shard-mode`**
+ Two modes available: 
  + Pessimistic mode: `DDL` on upstream databases can block `DML` replications, manual coordinating `DDL` is required
  + Optimistic mode: `DDL` on upstream databases do NOT block `DML` replications, might cause data inconsistency

| Pessimistic Mode                                                                   | Optimistic Mode                                                                                               |
| :--------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------------------ |
| Sharded tables that executes `DDL` suspend `DML` replication                       | Sharded tables that executes `DDL` will not block `DML` replication                                           |
| The `DDL` execution order and statements of each sharded table must be the same    | Each sharded table only needs to keep the table schema compatible with each other                             |
| The `DDL` is migrated to the downstream after the entire shard group is consistent | The `DDL` of each sharded table immediately affects the downstream                                            |
| Wrong changes caused by improper `DDL` can be intercepted after the detection      | Wrong changes caused by improper `DDL` will be replicated to downstream `TiDB`, which may cause inconsistency |

```
name: test                    
shard-mode: "pessimistic|optimistic"   
```
-------------------------------------------------------------
To merge and migrate data from sharded tables, you must set global parameter `shard-mode` in the task configuration file.

The pessimistic mode (default):
- When an upstream sharded table executes a `DDL` statement, the migration of this sharded table will be suspended. After all other sharded tables execute the same `DDL`, the `DDL` will be executed in the downstream and the data migration task will restart. The advantage of this mode is that it can ensure that the data migrated to the downstream will not go wrong.

The optimistic mode:
- `DM` will automatically modify the `DDL` executed on a sharded table into a statement compatible with other sharded tables, and then migrate to the downstream. This will not block the `DML` migration of any sharded tables. The advantage of this mode is that it will not block data migration when processing `DDL`. However, improper use will cause migration interruption or even data inconsistency.