第一章、      开发成功的Oracle应用程序

 

You should do it in a single SQL statement if at all possible.

If you cannot do it in a single SQL Statement, then do it in PL/SQL.

If you cannot do it in PL/SQL, try a Java Stored Procedure.

If you cannot do it in Java, do it in a C external procedure.

If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it...

 这段表明我们操作一个过程, 简单来说,优先选择SQL

 

 

SQL中一些注意

1、不要在MTS下运行长事务

2、使用绑定变量

 

 

 

理解并行控制

1、

Oracle locks data at the row level on modification only. There is no lock escalation to a block

or table level, ever.

Oracle never locks data just to read it. There are no locks placed on rows of data by simple

reads.

A writer of data does not block a reader of data. Let me repeat – reads are not blocked by

writes. This is fundamentally different from almost every other database, where reads are

blocked by writes.

A writer of data is blocked only when another writer of data has already locked the row it was

going after. A reader of data never blocks a writer of data.

所以实际应用时候,注意改变锁的级别!!!

 

2、多版本

主要针对写操作时候,对读操作不阻塞


具体来说: (R-read  W-write)

R/R       不上锁,但是如果两个线程先读再修改,则需要加SS锁(for update)

R/W  W/R   多版本控制,不阻塞读

W/W          使用SX锁,阻塞任何DML操作




DML锁分类表
表1 Oracle的TM锁类型
锁模式 锁描述 解释 SQL操作
0 none

1 NULL Select
2 SS(Row-S) 行级共享锁,其他对象只能查询这些数据行

Select for update、Lock for update、Lock row share

3 SX(Row-X) 行级排它锁,在提交前不允许做DML操作

Insert、 Update、Delete、Lock row share

4 S(Share) 共享锁 Create index、Lock share
5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive
6 X(Exclusive) 排它锁

Alter table、Drop able、Drop index、Truncate table 、Lock exclusive