You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/09/27 06:55:08 UTC
[GitHub] [shardingsphere] hanxu00920 opened a new issue, #21219: Why Local Transactions with Multiple Data Sources is work
hanxu00920 opened a new issue, #21219:
URL: https://github.com/apache/shardingsphere/issues/21219
thank you for reading my question!
## Question
### I used
shardingsphere JDBC version: 5.1.1
springboot version: 2.3.12.RELEASE
### configured
2 data sources
2 tables with only database-strategy
I annotated my service method with @Transactional and @ShardingSphereTransactionType(TransactionType.LOCAL):
```
@Transactional
@ShardingSphereTransactionType(TransactionType.LOCAL)
public void insertDate1() {
TbAddconsign a_add = new TbAddconsign();
a_add.setAcctNo("000059989882");
a_add.setCertNo("1100988789");
a_add.setSignNo("000059989882S1");
a_add.setCertType("00");
a_add.setBusinessTypeCode("01");
tbAddconsignMapper.insert(a_add);
TbChecklist b_add = new TbChecklist();
b_add.setColId("1099991101");
b_add.setColName("test name balabalabalabala balabalabalabala");
tbChecklistMapper.insert(b_add);
}
```
applaction log:
```
2022-09-27T14:29:28,838 DEBUG [d.DataSourceTransactionManager:370 ] Creating new transaction with name [cn.meleehan.service.TbAddMixDBService.insertDate9]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2022-09-27T14:29:28,847 DEBUG [d.DataSourceTransactionManager:263 ] Acquired Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3e4636c3] for JDBC transaction
2022-09-27T14:29:28,850 DEBUG [d.DataSourceTransactionManager:281 ] Switching JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3e4636c3] to manual commit
2022-09-27T14:29:28,862 DEBUG [o.m.s.SqlSessionUtils :49 ] Creating a new SqlSession
2022-09-27T14:29:28,867 DEBUG [o.m.s.SqlSessionUtils :49 ] Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499]
2022-09-27T14:29:28,873 DEBUG [m.s.t.SpringManagedTransaction:49 ] JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3e4636c3] will be managed by Spring
2022-09-27T14:29:29,686 WARN [c.m.a.TestAlgorithm :34 ] value:000059989882S1, mod:1
2022-09-27T14:29:29,737 INFO [ShardingSphere-SQL :74 ] Logic SQL: insert into tb_addconsign (business_type_code, sign_no, acct_no,
cert_type, cert_no)
values (?, ?, ?,
?, ?)
2022-09-27T14:29:29,737 INFO [ShardingSphere-SQL :74 ] SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
2022-09-27T14:29:29,737 INFO [ShardingSphere-SQL :74 ] Actual SQL: ds1 ::: insert into tb_addconsign (business_type_code, sign_no, acct_no,
cert_type, cert_no)
values (?, ?, ?, ?, ?) ::: [01, 000059989882S1, 000059989882, 00, 1100988789]
2022-09-27T14:29:29,767 DEBUG [o.m.s.SqlSessionUtils :49 ] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499]
2022-09-27T14:29:29,768 DEBUG [o.m.s.SqlSessionUtils :49 ] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499] from current transaction
2022-09-27T14:29:29,770 WARN [c.m.a.TestAlgorithm :34 ] value:1099991100, mod:0
2022-09-27T14:29:29,771 INFO [ShardingSphere-SQL :74 ] Logic SQL: insert into tb_checklist (col_id, col_name)
values (?, ?)
2022-09-27T14:29:29,771 INFO [ShardingSphere-SQL :74 ] SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
2022-09-27T14:29:29,771 INFO [ShardingSphere-SQL :74 ] Actual SQL: ds0 ::: insert into tb_checklist (col_id, col_name)
values (?, ?) ::: [1099991100, test name balabalabalabala balabalabalabala]
2022-09-27T14:29:29,779 DEBUG [o.m.s.SqlSessionUtils :49 ] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499]
2022-09-27T14:29:29,824 DEBUG [o.s.j.s.SQLErrorCodesFactory :223 ] Looking up default SQLErrorCodes for DataSource [org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource@22c0344e]
2022-09-27T14:29:29,830 DEBUG [o.s.j.s.SQLErrorCodesFactory :181 ] SQL error codes for 'PostgreSQL' found
2022-09-27T14:29:29,830 DEBUG [o.s.j.s.SQLErrorCodesFactory :267 ] Caching SQL error codes for DataSource [org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource@22c0344e]: database product name is 'PostgreSQL'
2022-09-27T14:29:29,831 DEBUG [rrorCodeSQLExceptionTranslator:289 ] Unable to translate SQLException with SQL state '22001', error code '0, will now try the fallback translator
2022-09-27T14:29:29,831 DEBUG [SQLStateSQLExceptionTranslator:98 ] Extracted SQL state class '22' from value '22001'
2022-09-27T14:29:29,832 DEBUG [o.m.s.SqlSessionUtils :49 ] Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499]
2022-09-27T14:29:29,832 DEBUG [o.m.s.SqlSessionUtils :49 ] Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d641499]
2022-09-27T14:29:29,832 DEBUG [d.DataSourceTransactionManager:833 ] Initiating transaction rollback
2022-09-27T14:29:29,833 DEBUG [d.DataSourceTransactionManager:341 ] Rolling back JDBC transaction on Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3e4636c3]
2022-09-27T14:29:29,839 DEBUG [d.DataSourceTransactionManager:385 ] Releasing JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3e4636c3] after transaction
org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(22)
### The error may exist in file [/Users/hanxu/workspace_idea/test-sharding-jdbc/target/classes/mapper/TbChecklistMapper.xml]
### The error may involve cn.meleehan.dao.TbChecklistMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into tb_checklist (col_id, col_name) values (?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(22)
; ERROR: value too long for type character varying(22); nested exception is org.postgresql.util.PSQLException: ERROR: value too long for type character varying(22)
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy217.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at com.sun.proxy.$Proxy218.insert(Unknown Source)
at cn.meleehan.service.TbAddMixDBService.insertDate9(TbAddMixDBService.java:43)
at cn.meleehan.service.TbAddMixDBService$$FastClassBySpringCGLIB$$a9cb26ab.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.apache.shardingsphere.spring.transaction.TransactionTypeInterceptor.invoke(TransactionTypeInterceptor.java:44)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
at cn.meleehan.service.TbAddMixDBService$$EnhancerBySpringCGLIB$$1b44f0dd.insertDate9(<generated>)
at cn.meleehan.Test2.test9(Test2.java:19)
```
I queried the pg_catalog.pg_stat_activity table of all databases, rollback is successful.
I did not use distributed transactions, why is the first insert transaction rollback?
Also, if I use **Sharding by Hint**, can I use **TransactionType.LOCAL** to control transactions (I guarantee all operations in one data source)?
thanks and waiting for reply!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] hanxu00920 commented on issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
hanxu00920 commented on issue #21219:
URL: https://github.com/apache/shardingsphere/issues/21219#issuecomment-1259165368
> <img alt="image" width="1440" src="https://user-images.githubusercontent.com/41012300/192458531-9821e103-fd01-4667-ac20-e9f2fe47b111.png">
>
> CZ `LocalTransactionManager` works with `ShardingSphereConnection`, which holds multiple `HikariConnectionProxy` for various data sources. These data sources will rollback one by one while `ShardingSphereConnection` is rollbacking.
>
> This is not a bug.
My big brother, if a single-database operation, how can guarantee to use one database link? Is Hint Sharding OK?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] hanxu00920 closed issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
hanxu00920 closed issue #21219: Why Local Transactions with Multiple Data Sources is work
URL: https://github.com/apache/shardingsphere/issues/21219
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] I-Love-China commented on issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
I-Love-China commented on issue #21219:
URL: https://github.com/apache/shardingsphere/issues/21219#issuecomment-1259086356
<img width="1440" alt="image" src="https://user-images.githubusercontent.com/41012300/192458531-9821e103-fd01-4667-ac20-e9f2fe47b111.png">
CZ `LocalTransactionManager` works with `ShardingSphereConnection`, which holds multiple `HikariConnectionProxy` for various data sources. These data sources will rollback one by one while `ShardingSphereConnection` is rollbacking.
This is not a bug.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] hanxu00920 closed issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
hanxu00920 closed issue #21219: Why Local Transactions with Multiple Data Sources is work
URL: https://github.com/apache/shardingsphere/issues/21219
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] hanxu00920 commented on issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
hanxu00920 commented on issue #21219:
URL: https://github.com/apache/shardingsphere/issues/21219#issuecomment-1259248071
> A local transaction will only use one connection in one data source. @hanxu00920
Got it, thanks boss! 👍 @FlyingZC
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] FlyingZC commented on issue #21219: Why Local Transactions with Multiple Data Sources is work
Posted by GitBox <gi...@apache.org>.
FlyingZC commented on issue #21219:
URL: https://github.com/apache/shardingsphere/issues/21219#issuecomment-1259233109
A local transaction will only use one connection in one data source. @hanxu00920
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org