This is an automated email from the ASF dual-hosted git repository.
jianbin pushed a commit to branch 2.x
in repository https://gitbox.apache.org/repos/asf/incubator-seata.git
The following commit(s) were added to refs/heads/2.x by this push:
new 8c3224f6f9 bugfix: fix SQLServer-related SQL error when using database
of SQLServer (#6493)
8c3224f6f9 is described below
commit 8c3224f6f90e9ddc18058c33fda7a368af181617
Author: TakeActionNow2019 <55011193+takeactionnow2...@users.noreply.github.com>
AuthorDate: Fri Apr 26 11:32:53 2024 +0800
bugfix: fix SQLServer-related SQL error when using database of SQLServer
(#6493)
---
changes/en-us/2.x.md | 6 ++---
changes/zh-cn/2.x.md | 5 ++---
.../org/apache/seata/common/util/PageUtil.java | 4 ++--
.../org/apache/seata/common/util/PageUtilTest.java | 2 +-
.../distributed/lock/BaseDistributedLockSql.java | 3 ++-
.../lock/BaseDistributedLockSqlServer.java} | 21 +++++++----------
.../lock/DistributedLockSqlFactory.java | 26 +++++++++++++++++++---
.../store/db/sql/lock/SqlServerLockStoreSql.java | 11 +++++++++
.../store/db/sql/log/SqlServerLogStoreSqls.java | 11 +++++++++
...tore.db.sql.distributed.lock.DistributedLockSql | 2 ++
script/server/db/sqlserver.sql | 26 +++++++++-------------
11 files changed, 74 insertions(+), 43 deletions(-)
diff --git a/changes/en-us/2.x.md b/changes/en-us/2.x.md
index 2d681fcd54..3951d0eeed 100644
--- a/changes/en-us/2.x.md
+++ b/changes/en-us/2.x.md
@@ -37,9 +37,9 @@ Add changes here for all PR submitted to the 2.x branch.
- [[#6465](https://github.com/apache/incubator-seata/pull/6465)] fix(6257):
fix saga mode replay context lost start in 2.x
- [[#6469](https://github.com/apache/incubator-seata/pull/6469)] fix Error in
insert sql of [lock_table] data table to sqlserver database
- [[#6492](https://github.com/apache/incubator-seata/pull/6492)] fix XA did
not rollback but close when executing a long-running SQL(or deadlock SQL)
+- [[#6493](https://github.com/apache/incubator-seata/pull/6493)] fix
SQLServer-related SQL error in seata server when using database of SQLServer
- [[#6497](https://github.com/apache/incubator-seata/pull/6497)] fix tcc
properties class when autoconfigure
-
### optimize:
- [[#6031](https://github.com/apache/incubator-seata/pull/6031)] add a check
for the existence of the undolog table
- [[#6089](https://github.com/apache/incubator-seata/pull/6089)] modify the
semantics of RaftServerFactory and remove unnecessary singleton
@@ -129,7 +129,6 @@ Add changes here for all PR submitted to the 2.x branch.
- [[#6412](https://github.com/apache/incubator-seata/pull/6412)] optimize core
compatible module
- [[#6429](https://github.com/apache/incubator-seata/pull/6429)] remove
repetitive words
-
### security:
- [[#6069](https://github.com/apache/incubator-seata/pull/6069)] Upgrade Guava
dependencies to fix security vulnerabilities
- [[#6145](https://github.com/apache/incubator-seata/pull/6145)] upgrade
jettison to 1.5.4
@@ -153,7 +152,6 @@ Add changes here for all PR submitted to the 2.x branch.
- [[#6466](https://github.com/apache/incubator-seata/pull/6466)] support redis
integration testing
- [[#6484](https://github.com/apache/incubator-seata/pull/6484)] fix
FileConfigurationTest and MockServerTest fail
-
### refactor:
- [[#6280](https://github.com/apache/incubator-seata/pull/6280)] refactor Saga
designer using diagram-js
@@ -193,6 +191,6 @@ Thanks to these contributors for their code commits. Please
report an unintended
- [deung](https://github.com/deung)
- [tanyaofei](https://github.com/tanyaofei)
- [xjlgod](https://github.com/xjlgod)
-
+- [TakeActionNow2019](https://github.com/TakeActionNow2019)
Also, we receive many valuable issues, questions and advices from our
community. Thanks for you all.
diff --git a/changes/zh-cn/2.x.md b/changes/zh-cn/2.x.md
index 23091b29a0..14fad2b72c 100644
--- a/changes/zh-cn/2.x.md
+++ b/changes/zh-cn/2.x.md
@@ -37,9 +37,9 @@
- [[#6465](https://github.com/apache/incubator-seata/pull/6465)]
修复2.0下saga模式的context replay丢失start问题
- [[#6469](https://github.com/apache/incubator-seata/pull/6469)]
修复在sqlserver数据库下[lock_table]数据表的插入操作sql中存在的错误
- [[#6492](https://github.com/apache/incubator-seata/pull/6492)]
修复XA执行长时间SQL(或死锁SQL)没有完成回滚就释放连接
+- [[#6493](https://github.com/apache/incubator-seata/pull/6493)]
修复当使用数据库为SQLServer时seata server的SQL报错
- [[#6497](https://github.com/apache/incubator-seata/pull/6497)] 修复自动装配时的seata
tcc 配置类
-
### optimize:
- [[#6031](https://github.com/apache/incubator-seata/pull/6031)]
添加undo_log表的存在性校验
- [[#6089](https://github.com/apache/incubator-seata/pull/6089)]
修改RaftServerFactory语义并删除不必要的单例构建
@@ -150,7 +150,6 @@
- [[#6466](https://github.com/apache/incubator-seata/pull/6466)] 支持redis的集成测试
- [[#6484](https://github.com/apache/incubator-seata/pull/6484)]
修复FileConfigurationTest和MockServerTest失败
-
### refactor:
- [[#6280](https://github.com/apache/incubator-seata/pull/6280)]
使用diagram-js重构Saga设计器
@@ -190,6 +189,6 @@
- [deung](https://github.com/deung)
- [tanyaofei](https://github.com/tanyaofei)
- [xjlgod](https://github.com/xjlgod)
-
+- [TakeActionNow2019](https://github.com/TakeActionNow2019)
同时，我们收到了社区反馈的很多有价值的issue和建议，非常感谢大家。
diff --git a/common/src/main/java/org/apache/seata/common/util/PageUtil.java
b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
index 569389a01a..90ca42974c 100644
--- a/common/src/main/java/org/apache/seata/common/util/PageUtil.java
+++ b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
@@ -76,9 +76,9 @@ public class PageUtil {
+ SOURCE_SQL_PLACE_HOLD + ") temp ) where rn between " +
START_PLACE_HOLD + " and " + END_PLACE_HOLD;
/**
- * The constant SQLSERVER_PAGE_TEMPLATE
+ * The constant SQLSERVER_PAGE_TEMPLATE. Currently, it only works for
order-by condition of "ORDER BY gmt_create desc"
*/
- private static final String SQLSERVER_PAGE_TEMPLATE = "select * from
(select temp.*, ROW_NUMBER() OVER(ORDER BY (select NULL)) AS rowId from ("
+ private static final String SQLSERVER_PAGE_TEMPLATE = "select * from
(select temp.*, ROW_NUMBER() OVER(ORDER BY gmt_create desc) AS rowId from ("
+ SOURCE_SQL_PLACE_HOLD + ") temp ) t where t.rowId between " +
START_PLACE_HOLD + " and " + END_PLACE_HOLD;
/**
* check page parm
diff --git
a/common/src/test/java/org/apache/seata/common/util/PageUtilTest.java
b/common/src/test/java/org/apache/seata/common/util/PageUtilTest.java
index f9b9b91b45..5fcdcfd511 100644
--- a/common/src/test/java/org/apache/seata/common/util/PageUtilTest.java
+++ b/common/src/test/java/org/apache/seata/common/util/PageUtilTest.java
@@ -37,7 +37,7 @@ public class PageUtilTest {
String oracleTargetSql = "select * from " +
"( select ROWNUM rn, temp.* from (select * from test where a =
1) temp )" +
" where rn between 1 and 5";
- String sqlserverTargetSql = "select * from (select temp.*,
ROW_NUMBER() OVER(ORDER BY (select NULL)) AS rowId from (select * from test
where a = 1) temp ) t where t.rowId between 1 and 5";
+ String sqlserverTargetSql = "select * from (select temp.*,
ROW_NUMBER() OVER(ORDER BY gmt_create desc) AS rowId from (select * from test
where a = 1) temp ) t where t.rowId between 1 and 5";
assertEquals(PageUtil.pageSql(sourceSql, "mysql", 1, 5),
mysqlTargetSql);
assertEquals(PageUtil.pageSql(sourceSql, "h2", 1, 5), mysqlTargetSql);
diff --git
a/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSql.java
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSql.java
index fde17fbebb..e912f96db3 100644
---
a/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSql.java
+++
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSql.java
@@ -16,9 +16,10 @@
*/
package org.apache.seata.core.store.db.sql.distributed.lock;
+import org.apache.seata.common.loader.LoadLevel;
import org.apache.seata.core.constants.ServerTableColumnsName;
-
+@LoadLevel(name = "default")
public class BaseDistributedLockSql implements DistributedLockSql {
protected static final String DISTRIBUTED_LOCK_TABLE_PLACE_HOLD = "
#distributed_lock_table# ";
diff --git
a/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSqlServer.java
similarity index 59%
copy from
core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
copy to
core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSqlServer.java
index 029c3b0496..72d041df4e 100644
---
a/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
+++
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/BaseDistributedLockSqlServer.java
@@ -14,25 +14,20 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-package org.apache.seata.core.store.db.sql.lock;
+package org.apache.seata.core.store.db.sql.distributed.lock;
import org.apache.seata.common.loader.LoadLevel;
+import org.apache.seata.core.constants.ServerTableColumnsName;
-/**
- * the database lock store ms-sqlserver sql
- *
- */
@LoadLevel(name = "sqlserver")
-public class SqlServerLockStoreSql extends AbstractLockStoreSql {
+public class BaseDistributedLockSqlServer extends BaseDistributedLockSql {
- /**
- * The constant INSERT_LOCK_SQL_MYSQL.
- */
- private static final String INSERT_LOCK_SQL_SQLSERVER = "insert into " +
LOCK_TABLE_PLACE_HOLD + "(" + ALL_COLUMNS + ")"
- + " values (?, ?, ?, ?, ?, ?, ?, SYSDATETIME(), SYSDATETIME(), ?)";
+ protected static final String SELECT_FOR_UPDATE_SQL = "SELECT " +
ALL_COLUMNS + " FROM " + DISTRIBUTED_LOCK_TABLE_PLACE_HOLD
+ + " WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE " +
ServerTableColumnsName.DISTRIBUTED_LOCK_KEY + " = ?";
@Override
- public String getInsertLockSQL(String lockTable) {
- return INSERT_LOCK_SQL_SQLSERVER.replace(LOCK_TABLE_PLACE_HOLD,
lockTable);
+ public String getSelectDistributeForUpdateSql(String distributedLockTable)
{
+ return
SELECT_FOR_UPDATE_SQL.replace(DISTRIBUTED_LOCK_TABLE_PLACE_HOLD,
distributedLockTable);
}
+
}
diff --git
a/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/DistributedLockSqlFactory.java
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/DistributedLockSqlFactory.java
index f6193e8bd0..2072d4631c 100644
---
a/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/DistributedLockSqlFactory.java
+++
b/core/src/main/java/org/apache/seata/core/store/db/sql/distributed/lock/DistributedLockSqlFactory.java
@@ -17,16 +17,36 @@
package org.apache.seata.core.store.db.sql.distributed.lock;
+import org.apache.seata.common.loader.EnhancedServiceLoader;
+import org.apache.seata.common.loader.EnhancedServiceNotFoundException;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.util.Map;
+import java.util.concurrent.ConcurrentHashMap;
+
public class DistributedLockSqlFactory {
- private static final DistributedLockSql DISTRIBUTED_LOCK_SQL = new
BaseDistributedLockSql();
+
+ private static final Logger LOGGER =
LoggerFactory.getLogger(DistributedLockSqlFactory.class);
+
+ protected static Map<String, DistributedLockSql> distributedLockSqlCache =
new ConcurrentHashMap<>(4);
/**
* get the lock store sql
*
- * @param dbType the dbType, support mysql/oracle/h2/postgre/oceanbase/dm,
it's useless now, but maybe useful later
+ * @param dbType the dbType, support
mysql/oracle/h2/postgre/oceanbase/dm/sqlserver ...
* @return lock store sql
*/
public static DistributedLockSql getDistributedLogStoreSql(String dbType) {
- return DISTRIBUTED_LOCK_SQL;
+ return distributedLockSqlCache.computeIfAbsent(dbType, method -> {
+ try {
+ return EnhancedServiceLoader.load(DistributedLockSql.class,
dbType);
+ } catch (EnhancedServiceNotFoundException ex) {
+ if (LOGGER.isDebugEnabled()) {
+ LOGGER.debug("Can't special implementation of
DistributedLockSql for {}", dbType);
+ }
+ }
+ return EnhancedServiceLoader.load(DistributedLockSql.class,
"default");
+ });
}
}
diff --git
a/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
b/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
index 029c3b0496..dc7210c447 100644
---
a/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
+++
b/core/src/main/java/org/apache/seata/core/store/db/sql/lock/SqlServerLockStoreSql.java
@@ -31,8 +31,19 @@ public class SqlServerLockStoreSql extends
AbstractLockStoreSql {
private static final String INSERT_LOCK_SQL_SQLSERVER = "insert into " +
LOCK_TABLE_PLACE_HOLD + "(" + ALL_COLUMNS + ")"
+ " values (?, ?, ?, ?, ?, ?, ?, SYSDATETIME(), SYSDATETIME(), ?)";
+ /**
+ * The constant QUERY_ALL_LOCK.
+ */
+ private static final String QUERY_ALL_LOCK_SQLSERVER = "select " +
ALL_COLUMNS + " from " + LOCK_TABLE_PLACE_HOLD
+ + WHERE_PLACE_HOLD;
+
@Override
public String getInsertLockSQL(String lockTable) {
return INSERT_LOCK_SQL_SQLSERVER.replace(LOCK_TABLE_PLACE_HOLD,
lockTable);
}
+
+ @Override
+ public String getAllLockSql(String lockTable, String whereCondition) {
+ return QUERY_ALL_LOCK_SQLSERVER.replace(LOCK_TABLE_PLACE_HOLD,
lockTable).replace(WHERE_PLACE_HOLD, whereCondition);
+ }
}
diff --git
a/core/src/main/java/org/apache/seata/core/store/db/sql/log/SqlServerLogStoreSqls.java
b/core/src/main/java/org/apache/seata/core/store/db/sql/log/SqlServerLogStoreSqls.java
index a22f982c53..07eae15248 100644
---
a/core/src/main/java/org/apache/seata/core/store/db/sql/log/SqlServerLogStoreSqls.java
+++
b/core/src/main/java/org/apache/seata/core/store/db/sql/log/SqlServerLogStoreSqls.java
@@ -26,6 +26,12 @@ import
org.apache.seata.core.constants.ServerTableColumnsName;
@LoadLevel(name = "sqlserver")
public class SqlServerLogStoreSqls extends AbstractLogStoreSqls {
+ /**
+ * The constant QUERY_ALL_GLOBAL_SESSION_SQLSERVER.
+ */
+ private static final String QUERY_ALL_GLOBAL_SESSION_SQLSERVER = "select "
+ ALL_GLOBAL_COLUMNS + " from "
+ + GLOBAL_TABLE_PLACEHOLD + WHERE_PLACEHOLD;
+
/**
* The constant INSERT_GLOBAL_TRANSACTION_SQLSERVER.
*/
@@ -85,6 +91,11 @@ public class SqlServerLogStoreSqls extends
AbstractLogStoreSqls {
+ " where " + ServerTableColumnsName.BRANCH_TABLE_XID + " = ?"
+ " and " + ServerTableColumnsName.BRANCH_TABLE_BRANCH_ID + " =
?";
+ @Override
+ public String getAllGlobalSessionSql(String globalTable, String
whereCondition) {
+ return
QUERY_ALL_GLOBAL_SESSION_SQLSERVER.replace(GLOBAL_TABLE_PLACEHOLD,
globalTable).replace(WHERE_PLACEHOLD, whereCondition);
+ }
+
@Override
public String getInsertGlobalTransactionSQL(String globalTable) {
return
INSERT_GLOBAL_TRANSACTION_SQLSERVER.replace(GLOBAL_TABLE_PLACEHOLD,
globalTable);
diff --git
a/core/src/main/resources/META-INF/services/org.apache.seata.core.store.db.sql.distributed.lock.DistributedLockSql
b/core/src/main/resources/META-INF/services/org.apache.seata.core.store.db.sql.distributed.lock.DistributedLockSql
new file mode 100644
index 0000000000..f769d558a3
--- /dev/null
+++
b/core/src/main/resources/META-INF/services/org.apache.seata.core.store.db.sql.distributed.lock.DistributedLockSql
@@ -0,0 +1,2 @@
+org.apache.seata.core.store.db.sql.distributed.lock.BaseDistributedLockSql
+org.apache.seata.core.store.db.sql.distributed.lock.BaseDistributedLockSqlServer
diff --git a/script/server/db/sqlserver.sql b/script/server/db/sqlserver.sql
index 70643c4e04..729cc1178e 100644
--- a/script/server/db/sqlserver.sql
+++ b/script/server/db/sqlserver.sql
@@ -103,23 +103,17 @@ CREATE NONCLUSTERED INDEX [idx_branch_id]
)
GO
-CREATE NONCLUSTERED INDEX [idx_xid]
- ON [branch_table] (
- [xid]
- )
-GO
-
+-- the table to store distributed lock constants
CREATE TABLE [distributed_lock]
(
- [lock_key] VARCHAR(20) NOT NULL,
- [lock_value] VARCHAR(20) NOT NULL,
- [expire] bigint,
- PRIMARY KEY CLUSTERED ([lock_key])
- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-)
+ [lock_key] char(20) not null primary key,
+ [lock_value] varchar(20) not null,
+ [expire] bigint
+ )
GO
-INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES
('AsyncCommitting', ' ', 0);
-INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES
('RetryCommitting', ' ', 0);
-INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES
('RetryRollbacking', ' ', 0);
-INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES
('TxTimeoutCheck', ' ', 0);
\ No newline at end of file
+INSERT INTO [distributed_lock] (lock_key, lock_value, expire) VALUES
('AsyncCommitting', ' ', 0);
+INSERT INTO [distributed_lock] (lock_key, lock_value, expire) VALUES
('RetryCommitting', ' ', 0);
+INSERT INTO [distributed_lock] (lock_key, lock_value, expire) VALUES
('RetryRollbacking', ' ', 0);
+INSERT INTO [distributed_lock] (lock_key, lock_value, expire) VALUES
('TxTimeoutCheck', ' ', 0);
+INSERT INTO [distributed_lock] (lock_key, lock_value, expire) VALUES
('UndologDelete', ' ', 0);
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscr...@seata.apache.org
For additional commands, e-mail: notifications-h...@seata.apache.org
