非规范化表和重复数据

作者: Ni
发布时间: 2024-01-22 10:55:22 (7月前)
转自：

3 条回复

0#
回复此人
v-star*위위 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> 在我阅读它们时，您的要求是： <UL> <LI> 数据库结构不能改变 </LI> <LI> 两个应用程序必须同时更新完全相同的数据 </LI> <LI> 乐观锁定是因为它可能导致错误或性能下降 </LI> <LI> 悲观锁定出于与乐观锁定相同的原因 </LI> </UL> 看起来最重要的不是你正在改变什么数据，而是你正在读什么数据。您需要一种方法来确定系统用户的数据（我无法判断这些应用程序是仅维护数据还是使用它）。 我假设您当前对提供数据的查询是这样的： <pre> <code> select * from table where date = :1 and hash = :2 </code> </pre> 如果您将此更改为以下内容，那么您将始终选择最新的数据，如果有重复的时间，您将选择第一个应用程序（基本上是随机的 - 更改为您想要的任何顺序） <pre> <code> select * from ( select t.* , rank() over (partition by hash order by date desc, app_id desc) as rnk from table t ) where rnk = 1 </code> </pre> 你可以把它放在一个视图中吗？ 然后，您基本上可以在一个表中运行两个单独的表。您可以使用MERGE等，并可以将DELETE / INSERT语句更改为： <pre> <code> merge into table o using (select :1, :2 ... ) n on ( o.date = n.date and o.hash = n.hash and o.app_id = n.app_id ) when matched then update set ... when not matched then insert (... commit; delete from table where date < :1 and hash = :2 commit; </code> </pre> 您在MERGE语句中使用相同日期和哈希的位置。如果DELETE失败，你不介意 - 因为你已经改变了SELECT查询，所以你不会选择错误的数据。 <HR /> 就个人而言，我承认你的一项要求必须改变。 如果有任何添加其他应用程序的计划，我会接受性能下降并使用排队机制串行执行此表的更新。 如果没有添加其他应用程序的计划，请立即采用简单方法并开始使用锁定策略（不是很好），只处理一些已知错误。 </DIV>

编辑
1#
回复此人
不见你 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> 为了序列化这两个事务，我将创建一个额外的表： <pre> <code> CREATE TABLE locktable( my_date date, my_hash number, primary key (my_date, my_hash) ); </code> </pre> 并将以下面的方式更改整个交易： <pre> <code> 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; </code> </pre> <HR /> 由于现有的主键约束阻止将两个重复记录插入表中，因此第一个INSERT语句将序列化事务。 您可以通过使用两个不同的会话和默认隔离级别READ COMMITED运行简单测试来了解它是如何工作的。 <HR /> 首先，让我们创建测试数据： <pre> <code> 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) ); </code> </pre> <HR /> 的 Sesion＃1 </强> - 看到原始数据。 我们将把记录插入locktable，然后删除旧记录并插入新记录。 <pre> <code> 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. </code> </pre> <HR /> 的 会议＃2 </强> - 此会话未查看会话＃1插入的记录，因为它尚未提交（ <code> somevalue = 111 </code> ）： <pre> <code> 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 ); </code> </pre> 执行INSERT时，会话＃2“挂起”（处于保持状态），因为Oracle检测到表中存在重复记录 <code> locktable </code> 由另一个会话插入，这是尚未完成的。 Oracle现在将等待第一个会话将执行的操作： <UL> <LI> 如果第一个会话将执行COMMIT，则重复错误将是在会话＃2中引发 </LI> <LI> 如果第一个会话将执行ROLLBACK，或者将删除此行并执行COMMIT，则sessin＃2将被取消，并且将插入该行 </LI> </UL> <HR /> 让我们去的 会议＃1 </强> 并做： <pre> <code> SQL> DELETE FROM locktable WHERE my_date = trunc( sysdate) AND my_hash = 123; 1 row deleted. SQL> commit; Commit complete. </code> </pre> <HR /> 现在让我们看看发生了什么的 会议＃2 </强> ： <pre> <code> SQL> INSERT INTO locktable( my_date, my_hash ) VALUES ( trunc( sysdate), 123 ); 1 row created. SQL> </code> </pre> 会议已取消阻止并继续工作。 我们再做一次检查： <pre> <code> 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 </code> </pre> 的 现在会话＃2看到会话＃1提交的更改 </强> ！ 这是因为在 <a href="https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1319" rel="nofollow noreferrer"> 读取提交的隔离级别 </A> ： <BLOCKQUOTE> 在读提交的隔离级别（默认值）中的 一切询问 </强> 由交易执行的 只看到之前提交的数据查询不是交易开始 </强> 。这种隔离程度是适用于少数交易的数据库环境可能会发生冲突。 </BLOCKQUOTE> 即 - 第一个事务被提交，然后第二个事务被解除阻塞，然后第二个事务看到第一个事务所做的更改，尽管事实上第二个事务的开始时间晚于第一个事务。 <HR /> 现在我们可以继续在第二个事务中工作（删除旧数据并插入新数据）。如果另一个（第三个）事务开始（具有相同的日期和哈希），它将再次被搁置，因为 <code> locktable </code> 表。 <HR /> 上述方法将确保正确的序列化的 只有这一笔交易 </强> 。 如果应用程序也在其他位置插入或删除记录，则除非相应地更改其他位置，否则它将无法正常工作。 </DIV>

编辑

登录后才能参与评论