LOB Locators and Transaction Boundaries

A basic description of LOB locators and their operations is given in Chapter 2, "Basic LOB Components".

This section discusses the use of LOB locators in transactions, and transaction IDs.

• Locators Contain Transaction IDs When....

  You Begin the Transaction, Then Select Locator. If you begin a transaction and then select a locator, 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 will contain 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, the locator does not contain a transaction ID.

  • Locators Do Not Contain Transaction IDs When Selected Prior to DML Statement Execution. A transaction ID will not be assigned until the first DML statement executes. Therefore, locators that are selected prior to such a DML statement will not contain a transaction ID.

Transaction IDs: Reading and Writing to a LOB Using Locators

You can always read the LOB data using the locator irrespective of whether the locator contains a transaction ID.

• Cannot Write Using Locator: If the locator contains a transaction ID, you cannot write to the LOB outside of that particular transaction.

• Can Write Using Locator: If the locator does not contain a transaction ID, 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, you cannot read or write using that locator.

• Can Read, Not Write Using Locator With Non-Serializable Transactions: If the transaction is non-serializable, you can read, but not write outside of that transaction.

The following examples show the relationship between locators and non-serializable transactions

Non-Serializable Example: Selecting the Locator with No Current Transaction

Case 1:

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.

Case 2:

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 and/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 already contains the previous transaction's id.

8. Use the locator to write data to the LOB. This write operation will fail because the locator does not contain the transaction id that matches the current transaction.

Non-Serializable Example: Selecting the Locator within a Transaction

Case 3:

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's 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.

Case 4:

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 and/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's a transaction id in the locator and the transaction was previously committed or rolled back.

   See Also: "Read Consistent Locators" for more information on the using the locator to read LOB data.

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.