官网解释:
功能
- 支持数据分片后的跨库XA事务
- 两阶段提交保证操作的原子性和数据的强一致性
- 服务宕机重启后,提交/回滚中的事务可自动恢复
- SPI机制整合主流的XA事务管理器,默认Atomikos,可以选择使用Narayana和Bitronix
- 同时支持XA和非XA的连接池
- 提供spring-boot和namespace的接入端
不支持项
- 服务宕机后,在其它机器上恢复提交/回滚中的数据
一、准备环境:
ps:两个order_db分布在不同的服务器上,192.168.87.133节点的order_db库中有三张表,t_dict,t_order_1,t_order_2,192.168.87.134节点上的order_db库中有两张表,t_order_1,t_order_2。
二、(三)Sharding-JDBC 整合mybatis-plus 水平分库、分表改造
application.yml:
#服务端口
server:
port: 56081
#服务名
spring:
application:
name: sharding-jdbc-examples
http:
encoding:
enabled: true
charset: utf-8
force: true
main:
allow-bean-definition-overriding: true
#shardingsphere相关配置
shardingsphere:
datasource:
names: m1,m2 #配置库的名字,随意
m1: #配置目前m1库的数据源信息
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.87.133:3306/order_db?useUnicode=true
username: root
password: 123456
m2:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.87.134:3306/order_db?useUnicode=true
username: root
password: 123456
sharding:
tables:
t_order:
key-generator:
column: user_id
type: SNOWFLAKE
actual-data-nodes: m$->{1..2}.t_order_$->{1..2} # 分库策略,以user_id为分片键,分片策略为user_id % 2 + 1,user_id为偶数操作m1数据源,否则操作m2。
database‐strategy: #分库策略
inline:
sharding‐column: user_id
algorithm‐expression: m$->{user_id % 2 + 1}
table‐strategy: #分表策略
inline:
sharding‐column: order_id
algorithm‐expression: t_order_$->{order_id % 2 + 1}
defaultDataSourceName: m1
props:
sql:
show: true #打印sql
#日志打印
logging:
level:
root: info
org.springframework.web: info
com.lucifer.sharding.dao: debug
druid.sql: debug
ps:由于t_dist这张表只出现在192.168.87.133这个服务器节点的order_db库中,因此需要配置属性defaultDataSourceName,设置默认数据库。否则会出现找不到数据库名为null的数据库名。
测试方法:t_dict这个表只存在192.168.87.133的order_db库中的,而t_order_*这个存在于两个库中,既有分库策略也有分表策略。
@Test
public void add() {
Dict dict = new Dict();
dict.setCode("111");
dict.setType("性别");
dict.setValue("男");
dictDao.insert(dict);
//伪造异常
int a=1/0;
Order order = new Order();
order.setPrice(BigDecimal.valueOf(0.1));
order.setStatus("0");
orderDao.insert(order);
}
1.首先看下未使用事务的情况:
2020-01-19 10:30:36.538 INFO 6980 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO t_dict (dict_id, code, type, value) VALUES (?, ?, ?, ?) ::: [1218722393623670785, 111, 性别, 男]
2020-01-19 10:30:36.572 DEBUG 6980 --- [ main] com.lucifer.sharding.dao.DictDao.insert : <== Updates: 1
java.lang.ArithmeticException: / by zero
at com.lucifer.sharding.ShardingJdbcExamplesApplicationTests.add(ShardingJdbcExamplesApplicationTests.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
控制台输出,可以看出在执行完m1库(192.168.87.133的order_db库中的t_dict)sql操作完后,报错。因此这个时候只有一条sql执行了。数据未回滚。
2.使用事务的情况
2.1.pom.xml:引入两个jar包
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-2pc-xa</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-spring</artifactId>
<version>3.1.0</version>
</dependency>
或使用下面这个jar包
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
2.2.springboot主配置类的SpringBootApplication注解去掉JtaAutoConfiguration这个配置类这个注入。
/**
* @author Lucifer
*/
@MapperScan("com.lucifer.sharding.dao")
@SpringBootApplication(exclude = JtaAutoConfiguration.class)
public class ShardingJdbcExamplesApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcExamplesApplication.class, args);
}
}
2.3测试方法: @ShardingTransactionType(value = TransactionType.XA)
@Transactional(rollbackFor =Exception.class) 两个注解需要配合使用。
@ShardingTransactionType(value = TransactionType.XA)
@Transactional(rollbackFor = Exception.class)
@Test
public void add() {
Dict dict = new Dict();
dict.setCode("111");
dict.setType("性别");
dict.setValue("男");
dictDao.insert(dict);
int a=1/0;
Order order = new Order();
order.setPrice(BigDecimal.valueOf(0.1));
order.setStatus("0");
orderDao.insert(order);
}
控制台输出:
2020-01-19 10:55:36.735 INFO 4660 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO t_dict (dict_id, code, type, value) VALUES (?, ?, ?, ?) ::: [1218728685880786946, 111, 性别, 男]
2020-01-19 10:55:36.757 DEBUG 4660 --- [ main] com.lucifer.sharding.dao.DictDao.insert : <== Updates: 1
2020-01-19 10:55:36.776 INFO 4660 --- [ main] o.s.t.c.transaction.TransactionContext : Rolled back transaction for test: [DefaultTestContext@563e4951 testClass = ShardingJdbcExamplesApplicationTests, testInstance = com.lucifer.sharding.ShardingJdbcExamplesApplicationTests@278fe428, testMethod = add@ShardingJdbcExamplesApplicationTests, testException = java.lang.ArithmeticException: / by zero, mergedContextConfiguration = [WebMergedContextConfiguration@4066c471 testClass = ShardingJdbcExamplesApplicationTests, locations = '{}', classes = '{class com.lucifer.sharding.ShardingJdbcExamplesApplication, class com.lucifer.sharding.ShardingJdbcExamplesApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@140c9f39, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@7690781, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@be35cd9, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@193f604a], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder' -> true]]
java.lang.ArithmeticException: / by zero
at com.lucifer.sharding.ShardingJdbcExamplesApplicationTests.add(ShardingJdbcExamplesApplicationTests.java:39)
数据库t_dict也回滚了。
测试异常在后的情况:
@ShardingTransactionType(value = TransactionType.XA)
@Transactional(rollbackFor = Exception.class)
@Test
public void add() {
Dict dict = new Dict();
dict.setCode("111");
dict.setType("性别");
dict.setValue("男");
dictDao.insert(dict);
Order order = new Order();
order.setPrice(BigDecimal.valueOf(0.1));
order.setStatus("0");
orderDao.insert(order);
int a=1/0;
}
根据控制台输出,知道t_dict执行成功, 而order的数据库操作最终是落在m2库的t_order_1表中。
2020-01-19 11:13:13.146 DEBUG 11876 --- [ main] com.lucifer.sharding.dao.DictDao.insert : ==> Preparing: INSERT INTO t_dict ( dict_id, code, type, value ) VALUES ( ?, ?, ?, ? )
2020-01-19 11:13:13.164 DEBUG 11876 --- [ main] com.lucifer.sharding.dao.DictDao.insert : ==> Parameters: 1218733118769360898(Long), 111(String), 性别(String), 男(String)
2020-01-19 11:13:19.666 INFO 11876 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: INSERT INTO t_order_1 (order_id, price, status, user_id) VALUES (?, ?, ?, ?) ::: [1218733137404653570, 0.1, 0, 425612793970950145]
2020-01-19 11:13:21.182 DEBUG 11876 --- [ main] c.lucifer.sharding.dao.OrderDao.insert : <== Updates: 1
查看数据库发现均回滚了。