Page 624
Transaction-level read consistency means that all data seen within the same transaction are consistent to the point of time. Oracle ensures transaction-level read consistency by using the SCN mechanism and by acquiring exclusive table and row locks when needed.
Oracle implements read consistency by using the data available in the rollback segments. When a query enters the execution stage, the current SCN is determined. As it starts reading data of data blocks, the SCN of the block is compared with the observed SCN. If the block's SCN has a value greater than SCN at the start of the query, then it means that the data in those blocks have changed after the query has started executing. If this data is read from the data blocks, it is not consistent with the point in time the query started. Oracle uses the rollback segments to reconstruct the data, as rollback segments will always contain the old dataexcept in cases of high update activity on the system where the old data in the rollback segments could potentially be overwritten.
Locking TypesOracle automatically acquires different types of locking, depending on the operation and the resource on which it has to acquire the lock.
There are three classes of Oracle locks:
Data locks (DML locks) are acquired on tables and are used to protect data, or rather ensure integrity of data.
Dictionary locks (DDL locks) are used to protect the structure of objectsfor example, the structural definition of tables and view indexes. A dictionary lock is automatically acquired by Oracle on behalf of any DDL transaction requiring it.
Internal locks and latches protect internal database structures. Latches are discussed in greater detail in the next section.
Distributed locks and Parallel Cache Management (PCM) locks are used in Parallel Server.
Only data locks and internal locks are covered in this chapter because they are more relevant in the day-to-day issues of the database.
Data LocksTable Locks (TM): Table Locks are acquired when a transaction issues the statements in Table 25.1. Table Locks are acquired by Oracle on behalf of the transaction to reserve DML access on the table and to prevent conflicting DDL operations on the table. For example, if a transaction has a Table Lock on a table, then it will prevent any other transaction from acquiring an exclusive DDL lock on the table, which is required to drop or alter the table.
Table 25.1Statements and Table Locks AcquiredStatement | Type | Mode |
INSERT | TM | Row Exclusive(3) (RX) |
UPDATE | TM | Row Exclusive(3) (RX) |
DELETE | TM | Row Exclusive(3) (RX) |
SELECT FOR UPDATE | TM | Row Share(2) (RS) |
LOCK TABLE | TM | Exclusive(6) (X) |
Page 625
Table 25.1 shows the different modes in which Table Locks (TM)are acquired by the RDBMS when specific statements are issued. The Type column has a value such as TM, and the Mode column has a value of 2, 3, or 6. The value TM indicates a Table Lock; the value 2 indicates a row share (RS) lock, 3 indicates a row exclusive (RX) lock, and 6 indicates an exclusive (X) lock. The value TM(3), for example, is the value that would be stored in the v$lock table, against the session that issued the statement, when the corresponding statement is issued, so you need to be familiar with these values. v$lock is the table where Oracle lists the locks currently held by the Oracle server, which is discussed in detail with examples in the section "Analyzing v$lock."
For example, when an Insert statement is issued, the Type column in v$lock will have value TM for the session and the Mode column will have value 3 which means row exclusive (RX) lock. For additional details, refer to the section "Analyzing v$lock." For all statements except the Lock table, there will be two entries in the v$lock table: one corresponding to the Table Lock (TM) and another entry corresponding to the Transaction Lock (TX). For statements such as insert update, deleting a TM lock is acquired only to prevent conflicting DDL operations on the locked objects, which means that when a user is inserting into a table, he or she acquires a TM lock in mode 3 (RX). If another user is trying to drop the same table, it will have to acquire a TM lock in mode 6 (Exclusive). The TM(3)(RX) lock will prevent the TM(6)(X) session from acquiring the lock and the second session will remain waiting. Table 25.2 illustrates lock modes acquired by a session and lock modes permitted.
Table 25.2Locking Modes and Operations PermittedSQL Statement | Table Lock Mode | Permitted Lock Modes |
Select * from tname | none | RS, RX, S, SRX, X |
Insert Into tname | RX | RS, RX |
Update tname | RX | RS*, RX* |
Delete From tname | RX | RS*, RX* |
Select
From tname For Update Of | RS | RS*RX*S*, SRX* |
Lock Table In ROW SHARE MODE | RS | RS, RX, S, SRX |
Lock Table In ROW EXCLUSIVE MODE | RX |
RS, RX |
Lock Table In SHARE MODE | S |
RS, S |
Lock Table In SHARE ROW EXCLUSIVE MODE | SRX |
RS |
continues
Page 626
Table 25.2ContinuedSQL Statement | Table Lock Mode | Permitted Lock Modes |
Lock Table In EXCLUSIVE MODE | X | None |
RS: Row Share | SRX: Share Row Exclusive | |
RX: Row Exclusive | X: Exclusive | |
S: Share |
* If another transaction has already acquired a lock on the row, a wait will occur.
Transaction Locks (TX): This type of lock is acquired when a transaction issues the statements in Table 25.3. Transaction Locks are always acquired at the row level. TX Locks exclusively lock the rows and prevent other transactions from modifying the row until the transaction holding the lock rolls back or commits the data.
Table 25.3StatementsStatement | Type | Mode |
INSERT | TX | Exclusive(6) (X) |
UPDATE | TX | Exclusive(6) (X) |
DELETE | TX | Exclusive(6) (X) |
SELECT FOR UPDATE | TX | Exclusive(6) (X) |
For TX locks to be acquired, the transaction must first acquire a TM lock on the table. For example, when an Insert statement is issued (refer to Table 25.1), a TM lock in mode 3(RX) has to be acquired. After the TM lock in RX mode is acquired, the transaction will have to acquire a TX in exclusive (X) mode (refer to Table 25.3). The TX lock will be prevented from being acquired only if another transaction has a TX lock on the same row, and the TM lock will be prevented if there is already a TM lock in exclusive (X) Mode on the table.
The other modes are 4 (Share Mode) and 5 (Share Row Exclusive), but these locking modes do not occur commonly on the database and hence do not merit much discussion.
The Share Mode (4) lock is acquired when a user issues the following statement:
Lock table <table name> in SHARE mode;
and the Share Row Exclusive lock is acquired when a user issues
Lock table <table name> in SHARE ROW EXCLUSIVE MODE;
Very few applications actually have to use these statements; therefore, they are not considered really important when compared to other day-to-day, more frequently occurring locking types.