防止在oracle数据库中两次插入相同的值

问题描述：

I have a number which is in this form : 2012-01 (2012 as current year) and 01 is just a the maximum value of a field in my database incremented by 1, and each year that number is reset to 0.

but if there are two users that try to do the same operation at the same time the value is the same for both and thus i get the same number inserted twice in my database .

I thought of creating a sequence but that requires a job that resets the sequence each year and i would prefer if there is a way to make a lock before i get the next number and release it after an insert is done ?

Thanks.

我有一个以这种形式的数字：2012-01（2012年为当年），01只是一个数据库中字段的最大值递增1，并且每年将该数字重置为0. p>

但是如果有两个用户尝试在同一个操作中执行相同的操作这两个值的时间相同，因此我在数据库中插入两次相同的数字。 p>

我想创建一个序列，但这需要一个每年重置序列的作业我希望如果有一种方法可以在我获得下一个数字之前锁定它并在插入完成后立即发布它吗？ p>

谢谢。 p> div >

答

You don't specify where you store the field that is used as the counter. But maybe it is possible to use a SELECT FOR UPDATE statement.

Before you increment the value of your counter field by 1 you can lock that record by using a SELECT FOR UPDATE. Then update the counter.

Something like this, assuming the table has only 1 record:

SELECT *  
FROM   CounterTable
FOR UPDATE;

UPDATE CounterTable
SET    Counter = Counter + 1;

COMMIT;

If one session (user) has done the SELECT FOR UPDATE and not yet committed or rolled back, the other session (user) doing a SELECT FOR UPDATE will block waiting to be able to get a lock. This prevents two users from getting the same number.

答

CREATE UNIQUE INDEX index_name ON table_name (column_name);

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

防止在oracle数据库中两次插入相同的值

相关推荐