“实体"指的是“实体".特定顺序 [英] An "entity" specific sequence
问题描述
背景
我有很多不同的事物"(领域特定的项目/实体/主题)，这些事物对事物"所有者(人类)可见.所有者将用数字标识他们的事物".我要显示一个较小的数字(最好是从1开始的序列)，而不是显示一个较大的随机"数字，这对人类来说比较容易.业主很乐于谈论我的富人37"和她的酒吧128".那里的序列"可以有间隔，但附加的数目必须在事物"实例的生存期内保持不变.因此，我需要一种生成事物" +所有者专有ID(当前称为可见ID")的方法.
I have a lot of different "things" (a domain specific item/entity/subject) that are visible to the "thing" owners (humans). The owners are going to identify their "things" with a number. Instead showing a big "random" number I want to show them a small number (preferably a sequence starting from 1) that is easier for humans. The owners are very comfortable to talk about "my foo 37" and "her bar 128". There "sequence" can have gaps but the attached number have to remain the same during the "thing" intance's lifetime. So I need a way to generate "thing" + owner specific id (currently called as "visible id").
事物" +所有者组合的数量为10k +.当前无法动态生成新的事物"，但所有者可以生成.
The number of "thing" + owner combinations is in scale of 10k+. Currently new "things" can't be dynamically generated but the owners can be.
每个所有者一个事物"实例的数量相对较小，每个所有者大约几十个，但是没有可以从业务规则中得出的硬性上限.经常创建和删除新的事物"实例.
The number of one "thing" instances per owner is a relatively small, about tens per owner, but there is no hard cap that can be derived from the business rules. New "thing" instances are created and deleted frequently.
考虑的选项
我在一个SO问题 Oracle分区序列中找到了很好的讨论，该问题几乎解决了我所遇到的同样问题.
I found a good discussion in a SO question Oracle Partitioned Sequence that addresses pretty much the same issue I do have.
到目前为止，我已经考虑了以下选项:
So far I have considered the following options:
- 我认为标准数据库序列会很好，但是这需要我动态创建大量事物" +所有者专有的序列，并在插入过程中解析序列名称. (并在所有者离开时删除序列.)我不确定创建大量序列是否是一个好习惯(对我而言，创建10k +数据库对象是一个巨大的数目).
- 我还考虑过臭名昭著的
max(visible_id) + 1，但与此同时我们会遇到正常的并发问题，所以这是不可行的. - 根本不将所有者特定的ID保存到数据库中，而是在选择时使用建议 亚当·穆奇.这是一个很棒的主意，但不幸的是，在事物"实例生存期内，ID必须相同.
- 通过让所有者命名事物"来避免整个问题.但是他们根本不喜欢这个主意-为什么我要打扰，说foo 16太容易了." ！
- I think a standard database sequence would be a perfectly fine but that would require me to dynamically create a big number of "thing" + owner specific sequences and also resolve the sequence name during insert. (And drop the sequences when the owner is gone.) I'm not sure if creating a huge number of sequences is a good practice at all (to me 10k+ database objects is a huge number).
- I also considered notorious
max(visible_id) + 1but we'll run into normal concurrency issues with that, so it's a no-go. - Don't save the owner specific id into the database at all but instead generate it in the select like suggested by Adam Musch. This is a wonderful idea but unfortunately the id needs to be the same during the "thing" instance lifetime.
- Avoid the whole problem by letting the owners name the "thing". But they didn't like the idea at all - "Why I should bother, it's just so easy to say foo 16." !
问题
还有其他方法可以解决此问题，还是应该开始动态创建序列?如果序列是答案，请详细说明可能存在的陷阱(例如DDL中的隐式提交).
Is there some other way to address this issue or should I start creating sequences dynamically ? If the sequences are the answer please elaborate what pitfalls there might be (like implicit commits in DDL).
我对Oracle 11gR2和12c解决方案都感兴趣(如果它们不同).
I'm interested in both Oracle 11gR2 and 12c solutions (if they are different).
用于说明问题的伪代码
create table foo (
id number primary key -- the key for computers
,owner_id number
,visible_id number -- the key for humans
,data_ varchar2(20)
);
create constraint foo_u1 unique foo(owner_id, visible_id);
-- primary key sequence
create sequence foo_id_seq;
insert into foo values(
foo_id_seq.nextval
,1
,1 -- what to put here ?
,'lorem ipsum'
);
insert into foo values(
foo_id_seq.nextval
,2
,1 -- what to put here ?
,'dolor sit amet'
);
select visible_id, data_ from foo where owner = 2 order by visible_id;
推荐答案
由于差距尚可，因此您应该实现选项2"的变体.留出空隙意味着您可以快速完成同步:竞争的会话只需检查并继续进行，而不必等待其他会话是否提交或回滚.
Since gaps are OK, you should implement a variation of "option 2". Allowing gaps means that your synchronization can be done quickly: with competing sessions merely checking and moving on rather than having to wait to see if the others commit or rollback.
如果Oracle提供了INSERT INTO..NOWAIT选项，这将很容易.实际上，我可能会涉及到DBMS_LOCK.这是我对您的API外观的看法.
If Oracle offered an INSERT INTO..NOWAIT option, this would be easy. As things are, I'd probably involve DBMS_LOCK. Here's my take on what your API would look like.
它对您拥有的最大可见ID做出了一些假设，因为您是在原始帖子中做出这些假设的.
It makes some assumptions about the max visible ID you'd have because you made those assumptions in your original post.
CREATE OR REPLACE PACKAGE foo_api AS
PROCEDURE create_foo (p_owner_id NUMBER, p_data VARCHAR2);
END foo_api;
CREATE OR REPLACE PACKAGE BODY foo_api AS
-- We need to call allocate_unique in an autonomous transaction because
-- it commits and the calling program may not want to commit at this time
FUNCTION get_lock_handle (p_owner_id NUMBER, p_visible_id NUMBER)
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_lock_handle VARCHAR2 (128);
BEGIN
DBMS_LOCK.allocate_unique (
lockname => 'INSERT_FOO_' || p_owner_id || '_' || p_visible_id,
lockhandle => l_lock_handle
);
COMMIT;
RETURN l_lock_handle;
END;
PROCEDURE create_foo (p_owner_id NUMBER, p_data VARCHAR2) IS
-- This is the highest visible ID you'd ever want.
c_max_visible_id NUMBER := 1000;
BEGIN
<<id_loop>>
FOR r_available_ids IN (SELECT a.visible_id
FROM (SELECT ROWNUM visible_id
FROM DUAL
CONNECT BY ROWNUM <= c_max_visible_id) a
LEFT JOIN foo
ON foo.owner_id = p_owner_id
AND foo.visible_id = a.visible_id
WHERE foo.visible_id IS NULL) LOOP
-- We found a gap
-- We could try to insert into it. If another session has already done so and
-- committed, we'll get an ORA-00001. If another session has already done so but not
-- yet committed, we'll wait. And waiting is bad.
-- We'd like an INSERT...NO WAIT, but Oracle doesn't provide that.
-- Since this is the official API for creating foos and we have good application
-- design to ensure that foos are not created outside this API, we'll manage
-- the concurrency ourselves.
--
-- Try to acquire a user lock on the key we're going to try an insert.
DECLARE
l_lock_handle VARCHAR2 (128);
l_lock_result NUMBER;
l_seconds_to_wait NUMBER := 21600;
BEGIN
l_lock_handle := get_lock_handle (
p_owner_id => p_owner_id,
p_visible_id => r_available_ids.visible_id
);
l_lock_result := DBMS_LOCK.request (lockhandle => l_lock_handle,
lockmode => DBMS_LOCK.x_mode,
timeout => 0, -- Do not wait
release_on_commit => TRUE);
IF l_lock_result = 1 THEN
-- 1 => Timeout -- this could happen.
-- In this case, we want to move onto the next available ID.
CONTINUE id_loop;
END IF;
IF l_lock_result = 2 THEN
-- 2 => Deadlock (this should never happen, but scream if it does).
raise_application_error (
-20001,
'A deadlock occurred while trying to acquire Foo creation lock for '
|| p_owner_id
|| '_'
|| r_available_ids.visible_id
|| '. This is a programming error.');
END IF;
IF l_lock_result = 3 THEN
-- 3 => Parameter error (this should never happen, but scream if it does).
raise_application_error (
-20001,
'A parameter error occurred while trying to acquire Foo creation lock for '
|| p_owner_id
|| '_'
|| r_available_ids.visible_id
|| '. This is a programming error.');
END IF;
IF l_lock_result = 4 THEN
-- 4 => Already own lock (this should never happen, but scream if it does).
raise_application_error (
-20001,
'Attempted to create a Foo creation lock and found lock already held by session for '
|| p_owner_id
|| '_'
|| r_available_ids.visible_id
|| '. This is a programming error.');
END IF;
IF l_lock_result = 5 THEN
-- 5 => Illegal lock handle (this should never happen, but scream if it does).
raise_application_error (
-20001,
'An illegal lock handle error occurred while trying to acquire Foo creation lock for '
|| p_owner_id
|| '_'
|| r_available_ids.visible_id
|| '. This is a programming error.');
END IF;
END;
-- If we get here, we have an exclusive lock on the owner_id / visible_id
-- combination. Attempt the insert
BEGIN
INSERT INTO foo (id,
owner_id,
visible_id,
data_)
VALUES (foo_id_seq.NEXTVAL,
p_owner_id,
r_available_ids.visible_id,
p_data);
-- If we get here, we are done.
EXIT id_loop;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- Unfortunately, if this happened, we would have waited until the competing
-- session committed or rolled back. But the only way it
-- could have happened if the competing session did not use our API to create
-- or update the foo.
-- TODO: Do something to log or alert a programmer that this has happened,
-- but don't fail.
CONTINUE id_loop;
END;
END LOOP;
END create_foo;
END foo_api;
这篇关于“实体"指的是“实体".特定顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
