第一章、
開發成功的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鎖分類表
| 表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
|