第一章 开发成功的Oracle应用
1、关于位图索引
位图索引适用于低基数(low-cardinality)列。低基数列就是指这个列只有很少的可取值。采用位图索引,一个键指向多行,可能数以百计甚至更多。如果更新一个位图索引,那么这个键所指向的数百条记录也会锁定。所以,如果插入一条记录到表t(表t有被位图索引的列,该列的值只允许Y、N,假设新插入记录对应的该列值为N),就会锁定位图索引中的N键,而这会同时锁定另外数百条为N的记录。此时,想要读取这个表并处理记录的进程就无法将N记录修改为Y记录。原因是,要想把这个列从N更新为Y,需要锁定同一个位图索引键。实际上,想在这个表中插入新记录的其他会话也会阻塞,因为它们同样想对这个位图索引键锁定。下面例子就能证明这一点。
create table t
( processed_flag varchar2(1)
);
create bitmap index
t_idx on t(processed_flag);
insert into t values ( 'N' );
declare
pragma autonomous_transaction;
begin
insert into t values ( 'N' );
commit;
end;
/
执行上面sql,将出现“ORA-00060:等待资源时检测到死锁”,第二个自治事务insert语句被第一个insert语句阻塞。但如果使用两个不同的会话,就不会发生死锁,此时第二个insert会被阻塞并一直等待第一个insert事务提交或回滚。在上面例子中,有两个并发事务,自治事务将在会话中开始一个完全独立于第一个事务的“子事务”。自治事务表现得就好像在一个完全不同的会话中一样,它可以由父事务阻塞,也不会看到父事务做出的尚未提交的修改。
2、并发串行化访问处理方案
现有一张排队表t(有一列processed_flag,值只能为N和Y),需并发串行化实现把该列为N的值更新为Y(记录“出队”)。根据上面对位图索引的描述,需对该列建立一个传统的B*树索引。假设对于标志为Y的列我们不想使用索引,只对值为N的列建立索引。建立基于函数的索引:
t_idx on
t( decode( processed_flag, 'N', 'N' ) );
函数decode( processed_flag, 'N', 'N' )返回N或NULL,因为完全为NULL的键不会放在传统的B*树索引中,这样我们只对值为N的记录建立了索引。
为实现并发串行化处理记录“出队”,我们必须并发查找第一条未锁定、未处理的记录,然后锁定该记录并进行处理。10g及以前版本、11g有不同的方式。
首先创建带有B*树索引的表及插入一些数据:
drop table t purge;
create table t
( id number primary key,
processed_flag varchar2(1),
payload varchar2(20)
);
create index
t_idx on
t( decode( processed_flag, 'N', 'N' ) );
insert into t
select r,
case when mod(r,2) = 0 then 'N' else 'Y' end,
'payload ' || r
from (select level r
from dual
connect by level <= 5)
/
select * from t;
第二步,找到所有未处理的记录,并给它加锁(
注意FOR UPDATE
部分实现加锁功能
),实现并发串行化处理:
10g及以前版本:
下面代码实现加锁功能。
create or replace
function get_first_unlocked_row
return t%rowtype
as
resource_busy exception;
pragma exception_init( resource_busy, -54 );
l_rec t%rowtype;
begin
for x in ( select rowid rid
from t
where decode(processed_flag,'N','N') = 'N')
loop
begin
select * into l_rec
from t
where rowid = x.rid and processed_flag='N'
for update nowait;
return l_rec;
exception
when resource_busy then null;
end;
end loop;
return null;
end;
/
下面代码对上述加锁代码进行测试。现在如果使用两个不同的事务,可以看到,她们会得到不同的记录。另外还可以看到,这个两个事务会并发地得到不同记录(这里再次使用自治事务展示并发问题)。
declare
l_rec t%rowtype;
begin
l_rec := get_first_unlocked_row;
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end;
/
declare
pragma autonomous_transaction;
l_rec t%rowtype;
begin
l_rec := get_first_unlocked_row;
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
commit;
end;
/
上述测试代码输出以下结果:
I got row 2, payload 2
I got row 4, payload 4
11g:11g里新增一个SKIP LOCKED特性,它允许多个会话并发查找第一个未锁定、未处理的记录,然后锁定该记录并进行处理。以下为代码:
declare
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N') = 'N'
FOR UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if ( c%found )
then
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end if;
close c;
end;
/
declare
pragma autonomous_transaction;
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N') = 'N'
FOR UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if ( c%found )
then
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end if;
close c;
commit;
end;
/
书里最后说使用高级排队(Advanced Queuing)并调用dbms_aq.dequeue也能实现并发串行化问题,并且会容易很多,有机会试试。
3、绑定变量
使用绑定变量,能大大减少sql硬解析次数,提高程序性能,防止sql注入攻击。下面列示sql注入攻击的例子。
创建一个过程,用于实现sql注入。
create or replace procedure inj( p_date in date )
as
l_rec all_users%rowtype;
c sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';
dbms_output.put_line( l_query );
open c for l_query;
for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;
/
create table user_pw
( uname varchar2(30) primary key,
pw varchar2(30)
);
insert into user_pw
( uname, pw )
values ( 'TKYTE', 'TOP SECRET' );
commit;
注意下面这条sql,它可以使nls_date_format包含字符串常量。
alter session set nls_date_format = '"''union select tname,0,null from tab--"';
declare
begin
inj(sysdate);
end;
4、小结
(1)要理解Oracle体系结构,知道数据库如何工作,弄清楚它能为你做什么,并最大限度加以利用;
(2)要理解锁定和并发控制特性,而且知道每个数据库都以不同的方式实现这些特性;理解Oracle中的多版本(multi-versioning)、读一致(read-consistent)查询、非阻塞查询;
多版本:指Oracle能够从数据库同时物化多个版本的数据;
读一致查询:对于一个时间点(point in time),查询会产生一致的结果,如打开一个游标(open v_x for select * from t,该游标实现从表t select数据),但并不读取数据,然后delete表t中的数据并commit,然后再访问该游标,此时还会查询表t中在执行delete动作前的数据,这表明该游标在打开的那个时间点就已确定能读取哪些数据;
非阻塞查询:查询不会被写入器(writer)阻塞,但在其他数据库中可能不是这样。
(3)阅读每个Oracle版本的Oracle Database Concepts文档,这是新功能指南,全面介绍了某一特定版本Oracle数据库提供的功能。