Data Structures   «Prev 

Oracle Database locks data structures

Different users can write to the same data structures without harming each other's data or coordinating with each other, because Oracle Database locks data structures automatically. To maximize data availability, Oracle Database locks the minimum amount of data for the minimum amount of time. For more information about the Oracle Database locking mechanism, see Oracle Database Concepts. You rarely must write extra code to prevent problems with multiple users accessing data concurrently. However, if you do need this level of control, you can manually override the Oracle Database default locking mechanisms. For more information about manual data locks, see Oracle Database Concepts.

Overriding Default Locking

By default, Oracle Database locks data structures automatically, which lets different applications write to the same data structures without harming each other's data or coordinating with each other. If you must have exclusive access to data during a transaction, you can override default locking with these SQL statements:
  1. LOCK TABLE, which explicitly locks entire tables.
  2. SELECT with the FOR UPDATE clause (SELECT FOR UPDATE), which explicitly locks specific rows of a table.

LOCK TABLE Statement

The LOCK TABLE statement explicitly locks one or more tables in a specified lock mode so that you can share or deny access to them. The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock. While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table.
A table lock never prevents other users from querying a table, and a query never acquires a table lock. Only if two different transactions try to modify the same row does one transaction wait for the other to complete. The LOCK TABLE statement lets you specify how long to wait for another transaction to complete. Table locks are released when the transaction that acquired them is either committed or rolled back.

One of the features of Oracle is the introduction of objects into the relational database paradigm.
Unfortunately, many DBAs continue to use Oracle as a purely relational database, and are not aware of some of the new object-oriented data features, that can be used to dramatically improve the performance of their systems.
For example, we are going to be learning about the insertion of VARRAYS into Oracle tables. Of course, this is a violation of first normal form, but it can dramatically improve the performance of Oracle queries, because the subordinate tables are no longer required to represent one-to-many relationships.

Represent the one-to-many relationship directly in the master table

Rather than building the one-to-many relationship, we can represent the one-to-many relationship directly in the master table by implementing these repeating groups. This avoids expensive table joins. In addition, we also have the exciting new concept of object IDs. Object IDs can act like pointers to relational database objects, and allow us to directly address (or de-reference), the contents of a row inside another table. The use of object IDs dramatically reduces the need for SQL joins, and we will be taking a look at some ways you can improve the performance of your Oracle databases by using these new object-oriented features.