Explicit (Manual) Data Locking (364)

Oracle always performs locking automatically to ensure data concurrency, data
integrity, and statement-level read consistency. However, you can override the Oracle
default locking mechanisms. Overriding the default locking is useful in situations such
as these:
■ Applications require transaction-level read consistency or repeatable reads. In
other words, queries in them must produce consistent data for the duration of the
transaction, not reflecting changes by other transactions. You can achieve
transaction-level read consistency by using explicit locking, read-only transactions,
serializable transactions, or by overriding default locking.
■ Applications require that a transaction have exclusive access to a resource so that
the transaction does not have to wait for other transactions to complete.

Oracle’s automatic locking can be overridden at the transaction level or the session
level.

At the transaction level, transactions that include the following SQL statements
override Oracle’s default locking:
■ The SET TRANSACTION ISOLATION LEVEL statement
■ The LOCK TABLE statement (which locks either a table or, when used with views,
the underlying base tables)
■ The SELECT ... FOR UPDATE statement
Locks acquired by these statements are released after the transaction commits or rolls
back.
At the session level, a session can set the required transaction isolation level with the
ALTER SESSION statement.

Note: If Oracle’s default locking is overridden at any level, the
database administrator or application developer should ensure that
the overriding locking procedures operate correctly. The locking
procedures must satisfy the following criteria: data integrity is
guaranteed, data concurrency is acceptable, and deadlocks are not
possible or are appropriately handled.

顯示(手工)加鎖
在以下情況下手工加鎖(覆蓋Oracle預設的鎖機制)非常有用
1. 應用需要事務級別的一致性讀或者可重複讀. 使用者可以顯示地對資料加鎖，使用只讀事務
或序列化事務，或覆蓋預設的鎖，從而實現事務級的讀一致性
2. 應用要求事務必須以排他地訪問某種資源，而不需要等待其他事務完成
3. 使用者可以在事務級或會話級覆蓋 Oracle 預設採用的鎖
4. 在事務級別 , 可以使用下面的語句來覆蓋Oracle預設的鎖 ... , 這些鎖在事務提交或回滾後
釋放
5. 在會話級別,可以使用alter session 設定事務隔離級別
6. 手工加鎖操作必須滿足以下標準：保證資料完整性，提供足夠的資料併發能力，
不會發生死鎖或能夠正確處理死鎖

[@more@]