You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by Eric <et...@gmail.com> on 2019/09/08 22:31:23 UTC

Would sharded database has data integrity issue when a shard fail

Hi,

If a shard fail on a transaction, would the transactions on the other
shards rolled back together?

Let say I have 100 payments being updated in one sql statement (e.g. update
payment set remark = 'xxx' where customer = 'yyy'), the records are
distributed on several shards. A shard failed to update the records. Would
other shards roll back the updates?

Best Regards,
Eric Poon

Re:Re: Re: Would sharded database has data integrity issue when a shard fail

Posted by Sion Yang <sc...@163.com>.
Hi Eric,


SCTL is same as normal SQL, it can be send by the connection and statement of sharding-proxy.
```
// datasource : datasource to sharding-proxy


Connection connection = datasource.getConnection();
Statement statement = connection.createStatement()
statement.execute("sctl:set transaction_type=local/xa/base")
connection.begin();
...(execute your business)
connection.commit();


```


--

Yi Yang(Sion)
Apache ShardingSphere
 

At 2019-09-09 22:31:29, "Eric" <et...@gmail.com> wrote:
>Hi Sion,
>
>That's very helpful but I don't know how to send the SCTL to the proxy. Is
>it a custom Java program, command prompt, or inside a configuration file?
>
>Please help.
>
>Best Regards,
>Eric Poon
>
>On Mon, Sep 9, 2019 at 10:52 AM Sion Yang <sc...@163.com> wrote:
>
>> Do you use sharding-proxy?
>>
>>
>> If you use sharding-proxy, you can send `sctl:set transaction_type=`
>> before you begin transaction.
>> you also can get information in
>> https://shardingsphere.apache.org/document/current/en/manual/sharding-proxy/usage/
>>
>>
>> --
>>
>> Yi Yang(Sion)
>> Apache ShardingSphere
>>
>>
>>
>> At 2019-09-09 10:28:42, "Eric" <et...@gmail.com> wrote:
>> >Hi Sion,
>> >
>> >Thank you. The document uses TransactionTypeHolder to modify transaction
>> >type in Java. Is it possible to set it in proxy yaml file?
>> >
>> >Regards,
>> >Eric Poon
>> >
>> >On Mon, Sep 9, 2019 at 8:45 AM Sion Yang <sc...@163.com> wrote:
>> >
>> >> Hi,
>> >>
>> >>
>> >> There are three kinds of distributed transaction in ShardingSphere,
>> local,
>> >> two phase commit and BASE.
>> >> Default is local transaction.
>> >>
>> >>
>> >> For local transaction, if the SQL execute failed before commit, all
>> >> sharding data node will be rollback.
>> >> If failed in commit, the other sharding data node will not be rollback.
>> >>
>> >>
>> >> Detail information can see the document
>> >>
>> https://shardingsphere.apache.org/document/current/en/features/transaction/
>> >>
>> >>
>> >> --
>> >>
>> >> Yi Yang(Sion)
>> >> Apache ShardingSphere
>> >>
>> >>
>> >>
>> >> At 2019-09-09 06:31:23, "Eric" <et...@gmail.com> wrote:
>> >> >Hi,
>> >> >
>> >> >If a shard fail on a transaction, would the transactions on the other
>> >> >shards rolled back together?
>> >> >
>> >> >Let say I have 100 payments being updated in one sql statement (e.g.
>> >> update
>> >> >payment set remark = 'xxx' where customer = 'yyy'), the records are
>> >> >distributed on several shards. A shard failed to update the records.
>> Would
>> >> >other shards roll back the updates?
>> >> >
>> >> >Best Regards,
>> >> >Eric Poon
>> >>
>>

Re: Re: Would sharded database has data integrity issue when a shard fail

Posted by Eric <et...@gmail.com>.
Hi Sion,

That's very helpful but I don't know how to send the SCTL to the proxy. Is
it a custom Java program, command prompt, or inside a configuration file?

Please help.

Best Regards,
Eric Poon

On Mon, Sep 9, 2019 at 10:52 AM Sion Yang <sc...@163.com> wrote:

> Do you use sharding-proxy?
>
>
> If you use sharding-proxy, you can send `sctl:set transaction_type=`
> before you begin transaction.
> you also can get information in
> https://shardingsphere.apache.org/document/current/en/manual/sharding-proxy/usage/
>
>
> --
>
> Yi Yang(Sion)
> Apache ShardingSphere
>
>
>
> At 2019-09-09 10:28:42, "Eric" <et...@gmail.com> wrote:
> >Hi Sion,
> >
> >Thank you. The document uses TransactionTypeHolder to modify transaction
> >type in Java. Is it possible to set it in proxy yaml file?
> >
> >Regards,
> >Eric Poon
> >
> >On Mon, Sep 9, 2019 at 8:45 AM Sion Yang <sc...@163.com> wrote:
> >
> >> Hi,
> >>
> >>
> >> There are three kinds of distributed transaction in ShardingSphere,
> local,
> >> two phase commit and BASE.
> >> Default is local transaction.
> >>
> >>
> >> For local transaction, if the SQL execute failed before commit, all
> >> sharding data node will be rollback.
> >> If failed in commit, the other sharding data node will not be rollback.
> >>
> >>
> >> Detail information can see the document
> >>
> https://shardingsphere.apache.org/document/current/en/features/transaction/
> >>
> >>
> >> --
> >>
> >> Yi Yang(Sion)
> >> Apache ShardingSphere
> >>
> >>
> >>
> >> At 2019-09-09 06:31:23, "Eric" <et...@gmail.com> wrote:
> >> >Hi,
> >> >
> >> >If a shard fail on a transaction, would the transactions on the other
> >> >shards rolled back together?
> >> >
> >> >Let say I have 100 payments being updated in one sql statement (e.g.
> >> update
> >> >payment set remark = 'xxx' where customer = 'yyy'), the records are
> >> >distributed on several shards. A shard failed to update the records.
> Would
> >> >other shards roll back the updates?
> >> >
> >> >Best Regards,
> >> >Eric Poon
> >>
>

Re:Re: Would sharded database has data integrity issue when a shard fail

Posted by Sion Yang <sc...@163.com>.
Do you use sharding-proxy?


If you use sharding-proxy, you can send `sctl:set transaction_type=` before you begin transaction.
you also can get information in https://shardingsphere.apache.org/document/current/en/manual/sharding-proxy/usage/


--

Yi Yang(Sion)
Apache ShardingSphere



At 2019-09-09 10:28:42, "Eric" <et...@gmail.com> wrote:
>Hi Sion,
>
>Thank you. The document uses TransactionTypeHolder to modify transaction
>type in Java. Is it possible to set it in proxy yaml file?
>
>Regards,
>Eric Poon
>
>On Mon, Sep 9, 2019 at 8:45 AM Sion Yang <sc...@163.com> wrote:
>
>> Hi,
>>
>>
>> There are three kinds of distributed transaction in ShardingSphere, local,
>> two phase commit and BASE.
>> Default is local transaction.
>>
>>
>> For local transaction, if the SQL execute failed before commit, all
>> sharding data node will be rollback.
>> If failed in commit, the other sharding data node will not be rollback.
>>
>>
>> Detail information can see the document
>> https://shardingsphere.apache.org/document/current/en/features/transaction/
>>
>>
>> --
>>
>> Yi Yang(Sion)
>> Apache ShardingSphere
>>
>>
>>
>> At 2019-09-09 06:31:23, "Eric" <et...@gmail.com> wrote:
>> >Hi,
>> >
>> >If a shard fail on a transaction, would the transactions on the other
>> >shards rolled back together?
>> >
>> >Let say I have 100 payments being updated in one sql statement (e.g.
>> update
>> >payment set remark = 'xxx' where customer = 'yyy'), the records are
>> >distributed on several shards. A shard failed to update the records. Would
>> >other shards roll back the updates?
>> >
>> >Best Regards,
>> >Eric Poon
>>

Re: Would sharded database has data integrity issue when a shard fail

Posted by Eric <et...@gmail.com>.
Hi Sion,

Thank you. The document uses TransactionTypeHolder to modify transaction
type in Java. Is it possible to set it in proxy yaml file?

Regards,
Eric Poon

On Mon, Sep 9, 2019 at 8:45 AM Sion Yang <sc...@163.com> wrote:

> Hi,
>
>
> There are three kinds of distributed transaction in ShardingSphere, local,
> two phase commit and BASE.
> Default is local transaction.
>
>
> For local transaction, if the SQL execute failed before commit, all
> sharding data node will be rollback.
> If failed in commit, the other sharding data node will not be rollback.
>
>
> Detail information can see the document
> https://shardingsphere.apache.org/document/current/en/features/transaction/
>
>
> --
>
> Yi Yang(Sion)
> Apache ShardingSphere
>
>
>
> At 2019-09-09 06:31:23, "Eric" <et...@gmail.com> wrote:
> >Hi,
> >
> >If a shard fail on a transaction, would the transactions on the other
> >shards rolled back together?
> >
> >Let say I have 100 payments being updated in one sql statement (e.g.
> update
> >payment set remark = 'xxx' where customer = 'yyy'), the records are
> >distributed on several shards. A shard failed to update the records. Would
> >other shards roll back the updates?
> >
> >Best Regards,
> >Eric Poon
>

Re:Would sharded database has data integrity issue when a shard fail

Posted by Sion Yang <sc...@163.com>.
Hi,


There are three kinds of distributed transaction in ShardingSphere, local, two phase commit and BASE.
Default is local transaction.


For local transaction, if the SQL execute failed before commit, all sharding data node will be rollback.
If failed in commit, the other sharding data node will not be rollback.


Detail information can see the document https://shardingsphere.apache.org/document/current/en/features/transaction/


--

Yi Yang(Sion)
Apache ShardingSphere



At 2019-09-09 06:31:23, "Eric" <et...@gmail.com> wrote:
>Hi,
>
>If a shard fail on a transaction, would the transactions on the other
>shards rolled back together?
>
>Let say I have 100 payments being updated in one sql statement (e.g. update
>payment set remark = 'xxx' where customer = 'yyy'), the records are
>distributed on several shards. A shard failed to update the records. Would
>other shards roll back the updates?
>
>Best Regards,
>Eric Poon