Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE
statements are as follows:
■ The transaction that contains a DML statement acquires exclusive row locks on the
rows modified by the statement. Other transactions cannot update or delete the
locked rows until the locking transaction either commits or rolls back.
■ The transaction that contains a DML statement does not need to acquire row locks
on any rows selected by a subquery or an implicit query, such as a query in a
WHERE clause. A subquery or implicit query in a DML statement is guaranteed to
be consistent as of the start of the query and does not see the effects of the DML
statement it is part of.
■ A query in a transaction can see the changes made by previous DML statements in
the same transaction, but cannot see the changes of other transactions begun after
its own transaction.
■ In addition to the necessary exclusive row locks, a transaction that contains a DML
statement acquires at least a row exclusive table lock on the table that contains the
affected rows. If the containing transaction already holds a share, share row
exclusive, or exclusive table lock for that table, the row exclusive table lock is not
acquired. If the containing transaction already holds a row share table lock, Oracle
automatically converts this lock to a row exclusive table lock.

NSERT,UPDATE,DELETE,及 SELECT ... FOR UPDATE 語句預設獲取的鎖
有以下特點 :
1. 包含 DML 語句的事務需要獲得被其修改的資料行上的排他行級鎖
2. 事務無需獲取 DML 語句內的子查詢或隱式查詢所選擇的行上的行級鎖
3. 事務內的查詢能夠看到本事務內之前執行的 DML 語句對資料的修改,之後的無法看到
4. 事務內的 DML 語句除了需要獲得必要的排他行級鎖外,至少還需獲得包含被修改資料行的表上
的行排他表級鎖 . 如果事務已經獲得了相關表上的行共享表級鎖 ,Oracle 將自動地將此鎖轉換為行排
他表級鎖.

[@more@]