oracle存储过程里，如何对数据加锁

Question

oracle 9i数据库表
合同信息表（contract_info）：字段：
合同ID:contract_id,number型主键，序列生成，递增
合同编号-contract_code nvarchar2(20)，格式如XXXX0001，其中XXXX表示n位大写英文字母，0001顺序编号，合同号有唯一约束简单描述下合同号生成规则：
流程开始时，contract_info表中存在记录，但记录的contract_code字段为空，
流程进行到特定节点时，要生成合同号，并把生成的合同号根据contract_id，更新到对应的contract_code字段.生成合同号时,XXXX已知，根据XXXX到contract_info中查找表中已有的合同号的前n位字母也是XXXX，且序号最大的记录
获得后边的序号，然后+1后生成新的四位序号，XXXX与新序号拼接，生成新的合同号，此合同号更新到contract_code字段。
这个合同号的更新过程使用存储过程实现存在一个问题，如果表中有两条合同号为空的记录，这两条记录生成合同号时，由于业务规则相同，其前边的n位英文大写字母即XXXX相同，由于操作业务的人较多，可能同时要查询表中已有的、前n位字母也是XXXX的合同号以获得后四位序号，此时这两条记录生成的合同号可能完全是一样的，而根据业务规则，这是不允许的。怎样才能避免这个问题，需要在存储过程中对表或行加锁吗？
加锁的话如何加？最好能有个差不多的例子，100分感谢！

Answer 1

可以按照某个字段对编号英文相同的排序，比如时间字段，然后根据rownum得到1 2 3...
只要没有中间插入记录就可以了

Answer 2

或者先用一个新的序列写入编号，然后排序后根据rownum改成0001 0002...

Answer 3

你这个规则本身是有漏洞的。
基本上是一个主键生成策略的问题。
这种生成方式在并发访问时不适用。如果号码非要连续的话，等待高手吧。
如果不需要连续，推荐你使用一个序列sequence，使用.nextval来获得下一个值，这样能保证不重复。

Answer 4

如果rownum变更了呢？那不是全部要改？

Answer 5

楼主没理解我的思路先用序列填充，比如：-AAA0011, -AAA0024, -AAA0035
然后排序，顺序肯定是确定的，所以rownum肯定也是确定的，再全部update一下
就可以变成AAA0001, AAA0002, AAA0003

Answer 6

两次update在一个事务里做试试
不行的话只能锁表，可以用select * from table for update

Answer 7

可以考虑自己维护一个序列号表
例如
create table t_seq
(
  sqe_name varchar2(20),
  curvalue number
)
;
使用的时候
 update t_seq
    set curvalue = curvalue +1
  where sqe_name='XXXX'
 returnning into v_value;
这样就确保不会出问题了
  

Answer 8

可以考虑自己维护一个序列号表
例如
create table t_seq
(
  sqe_name varchar2(20),
  curvalue number
)
;使用的时候
update t_seq
    set curvalue = curvalue +1
  where sqe_name='XXXX'
returning curvalue into v_value;这样就确保不会出问题了
  

Answer 9

请参考：http://doc.linuxpk.com/51648.html看看有没有什么帮助

Answer 10

看LZ的描述，其中XXXX表示某一份合同，只不过合同号不同。可不可以这样，再增加一个表。专门存储AAAA,BBBB这样的值(别忘了再加上个contract_id)。     column: XXID , contract_id, XXXX然后contract_info 做外键引用该表ID     column: contract_id,contract_code,XXID,sequence(自己增加 eg. i=i+1)
即使前边的n位英文大写字母即XXXX相同，但不是同一个contract_id。这样就可以避免LZ的问题了吧。
顺嘴胡说，仅供LZ参考

Answer 11

'"此时这两条记录生成的合同号可能完全是一样的，而根据业务规则，这是不允许的。 "
将contract_code这列加上个条件， 不准重复！ 不就可以了吗？ 如果出现你说的问题： 2个客服人员生成了2个一样的“编号”，那么他们更新表的时候，只有最前面的一条数据能更新成功，重复的数据 在存储过程中 exception中处理！   
这样可以吧，给点分吧 LZ，谢谢！

Answer 12

用Java采用Synchronized机制和Singleton模式开发一个Oracle存储过程就可以完全满足楼主的需要。
至于如何用Java开发Oracle存储过程，请见：http://www.developer.com/db/article.php/3337411

Answer 13

create or replace procedure p is
  lockhandle varchar2(200);
  ln         number;
begin
  dbms_lock.allocate_unique('MyLock', lockhandle);
  ln := dbms_lock.request(lockhandle,timeout => 0);
  if ln not in (0, 4) then
    dbms_output.put_line('Already run...');
    return;
  end if;
  dbms_lock.sleep(10);
  ln := dbms_lock.release(lockhandle);
exception
  when others then
    ln := dbms_lock.release(lockhandle);
    raise;
end;

Answer 14

终于把18L提到的那篇文章翻译完了，请参考：
用Java开发Oracle存储过程如果想满足17L的要求，我觉得用Java存储过程(其中的方法用Synchronized约束)就可以了。