FlyingZC
commented
2 years ago Thanks for your feedback,I will investigate it.
Closed congzhou2603 closed 3 months ago
FlyingZC
commented
2 years ago Thanks for your feedback,I will investigate it.
FlyingZC
commented
2 years ago The simle example to reproduce this issue:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static org.junit.Assert.assertEquals;
public class SimpleTest {
public static void main(String[] args) throws Exception {
Connection conn1 = getConnection("localhost", 3307, "root", "root", "sharding_db");
executeWithLog(conn1, "delete from account;");
rollback();
}
public static void rollback() throws Exception {
Connection conn2 = getConnection("localhost", 3307, "root", "root", "sharding_db");
conn2.setAutoCommit(false);
assertAccountRowCount(conn2, 0);
Statement std1 = conn2.createStatement();
std1.execute("insert into account(id, balance, transaction_id) values(1, 1, 1);");
assertAccountRowCount(conn2, 1);
conn2.rollback();
assertAccountRowCount(conn2, 0);
}
private static Connection getConnection(String ip, int port, String user, String pwd, String db) throws SQLException {
return DriverManager.getConnection(String.format("jdbc:opengauss://%s:%d/%s", ip, port, db), user, pwd);
}
protected static void assertAccountRowCount(final Connection conn, final int rowNum) {
assertTableRowCount(conn, "account", rowNum);
}
protected static void assertTableRowCount(final Connection conn, final String tableName, final int rowNum) throws SQLException {
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from " + tableName);
int resultSetCount = 0;
while (rs.next()) {
resultSetCount++;
}
statement.close();
assertEquals(String.format("Recode num assert error, expect: %s, actual: %s.", rowNum, resultSetCount), rowNum, resultSetCount);
}
public static void executeWithLog(final Connection connection, final String sql) {
System.out.println("Connection execute: {}." + sql);
connection.createStatement().execute(sql);
}
}yaml config-sharding:
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:opengauss://localhost:15432/omm
username: gaussdb
password: x
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:opengauss://localhost:15432/postgres
username: gaussdb
password: x
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_item_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
t_order_item_inline:
type: INLINE
props:
algorithm-expression: t_order_item_${order_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKEI don't know if XA is used, even if the transaction is not explicitly opened, the JDBC driver of sql pg/og will open the transaction implicitly by itself (openGauss needs to investigate the logic in the JDBC driver ) @MrCong233 .
If the current connection is already in the transaction, execute setAutocommit(false) to open the transaction.pg/og will not execute the open transaction operation again.
FlyingZC
commented
3 months ago I will close this issue because it has been fixed.
Bug Report
I developed a java program for testing read half commits problem. the program logic is init table first （create table and insert datas, set auoCommit = false）, and then create write and read transactions in a loop.
I find sometimes the JDBC connection created first (not auto commit) and write transaction createed later are automatically merged into the same explicit transaction.
Which version of ShardingSphere did you use?
ShardingSphere 5.2.0
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
Expected behavior
JDBC connection with setAutoCommit(true) should not an implicit transaction， and should not be affected by other JDBC connection.
Actual behavior
according to openGauss' pg_log, the JDBC connection with setAutoCommit(true) (include "drop table, "create table", "insert into" as shown below) merges into the same explicit transaction as a JDBC connection with setAutoCommit(false) (include "update account_0", "select balance", "update account_1").
Reason analyze (If you can)
The JDBC connection created first (not auto commit) and the JDBC connection created later (auto commit) are automatically merged into the same explicit transaction.
Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
This program can occasionally reproduce the problem， will occurs read consistency exception after creating table.
https://gitee.com/congzhou2603/shardingclient
Example codes for reproduce this issue (such as a github link).