Consistent Read of the Data in Oracle Queries

In some cases, you might want different queries to return same set of data as far as the where criteria defined on the query are the same. For e.g.

Select price from vehicle_price where status=’A’

Select sum(price) from vehicle_price where status=’A’

In the same transaction, if I try to do these queries, there is a possibility that the second query might give a sum that will not be equal to the sum added up by the price returned in the first query. This is possible if new records get added to the vehicle_price table with status as ‘A’ or any of the existing record status changes from ‘A’ to something else.

To ensure that you always get the same data, there are multiple ways to get this done

1. Lock the table and perform queries on it. This ensures that no one else can modify the data while your transaction is in progress. I wont be surprised if you get shouted by other folks who uses this table in some other part of the application.

2. Make the data time sensitive. For every record that gets into the table, there should be a effective start time and effective end time. The query should always query on these columns. So the row doesn’t get modified but a new row gets added with new status. As long as the query uses the effective start date and effective end date in the query, the data returned in subsequent queries will be same. This is a better approach as other users are not impacted. But there could be size and performance implications.

3. Use Serializable isolation level for the transaction which queries the tables. This ensures that all queries are made to wait serially. So till the read transaction doesn’t get over, the new transaction cannot insert data. This also impacts the table usage.

4. Use Transaction read only feature. There are few limitations in using this feature of transaction. This level can be set only for transactions that have only read statements. Oracle maintains a snapshot of the data and all queries are worked upon the snapshot effective the transaction start time. This should be used if its viable to be. A commit or rollback on this transaction will cause the flag to be turned off. Set Transaction Read only ; is the command.

These are few of the ways by which we can ensure that queries return the same data within a same transaction.