Oracle Lock Modes and Conncurency

These lock modes still hold true for Oracle 19c. Oracle Database supports several lock types and modes to control concurrent access to data, and the categories I have listed,

1. Row Share (RS),
2. Row Exclusive (RX),
3. Share Row Exclusive (SRX),
4. Share (S), and
5. Exclusive (X)

remain consistent in Oracle 19c.
Here is a brief overview of these locks:

1. Row Share (RS): Also called a sub-share table lock, it indicates that a transaction intends to query the table for update but does not intend to change the structure of the table (e.g., adding or dropping a column). This is the least restrictive table lock.
2. Row Exclusive (RX): A row exclusive table lock is slightly more restrictive than Row Share. It indicates that a transaction intends to update, insert, or delete rows in the table but not the table structure. It allows other transactions to query, lock rows, and lock the table in Row Share mode.
3. Share Row Exclusive (SRX): Also called a shared update table lock. It allows transactions to query and modify rows in the table but does not allow other transactions to modify the same table. However, it allows queries from other transactions.
4. Share (S): A share table lock prevents other transactions from modifying the table or obtaining an exclusive lock. It allows other transactions to query the table but not update it.
5. Exclusive (X): This is the most restrictive lock. It prevents any other transaction from accessing the table, either for queries or updates, and locks the entire table.

These lock modes allow Oracle to manage concurrent data access and maintain consistency, which continues to be crucial in Oracle 19c and later versions.

• Row share table locks (RS)
  Row share table 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):
  Row exclusive locks are issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.
• Table share locks (S)
  A table share 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 table1
  IN SHARE MODE
  

  
  has completed.
• Share row exclusive table locks (SRX)
  Share row exclusive table locks are issued with the
  

  LOCK TABLE table1 
  IN SHARE ROW EXCLUSIVE MODE
  

  
  command. This prevents any other tasks from issuing any explicit LOCK TABLE commands until the task has completed, and prevents any row-level locking on the target table.
• Exclusive table locks (X)
  An exclusive table lock is the most restrictive of the table locks and prevents everything except queries against the affected table. Exclusive locks are used when the programmer desires 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;
  

As you will see in a later lesson, the Oracle V$ tables can be queried to gather information about each of these types of locks.

Two init.ora parameters control locking:

1. serializable=false and
2. row_locking=always.

These default values should not be changed except in very rare cases. In the next lesson, we'll take a look at database deadlocks.

The two init.ora parameters mentioned above, serializable and row_locking, have been used in older Oracle versions, but in Oracle 19c, they do not play a role in controlling locking in the same way they might have in the past.

1. serializable=false:
   • Status in Oracle 19c: There is no such serializable parameter in Oracle 19c to control transaction isolation levels directly via init.ora.
   • Explanation: Oracle manages transaction isolation using SET TRANSACTION or ALTER SESSION commands. You can set the isolation level for transactions to either READ COMMITTED (default) or SERIALIZABLE. In Oracle 19c, if you want transactions to operate in a serializable mode, you do so with these SQL statements, not through an init.ora parameter.
   
   The serializable mode provides a higher isolation level by ensuring that a transaction cannot see changes made by other transactions that were committed after the serializable transaction began. This isolation level is typically set at the transaction or session level, not via a system-wide init.ora parameter.
2. row_locking=always:
   • Status in Oracle 19c: This parameter no longer exists in Oracle 19c.
   • Explanation: In older versions of Oracle (before Oracle8), row_locking controlled whether the database used row-level or table-level locking. Setting row_locking=always forced the database to use row-level locking, while row_locking=auto allowed Oracle to choose between row-level and table-level locking.
   
   In modern Oracle versions, including 19c, Oracle always uses row-level locking by default where appropriate, and the row_locking parameter has been deprecated. Locking behavior in Oracle 19c is controlled automatically, with Oracle managing the appropriate lock types (such as row-level or table-level locks) dynamically based on the situation.

Conclusion:

• serializable=false is not an init.ora parameter in Oracle 19c.
• row_locking=always has been deprecated in Oracle versions starting from Oracle8, and Oracle 19c always uses row-level locking where applicable.

Locking in Oracle 19c is primarily managed internally by the database engine, and more advanced controls over isolation and locking are done through SQL statements rather than initialization parameters.

Before going on, click the Exercise link below to complete an exercise on the different lock modes.
Oracle Lock Modes - Exericse