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/10/10 06:32:35 UTC
[GitHub] [shardingsphere] bojiw opened a new issue, #20302: Ordinary sql opens the transaction
bojiw opened a new issue, #20302:
URL: https://github.com/apache/shardingsphere/issues/20302
Scenes
within a method query within a transaction update outside of a transaction
The updated sql is not committed
` transactionTemplate.execute(t->{
addressesMapper.getById(1L);
addressesMapper.getById(1L);
return null;
});
ArrayList<Long> ids = new ArrayList<>();
ids.add(15L);
recentItemMapper.updateItemNameByIds(ids,"工作簿1.xlsx");
`
recentItemMapper.updateItemNameByIds
After the code is executed, it returns normally without blocking waiting
but
Re-request update in another place and find that it has been blocked and waited
<img width="880" alt="image" src="https://user-images.githubusercontent.com/22230991/185582378-8fb44fe3-2e7b-4ad4-a324-0fa7b8a04923.png">
sharding-proxy log
`[INFO ] 2022-08-19 16:31:49.421 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT @@session.transaction_read_only
[INFO ] 2022-08-19 16:31:49.661 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select
id,
address, phone,enterprise_id,receiver_name,is_default
from addresses
where id=1
[INFO ] 2022-08-19 16:31:49.662 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-08-19 16:31:49.662 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select
id,
address, phone,enterprise_id,receiver_name,is_default
from addresses
where id=1
[INFO ] 2022-08-19 16:31:53.061 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: SELECT @@session.transaction_read_only
[INFO ] 2022-08-19 16:31:53.063 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-08-19 16:31:53.063 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT @@session.transaction_read_only
[INFO ] 2022-08-19 16:31:53.248 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: UPDATE
recent_items
SET item_name = '工作簿1.xlsx'
WHERE
id IN
(
15
)
[INFO ] 2022-08-19 16:31:53.249 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLUpdateStatement(orderBy=Optional.empty, limit=Optional.empty)
[INFO ] 2022-08-19 16:31:53.249 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE
recent_items
SET item_name = '工作簿1.xlsx'
WHERE
id IN
(
15
)
`
Through arthas, it is found that setAutoCommit will be executed 3 times, and setAutoCommit(false) and setAutoCommit(true) will be executed within the transaction. After the transaction is completed, the update will be executed, and setAutoCommit(false) will be executed again, resulting in the following ordinary sql not submitted.
<img width="736" alt="image" src="https://user-images.githubusercontent.com/22230991/185581621-f0bb22e6-0b33-47a9-a424-9c9ae7afbfa5.png">
environment
spring-boot-2.6.7
mybatis-spring-boot-starter-2.2.2
HikariCP-3.4.2
sharding-proxy-5.1.1
mysql-8.0.29
mysql-connector-java-8.0.11
config
server.yaml
rules:
- !AUTHORITY
users:
- app@:843KewMLpcmac2PtH5wkNnsBHijtft6r
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
max-connections-size-per-query: 2
kernel-executor-size: 2
proxy-frontend-flush-threshold: 128
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: true
check-table-metadata-enabled: false
proxy-frontend-database-protocol-type: MySQL
config-sharding.yaml
schemaName: cloudoffice
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 500000
idleTimeoutMilliseconds: 300000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 8000
minPoolSize: 1
--
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] RaigorJiang closed issue #20302: Ordinary sql opens the transaction
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #20302: Ordinary sql opens the transaction
URL: https://github.com/apache/shardingsphere/issues/20302
--
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] strongduanmu commented on issue #20302: Ordinary sql opens the transaction
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20302:
URL: https://github.com/apache/shardingsphere/issues/20302#issuecomment-1221317044
Can you submit this issue according to issue template? I think a clear description helps to solve the problem quickly.
--
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] RaigorJiang commented on issue #20302: Ordinary sql opens the transaction
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #20302:
URL: https://github.com/apache/shardingsphere/issues/20302#issuecomment-1407557875
This issue has been inactive for a long time, so I'm closing it now.
If this problem persists, please reopen it or submit a new one.
--
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] github-actions[bot] closed issue #20302: Ordinary sql opens the transaction
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #20302: Ordinary sql opens the transaction
URL: https://github.com/apache/shardingsphere/issues/20302
--
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] github-actions[bot] commented on issue #20302: Ordinary sql opens the transaction
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #20302:
URL: https://github.com/apache/shardingsphere/issues/20302#issuecomment-1272348826
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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