Saving search results in multiple transactions

Question

I have to implement a requirement for a Java CRUD application where users want to keep search results intact, even if they perform actions that affect the criteria by which returned strings are matched.

Confused? OK. Let me give you a familiar example. In Gmail, if you perform an advanced search on unread messages, you are presented with a list of relevant results. Click on an entry, and then return to the search list. It happens that you just read this entry, but it has not disappeared from the original result set. Only this line has changed from bold to normal.

I need to implement the same behavior, but the application is designed in such a way that any transaction is saved first, and then the user interface requests db for synchronization. The complexity of the application and the size of the database prevent me from simply caching the memory of matching lines and making changes to both db and memory.

I am thinking of solving a problem at the database level by creating a staging table in the pointers of the Oracle database to match records and requesting only those records to synchronize the user interface with the data. Any ideas?

Answer 1

In Oracle, if you open a cursor, the results of this cursor are static, regardless of whether another transaction inserts a row that will be displayed in your cursor, or updates or deletes a row that exists in your cursor.

The challenge is not to close the cursor if you want the results to be consistent from the moment the cursor opens.

Answer 2

If the user interface supports one session in the database, one solution is to use global temporary tables in Oracle. When you search, insert unique identifiers in the GTT, then the user interface asks for the GTT.

If the user interface does not close the session, you can do the same, but with a regular table. Then, of course, you just need to add the cleanup code to remove the old search results from the table.

Answer 3

You can use flashback to read data from the past. For example, select * from employee as of timestamp to_timestap('01-MAY-2011 070000', 'DD-MON-YYYY HH24MISS');

Oracle only stores this historical information for a limited period of time. You will need to look into your hold settings; the UNDO_RETENTION parameter, the permanent custodian of the UNDO table space and the correct size, as well as LOB, have their own storage settings.

Answer 4

Create two database connections.

Set the first to READ ONLY (using SET TRANSACTION READ ONLY ), search from that connection, but make sure you never complete this transaction by sending a commit or rollback.

Since a read-only transaction only sees data, as it was at the time the transaction started, the first connection will never see any changes to the database - not even committed.

You can then do your updates in the second connection without affecting the results in the first connection.

If you cannot use two connections, you can implement updates through stored procedures that use autonomous transactions, then you can save the read-only transaction in the only connection that you have.