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:
- Select the locator with no current transaction. At this point, the locator does not contain a transaction id.
- Begin the transaction.
- Use the locator to read data from the LOB.
- Commit or rollback the transaction.
- Use the locator to read data from the LOB.
- Begin a transaction. The locator does not contain a transaction id.
- 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:
- Select the locator with no current transaction. At this point, the locator does not contain a transaction id.
- Begin the transaction. The locator does not contain a transaction id.
- Use the locator to read data from the LOB. The locator does not contain a transaction id.
- 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.
- Commit or rollback the transaction. The locator continues to contain the transaction id.
- Use the locator to read data from the LOB. This is a valid operation.
- Begin a transaction. The locator already contains the previous transaction's id.
- 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:
- Select the locator within a transaction. At this point, the locator contains the transaction id.
- Begin the transaction. The locator contains the previous transaction's id.
- 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.
- 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:
- Begin a transaction.
- Select the locator. The locator contains the transaction id because it was selected within a transaction.
- Use the locator to read from and/or write to the LOB. These operations are valid.
- Commit or rollback the transaction. The locator continues to contain the transaction id.
- 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.
- 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.