楼主请参考,不知道8i的支不支持下面的功能.LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];
where table_reference_list is a list of one or more table references (identifying either a local
table/view or a remote entity through a database link), and lock_ mode is the mode of the lock,
which can be one of the following:
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already
been locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table
is available (and there is no set limit on how long Oracle will wait). Locking a table never stops
other users from querying or reading the table.
The following LOCK TABLE statements show valid variations:
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
LOCK TABLE emp, dept IN SHARE MODE NOWAIT;
LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE;
where table_reference_list is a list of one or more table references (identifying either a local
table/view or a remote entity through a database link), and lock_ mode is the mode of the lock,
which can be one of the following:
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already
been locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table
is available (and there is no set limit on how long Oracle will wait). Locking a table never stops
other users from querying or reading the table.
The following LOCK TABLE statements show valid variations:
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
LOCK TABLE emp, dept IN SHARE MODE NOWAIT;
LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE;
先update 该号码的记录(比如update一个状态字段,将状态未使用改为已使用),然后select, 再提交。另外的终端也来update和select的时候,就取到原来那条记录了。这样好像可以吧?
另外的终端也来update和select的时候,就取不到原来那条记录了。
不过我觉得应该用程序或芯片组去控制好一些;而且你的程序是用VC写的,根据通信端口的状态,判断如果有用户已经select这个号码,其他户只能等;
可实现的方法是通过一个中间应用程序例如java小的应用的同步取得号码的过程。
如果哪个终端选定了一个号,那么将此记录select for update锁定,不许别人更新,
然后将此字段终端号更新为本客户机的终端号那么其它终端来选此号的时候,想要更新为它的终端号就不行了,因为此号被select for update了
如果前一终端断电,那么事务回退,锁定失效,这个号也就可以再次被其它人发现了这个方案与上一方案的区别在于,这一方案会导致所有号码的表(肯定是一张大表)I/O频繁
这个I/O频繁是没有办法的事情,比较可行的是为每一个字段建立select排他锁。
测试代码如下:
create table test(a number,b number);insert into test values(1,2);
insert into test values(3,4);
insert into test values(8,9);
commit;---session 1 模拟选中一个号码SQL> select * from test where a =1 for update skip locked; A B
---------- ----------
1 2
---session 2 对a=1再进行selectSQL> select * from test where a = 1 for update skip locked;未选定行-- session 3 全表select
SQL> select * from test for update skip locked; A B
---------- ----------
3 4
8 9SQL>
PS:在程序里自已对事务进行控制,不要自动commit了,不然达不到你想要的效果。
sleepzzzz大侠是正确答案,我难通过了