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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
