Oracle(PL/SQL):UPDATE RETURNING是否并发? [英] Oracle (PL/SQL): Is UPDATE RETURNING concurrent?

问题描述

我正在使用带有计数器的表，以确保子元素上的唯一ID.

I'm using table with a counter to ensure unique id's on a child element.

我知道通常使用序列会更好，但是我不能使用它，因为我有很多计数器(客户可以创建几个存储桶，每个存储桶都需要有自己的计数器，以1开头(这是必需条件，我的客户需要人类可读"的密钥).

I know it is usually better to use a sequence, but I can't use it because I have a lot of counters (a customer can create a couple of buckets and each of them needs to have their own counter, they have to start with 1 (it's a requirement, my customer needs "human readable" keys).

我正在创建具有prikey(bucket_id，num =计数器)的记录(我们称其为项目).

I'm creating records (let's call them items) that have a prikey (bucket_id, num = counter).

我需要保证bucket_id/num组合是唯一的(因此使用序列作为prikey不能解决我的问题).

I need to guarantee that the bucket_id / num combination is unique (so using a sequence as prikey won't fix my problem).

在pl/sql中不会创建行，因此我需要声明该数字(顺便说一句:存在间隙并不违反要求).

The creation of rows doesn't happen in pl/sql, so I need to claim the number (btw: it's not against the requirements to have gaps).

我的解决方法是:

   UPDATE bucket
      SET counter = counter + 1
    WHERE id = param_id
RETURNING counter INTO num_forprikey;

PL/SQL返回var_num_forprikey，以便可以创建项目记录.

PL/SQL returns var_num_forprikey so the item record can be created.

问题:

即使用户同时请求存储桶中的新商品，我也会始终获得唯一的num_forprikey吗?

Will I always get unique num_forprikey even if the user concurrently asks for new items in a bucket?

推荐答案

我总是得到唯一的num_forprikey 即使用户同时要求 桶中有新物品吗?

Will I always get unique num_forprikey even if the user concurrently asks for new items in a bucket?

是的，至少到了一定程度.第一个发布该更新的用户将锁定该行.因此，在用户numero uno提交(或回滚)之前，没有其他用户可以成功发出相同的语句.这样就保证了唯一性.

Yes, at least up to a point. The first user to issue that update gets a lock on the row. So no other user can successfully issue that same statement until user numero uno commits (or rolls back). So uniqueness is guaranteed.

很明显，关键在于并发性.您对该行的访问已序列化，因此两个用户无法同时获取新的PRIKEY.这不一定是问题.这取决于您创建新项目的用户数量以及他们执行此操作的频率.一个用户在同一会话中剥离数字不会引起任何注意.

Obviously, the cavil is regarding concurrency. Your access to the row is serialized, so there is no way for two users to get a new PRIKEY simultaneously. This is not necessarily a problem. It depends on how many users you have creating new Items, and how often they do it. One user peeling off numbers in the same session won't notice a thing.

这篇关于Oracle(PL/SQL):UPDATE RETURNING是否并发?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！