java - 非规范化表和重复数据

Question

关闭。这个问题需要更多 focused .它目前不接受答案。












想改进这个问题？更新问题，使其仅关注一个问题 editing this post .

4年前关闭。




Improve this question




我的 Java 项目使用纯 JDBC 与 Oracle DB (v. 12) 交互。事务隔离级别为已提交读。

我有一个高度非规范化的表，它将一个实体存储在一组行中。我无法改变这一点。不幸的是，这张 table 必须保持这种状态，原因与我无关。

+------+------+---------+
| date | hash | ....... |
+------+------+---------+
| date | xyz  | ....... |
| date | xyz  | ....... |
| date | xyz  | ....... |

我有两列标识一个实体——一个日期和一个哈希。由于每个实体都存储为多行，因此这些列并不是真正唯一的，也不是主键，而只是索引列。我仍然想强制执行一种“唯一性”，这意味着当时只存在一个实体，无论它由多少行组成。

这样的实体可以每天更新几次，从而产生不同的值，但也会产生不同的行数。

为了实现这一切，每次更新实体时，我都会在单个事务中执行两个或多个查询:

delete from "table" where "date" = ? and "hash" = ?
insert into "table" values (?, ?, .....)
insert into "table" ....
... -- as many inserts as needed to store whole entity

这适用于单个应用程序实例。不幸的是，我有 2 个实例同时工作，试图存储 完全相同的数据几乎在同一时间(它们只是主备份实例，但备份也在持续存在——这我也没有影响)。

如果这是规范化表，解决方案是使用 MERGE 语句，但在这里不起作用。

我目前的解决方案:

到目前为止，我尝试做的是再添加一列，实例的 ID 持久化，然后使用 SELECT 作为数据源执行 INSERT 语句，并将条件置于 SELECT 中，即此日期/哈希和应用程序 ID 必须没有数据，否则 SELECT 不提供要插入的数据。

我认为它会起作用，但显然它没有。我仍然看到重复。我认为这是因为两个事务首先进行了删除，仍然看不到其他事务尚未提交的数据，因此自己执行插入。然后“提交”是执行和繁荣。两个事务都插入它们的数据。

我考虑过的其他方法:

我想乐观锁定也不起作用，因为在最终版本检查中，两个事务仍然可以认为版本不会被更改，而它们实际上同时被两个事务同时更改并且即将以这种方式提交。

我知道我可以将事务隔离切换到 SERIALIZABLE，但它也不完美(首先，Oracle 驱动程序不会序列化查询，但会采取乐观的方法并在并发修改的情况下失败并出现错误，我不喜欢那样，这是一种“异常编程”范式，一种反模式，那么第二个缺点当然是性能)。

对于这样的问题还有其他解决方案吗？

Answer 1

为了序列化这两个事务，我将创建一个附加表:

CREATE TABLE locktable(
  my_date date,
  my_hash number,
  primary key (my_date, my_hash)
);

并将通过以下方式更改整个交易:

INSERT INTO locktable( my_date, my_hash ) VALUES ( date_value, hash_value );

delete from "table" where "date" = date_value and "hash" = hash_value;
insert ....
insert ....

DELETE FROM locktable WHERE my_date = date_value AND my_hash = hash_value;
COMMIT;

由于现有的主键约束阻止向表中插入两条重复记录，第一个 INSERT 语句将序列化事务。
您可以通过使用两个不同的 session 和默认隔离级别 READ COMMITED 运行一个简单的测试来了解它是如何工作的。

首先，让我们创建测试数据:

CREATE TABLE my_table(
  my_date date,
  my_hash number,
  somevalue int
);

INSERT INTO my_table( my_date, my_hash, somevalue)
SELECT trunc( sysdate ), 123, 111 FROM dual
CONNECT BY level <= 3;
commit;

CREATE TABLE locktable(
  my_date date,
  my_hash number,
  primary key (my_date, my_hash)
);

session #1 - 查看原始数据。
我们将向 locktable 中插入一条记录，然后删除旧记录并插入新记录。

SQL> select * from my_table;

MY_DATE      MY_HASH  SOMEVALUE
--------- ---------- ----------
01-JAN-18        123        111
01-JAN-18        123        111
01-JAN-18        123        111

SQL> INSERT INTO locktable( my_date, my_hash ) VALUES ( trunc( sysdate), 123 );

1 row created.

SQL> DELETE FROM my_table WHERE my_date = trunc( sysdate ) AND my_hash = 123;

3 rows deleted.

SQL> INSERT INTO my_table( my_date, my_hash, somevalue)
  2  SELECT trunc( sysdate ), 123, 222 FROM dual CONNECT BY level <= 3;

3 rows created.

session #2 - 此 session 看不到 session #1 插入的记录，因为它尚未提交 ( somevalue = 111):

SQL> select * from my_table;

MY_DATE      MY_HASH  SOMEVALUE
--------- ---------- ----------
01-JAN-18        123        111
01-JAN-18        123        111
01-JAN-18        123        111

SQL> INSERT INTO locktable( my_date, my_hash ) VALUES ( trunc( sysdate), 123 );

当执行 INSERT 时， session #2 “挂起”(处于保持状态)，因为 Oracle 检测到表 locktable 中有重复记录由另一个尚未提交的 session 插入。
Oracle 现在将等待第一个 session 将执行的操作:

如果第一个 session 将执行 COMMIT，则重复错误将是
在 session #2

中抛出

如果第一个 session 将执行 ROLLBACK，或者将删除该行并执行 COMMIT，则 sessin #2 将被解锁并插入该行

我们去 session #1 并做:

SQL> DELETE FROM  locktable WHERE my_date = trunc( sysdate) AND my_hash = 123;

1 row deleted.

SQL> commit;

Commit complete.

现在让我们看看 中发生了什么 session #2 :

SQL> INSERT INTO locktable( my_date, my_hash ) VALUES ( trunc( sysdate), 123 );

1 row created.

SQL>

session 已解除阻塞并继续工作。
让我们再做一次检查:

SQL> select * from my_table;

MY_DATE      MY_HASH  SOMEVALUE
--------- ---------- ----------
01-JAN-18        123        222
01-JAN-18        123        222
01-JAN-18        123        222

现在 session #2 看到 session #1 提交的更改!!!
这是因为在 Read Committed Isolation Level :

In the read committed isolation level, which is the default, every query executed by a transaction sees only data committed before the query—not the transaction—began. This level of isolation is appropriate for database environments in which few transactions are likely to conflict.

也就是说 - 第一个事务提交，然后第二个事务被解除阻塞，然后第二个事务看到第一个事务所做的更改，尽管第二个事务比第一个事务开始晚。

现在我们可以继续处理第二个事务(删除旧数据并插入新数据)。如果另一个(第三个)事务开始(具有相同的日期和哈希)，由于 locktable 中的现有记录，它将再次被搁置。 table 。

上述方法将确保 的正确序列化只有这一笔交易 .
如果应用程序也在其他地方插入或删除记录，除非其他地方相应更改，否则它将无法正常工作。