1. SecureFiles and Large Objects Developer's Guide
  2. Advanced Design Considerations
  3. LOB Locators and Transaction Boundaries

12.2 LOB Locators and Transaction Boundaries

LOB locators can be used in both transactions as well as transaction IDs.

See Also:

Locator Interface for LOBs for more information about LOB locators

12.2.1 About LOB Locators and Transaction Boundaries

Learn about LOB locators and transaction boundaries in this section.

Note the following regarding LOB locators and transactions:

  • Locators contain transaction IDs when:

    You Begin the Transaction, Then Select Locator: If you begin a transaction and subsequently select a locator, then the locator contains the transaction ID. Note that you can implicitly be in a transaction without explicitly beginning one. For example, SELECT... FOR UPDATE implicitly begins a transaction. In such a case, the locator contains a transaction ID.

  • Locators Do Not Contain Transaction IDs When...

    • You are Outside the Transaction, Then Select Locator: By contrast, if you select a locator outside of a transaction, then the locator does not contain a transaction ID.

    • When Selected Prior to DML Statement Execution: A transaction ID is not assigned until the first DML statement executes. Therefore, locators that are selected prior to such a DML statement do not contain a transaction ID.

12.2.2 Read and Write Operations on a LOB Using Locators

You can always read LOB data using the locator irrespective of whether or not the locator contains a transaction ID. Learn about various aspects of it in this section.

  • Cannot Write Using Locator:

    If the locator contains a transaction ID, then you cannot write to the LOB outside of that particular transaction.

  • Can Write Using Locator:

    If the locator does not contain a transaction ID, then you can write to the LOB after beginning a transaction either explicitly or implicitly.

  • Cannot Read or Write Using Locator With Serializable Transactions:

    If the locator contains a transaction ID of an older transaction, and the current transaction is serializable, then you cannot read or write using that locator.

  • Can Read, Not Write Using Locator With Non-Serializable Transactions:

    If the transaction is non-serializable, then you can read, but not write outside of that transaction.

The examples Selecting the Locator Outside of the Transaction Boundary, Selecting the Locator Within a Transaction Boundary, LOB Locators Cannot Span Transactions, and Example of Locator Not Spanning a Transaction show the relationship between locators and non-serializable transactions

12.2.3 Selecting the Locator Outside of the Transaction Boundary

This section has two scenarios that describe techniques for using locators in non-serializable transactions when the locator is selected outside of a transaction.

First Scenario:

  1. Select the locator with no current transaction. At this point, the locator does not contain a transaction id.

  2. Begin the transaction.

  3. Use the locator to read data from the LOB.

  4. Commit or rollback the transaction.

  5. Use the locator to read data from the LOB.

  6. Begin a transaction. The locator does not contain a transaction id.

  7. Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id.

Second Scenario:

  1. Select the locator with no current transaction. At this point, the locator does not contain a transaction id.
  2. Begin the transaction. The locator does not contain a transaction id.
  3. Use the locator to read data from the LOB. The locator does not contain a transaction id.
  4. Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id. You can continue to read from or write to the LOB.
  5. Commit or rollback the transaction. The locator continues to contain the transaction id.
  6. Use the locator to read data from the LOB. This is a valid operation.
  7. Begin a transaction. The locator contains the previous transaction id.
  8. Use the locator to write data to the LOB. This write operation fails because the locator does not contain the transaction id that matches the current transaction.

12.2.4 Selecting the Locator Within a Transaction Boundary

This section has two scenarios that describe techniques for using locators in non-serializable transactions when the locator is selected within a transaction.

First Scenario:

  1. Select the locator within a transaction. At this point, the locator contains the transaction id.

  2. Begin the transaction. The locator contains the previous transaction id.

  3. Use the locator to read data from the LOB. This operation is valid even though the transaction id in the locator does not match the current transaction.

    See Also:

    "Read-Consistent Locators" for more information about using the locator to read LOB data.

  4. Use the locator to write data to the LOB. This operation fails because the transaction id in the locator does not match the current transaction.

Second Scenario:

  1. Begin a transaction.
  2. Select the locator. The locator contains the transaction id because it was selected within a transaction.
  3. Use the locator to read from or write to the LOB. These operations are valid.
  4. Commit or rollback the transaction. The locator continues to contain the transaction id.
  5. Use the locator to read data from the LOB. This operation is valid even though there is a transaction id in the locator and the transaction was previously committed or rolled back.
  6. Use the locator to write data to the LOB. This operation fails because the transaction id in the locator is for a transaction that was previously committed or rolled back.

12.2.5 LOB Locators Cannot Span Transactions

LOB locators that are used to write data cannot span transactions. However, the locator can be used to read the LOB value unless you are in a serializable transaction.

Modifying a persistent LOB value through the LOB locator using DBMS_LOB, OCI, or SQL INSERT or UPDATE statements changes the locator from a read-consistent locator to an updated locator.

The INSERT or UPDATE statement automatically starts a transaction and locks the row. Once this has occurred, the locator cannot be used outside the current transaction to modify the LOB value. In other words, LOB locators that are used to write data cannot span transactions. However, the locator can be used to read the LOB value unless you are in a serializable transaction.

In the following code example, a CLOB locator called clob_updated is created and following operations are performed:

  • At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_updated.

  • The second operation (at t2), uses the DBMS_LOB.WRITE function to alter the value in clob_updated, and a DBMS_LOB.READ reveals a new value.

  • The commit statement (at t3) ends the current transaction.

  • Therefore (at t4), the subsequent DBMS_LOB.WRITE operation fails because the clob_updated locator refers to a different (already committed) transaction. This is noted by the error returned. You must re-select the LOB locator before using it in further DBMS_LOB (and OCI) modify operations.

12.2.6 Example of Locator Not Spanning a Transaction

The example of locator not spanning a transaction uses the print_media table. 

INSERT INTO PRINT_MEDIA VALUES (2056, 20010, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var          INTEGER;
  clob_updated     CLOB;
  read_amount      INTEGER;
  read_offset      INTEGER;
  write_amount     INTEGER;
  write_offset     INTEGER;
  buffer           VARCHAR2(20);

BEGIN
          -- At time t1:
     SELECT      ad_sourcetext
     INTO        clob_updated
     FROM        PRINT_MEDIA
     WHERE       ad_id = 20010
     FOR UPDATE;
     read_amount := 10;
     read_offset := 1;
     dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
     dbms_output.put_line('clob_updated value: ' || buffer);
     -- This produces the output 'abcd'

     -- At time t2:
     write_amount := 3;
     write_offset := 5;
     buffer := 'efg';
     dbms_lob.write(clob_updated, write_amount, write_offset, buffer);
     read_amount := 10;
     dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
     dbms_output.put_line('clob_updated value: ' || buffer);
     -- This produces the output 'abcdefg'

    -- At time t3:
    COMMIT;

     -- At time t4:
    dbms_lob.write(clob_updated , write_amount, write_offset, buffer);
    -- ERROR: ORA-22990: LOB locators cannot span transactions
END;
/