User Scott has updated the salary of one of the employees in the EMPLOYEES table and has not
committed the transaction. What are the two types of locks that this scenario would lead to?
(Choose two.)
A.
null lock on the row being updated
B.
null lock on the table containing the row
C.
ROW SHARE lock for the row being updated
D.
ROW EXCLUSIVE lock for the row being updated
E.
shared row-exclusive lock for the row being updated
F.
a shareable table lock for the table containing the row
G.
exclusive table-level lock for the table containing the row
Oracle maintains locks at either the row level or the table level. Unlike other databases, such as DB2, Oracle will never “escalate” locks to the table level if the database detects that a majority of the rows in a table are being locked. Consequently, the Oracle programmer must decide in advance whether to lock the entire table or allow each row of the table to be locked individually.
Two init.ora parameters control locking: serializable=false and row_locking=always. These default values should never be changed except in very rare cases.
Oracle supports two types of locks: row locks and table locks. These locks can be subdivided into several categories:
Row Share Table Locks (RS)—These locks are issued when an SQL transaction has declared its intent to update the table in row share mode. This type of lock will allow other queries to update rows in the customer table. For example:
lock table customer in row share mode;
SELECT customer_name
FROM CUSTOMER
FOR UPDATE OF CUSTOMER;
Row Exclusive Table Locks (RX)—These locks are issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.
Table Share Locks (S)—This type of lock is issued when the LOCK TABLE command is issued against the table. This indicates that the transaction intends to perform updates against some rows in the table, and prevents any other tasks from execution until the LOCK TABLE xxx IN SHARE MODE has completed.
Share Row Exclusive Table Locks (SRX)—These locks are issued with the LOCK TABLE xxx IN SHARE ROW EXCLUSIVE MODE command. This prevents any other tasks from issuing any explicit LOCK TABLE commands until the task has completed, and also prevents any row-level locking on the target table.
Exclusive Table Locks (X)—This is the most restrictive of the table locks and prevents everything except queries against the affected table. Exclusive locks are used when the programmer desired exclusive control over a set of rows until their operation has completed. The following command is used to lock the CUSTOMER table for the duration of the task:
LOCK TABLE CUSTOMER IN ROW EXCLUSIVE MODE NOWAIT;
Row exclusive
Table share