apache / apache/incubator-seata

AT模式在oracle和postgresql中回滚逻辑实现不正确。

Open
#4,727 32 comments 0 reactions 1 assignee Claimed by @slievrly View on GitHub
Dominant language
Java
Stars
26k
Forks
8.8k
Avg merge
1d 8h
Merged PRs (30d)
4

Description

- [ ] I have searched the [issues](https://github.com/seata/seata/issues) of this repository and believe that this is not a duplicate.

### Ⅰ. Issue Description
AT模式在oracle和postgresql中的生成回滚日志实现逻辑是错误的。
Seata的实现逻辑如下:
```sql
SQL1 备份回滚数据:
select * from t_test where name = 'zhang' for update;

SQL2 真正执行的语句:
update t_test set age = 10 where name = 'zhang'

```
由于mysql的锁是通过在索引上加锁实现的,因此在mysql中是正确的。
oracle和postgresql锁是加在行记录上。因此导致实际update数,和select的结果不一致。导致在高并发下不能正确回滚。

正确的实现应该是:
在Oracle和postgreSQL中应在锁定主键id,不应该再次使用条件查询,用如下sql语句。
```sql
用 select …… for update 中查到的id作为修改条件。替换真正要执行的的sql语句。
update t_test set age = 10 where id in (1,2,3)

```

即使在mysql中如果where条件中使用了rand() ,now()之类的可变函数也是有可能导致错误的。因为select 和update的结果不一致。

### Ⅱ. Describe what happened

If there is an exception, please attach the exception trace:

```
Just paste your stack trace here!
```

### Ⅲ. Describe what you expected to happen

在oracle和postgresql中能够正确回滚。
### Ⅳ. How to reproduce it (as minimally and precisely as possible)

1. 创建测试表
```sql
CREATE TABLE t_test (id INT PRIMARY KEY, name VARCHAR(20), age INT );
```
2. 在io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.java 文件中加debug断点。运行到断点处停下。断点见代码注释
如:使用测试SQL: update t_test set age = 10 where name = 'zhang'

```java
protected T executeAutoCommitFalse(Object[] args) throws Exception {
if (!JdbcConstants.MYSQL.equalsIgnoreCase(getDbType()) && isMultiPk()) {
throw new NotSupportYetException("multi pk only support mysql!");
}
TableRecords beforeImage = beforeImage();
T result = statementCallback.execute(statementProxy.getTargetStatement(), args); //在这一行加debug断点。
TableRecords afterImage = afterImage(beforeImage);
prepareUndoLog(beforeImage, afterImage);
return result;
}
```
3. 在数据库中手动插入一条满足where条件的数据, 如 insert into t_test (id, name,age) values(35,'zhang', 21);
4. 继续执行代码,一直到本地事务commit, 查看回滚日志表undo_log。发现回滚日志中数据的记录比实际update执行的记录少一条。

### Ⅴ. Anything else we need to know?

### Ⅵ. Environment:

- JDK version : 1.8
- Seata version: 1.5.1
- OS : windows
- Others: Oracle 和 PostgreSqL

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.