考虑这么一个场景:等待处理的任务加入数据库某个表中,表的PROCESSED_FLAG列只有两个值:Y 和N。对于插入到表中的记录,该列值为N(表示未处理)。其他进程读取和处理这个记录时,就会把该列值从N更新为Y。
位图索引适用于低基数(low-cardinality)列,所谓低基数列就是指这个列只有很少的可取值。但是这里并不适合。采用位图索引,一个键指向多行,可能数以百计甚至更多。如果更新一个位图索引键,那么这个键指向的数百条记录会与你实际更新的那一行一同被有效地锁定。
可以在函数decode(process_flag, 'N', 'N')上创建一个基于函数的B*Tree索引,从而返回N或者NULL,利用完全为NULL的键不会放入B*Tree索引的特性,只在处理标志为N的记录上创建一个索引。
为了能够多个会话并行处理任务,在Oracle11g R1之后,可以利用SKIP LOCKED特性。它允许多个会话并发查找第一个未锁定,未处理的记录,然后锁定该记录进行处理。
下面举例说明。
创建表格,索引,添加数据:
现在表格T中的数据如下:
tony@ORA11GR2> select * from t;
ID PR PAYLOAD
---------- -- ----------------------------------------
1 Y payload 1
2 N payload 2
3 Y payload 3
4 N payload 4
5 Y payload 5
在2个会话中分别执行如下的过程块,
在第一个会话中,得到结果: I got row 2, payload 2
在第二个会话中,得到结果: I got row 4, payload 4
在Oracle11g R1之前,可以利用如下办法:
在2个会话中分别执行如下过程,也可以得到相同结果:
*如果使用高级排队(Advanced Queuing)并调用DBMS_AQ.DEQUEUE,解决方案会更容易。
分享到:
相关推荐
We report a passively Q-switched and mode-locked erbium-doped fiber laser (EDFL) based on PtSe2, a new two-dimensional material, as a saturable absorber (SA). Self-started Q-switching at 1560 nm in ...
SVN在客户端执行UPDATE报locked的处理办法,这里报错是提示工作拷贝“D:\svn_repository\cmout\cm\SVN\SVN客户端\TortoiseSVN\download”锁住了
最近在操作oracle11g的使用出现错误的现象:ora-28000 the account is locked,既用户无法登录问题,如何解决此问题呢?下面小编给大家带来了oracle11g用户登录时被锁定问题的解决方法,感兴趣的朋友一起看看吧
解析oracle对select加锁的方法以及锁的查询一、oracle对select加锁方法 代码如下:create table...—session 1 模拟选中一个号码SQL> select * from test where a =1 for update skip locked; A B———- ———- 1
We experimentally demonstrate a diode-pumped passively mode-locked femtosecond laser with Yb3+-doped yttrium lanthanum oxide ceramic. Mode-locking is achieved by using a semiconductor saturable ...
<br> select * from v$locked_object ; <br> 15. 如何以archivelog的方式运行oracle。 <br> init.ora <br> log_archive_start = true <br> RESTART DATABASE <br> 16. 怎幺获取有哪些用户...
CD-Rom for the book "Phase-Locked Loops Design,Simulation and Applications by R.E.Best. A design program for Phase-locked loops circuits
A dual-wavelength synchronously mode-locked homogeneously broadened bulk laser operating at 1985.6 and 1989 nm is presented for the first time, to the best of our knowledge, which delivers a maximum ...
We experimentally demonstrate for the first time, to the best of our knowledge, an all-fiber passively mode-locked laser operation based on the nonlinear multimode interference of step-index multimode...
基于oracle数据库开发的应用,在发生锁情况下,如何检查锁和解锁
oracle清除死锁
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。 有主外键约束时 update / delete ... ; 可能会产生4,5的锁。 DDL语句时是6的锁。 以DBA...
Oracle; the account is locked
Oracle锁表常用sql语句: select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ...select * from tc_daily_quotation for update; select * from tc_Pre_Contract_Bail for update;
We report Q-switched and mode-locked erbium-doped all-fiber lasers using ternary ReS2(1?x)Se2x as saturable absorbers (SAs). The modulation depth and saturable intensity of the film SA are 1.8% and 0....
We demonstrate a stable conventional soliton in a Tm-doped hybrid mode-locked fiber laser by employing a homemade all-fiber Lyot filter (AFLF) and a single-wall carbon nanotube. The AFLF, designed by ...
80-GHzColliding-Pulse Mode-Locked laser with High Pulse Power
锁相环经典书籍,对于想了解无线电,激光器锁相或许有帮助。
Er-doped all-fiber laser mode-locked by graphitic carbon nitride nanosheets