-1

As a typical scenario in any prod environment, we have multiple nodes which fetches and processes items from the database (oracle).

We want to make sure that each node fetches unique set of items from database and acts on it. To make this possible we are looking whether it is possible to update the records status (for e.g., Idle to In-Process), and the same update query returning the records which it updated. In this way every node will act on its own set of records and not interfere with each others' set.

We want to avoid pl/sql due to maintenance reasons. We tried with "select for update", but in few cases it was leading to database locks getting hold up for longer period of time.

Any suggestions on how to achieve this through simple sql or hibernate (since we have hibernate option available as well)?

Sandeep Khantwal
  • 191
  • 1
  • 7
  • This doesn't look like a coding question to me so it might be put on hold but it feels like even if you use some status flag this can't be handled with some simple sql statement. You need to have logic in the application layer to handle lost connections, unprocessed rows etc so that the status flag is reset or updated correctly. – Joakim Danielson Apr 09 '18 at 14:53
  • @JoakimDanielson There are multiple nodes running in production env for load balancing. The **status** column can be used by the nodes so that before processing any item, nodes can first claim it so that other nodes don't pick up the same item and start processing it, which might lead to multiple processing of the same item. – Sandeep Khantwal Apr 10 '18 at 07:22

2 Answers2

0

A couple of thoughts on this. First up in Oracle you can use the RETURNING clause as part of an update statement to return select columns (such as the primary key) from the table being updated into a collection. But this method does require PL/SQL to work since you need to work with collections, although BULK transactions will mitigate some of the drawbacks of using PL/SQL.

Another option would be to add a column to your table where you can indicate which node is processing the record(s), similar to your idea of a status column indicating Idle, or Processing statuses. This one would be NULL for not being handled, or a value uniquely identifying the node or process working on the record.

A little extra research led to this post here on Stack about using Oracles RETURNING INTO statement with Java. It also leads right back to Oracle's own documentation on the subject of Oracles DML Returning feature as supported by Java

Sentinel
  • 6,379
  • 1
  • 18
  • 23
  • @Santinel In one of our module we have the _Node_ column, but we want to get away from this design since in near future we are targeting for cloud. In cloud we would be dynamically spawning/deleting nodes as per the load on the server. We would also like to avoid pl/sql, since there are very few expertise available in our project. Even though if we go about writing pl/sql to solve the current scenarios, there will be maintenance issues in future for changing the business logic. We would like to keep our business login in java/app layer instead of pl/sql. – Sandeep Khantwal Apr 10 '18 at 07:06
  • See edits above. Looks like you can do it in Java without PL/SQL using my first suggestion of the RETURNING INTO clause. – Sentinel Apr 10 '18 at 14:25
  • We tried this one, but this works well if there is only one row returned. If there are multiple rows then it throws error....`java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows` – Sandeep Khantwal Apr 10 '18 at 14:34
  • To return multiple rows you may need to use `RETURNING col, list BULK COLLECT INTO ...` syntax. Key words being `BULK COLLECT` informing oracle that more than one result record is expected. – Sentinel Apr 10 '18 at 17:10
0

Finally we were able to find the solution for our problem. Our problem statement: Claim the top 100 items from the list order by time of their creation. So the logic that we wanted to apply was based on FIFO. So in this case each node will pick-up top 100 items from the database and start processing on it. In this way each node will work on its own set of items, without overlapping on each others path.

We achieved this by creating a TYPE in oracle database, and then used hibernate to claim the items and store the claimed items temporarily in TYPE. Here is the code:

create type TMP_TYPE as table of VARCHAR2(1000);

//Hibernate code

 String query = "BEGIN UPDATE OUR_TABLE SET OUR_TABLE_STATUS = 'IP' WHERE OUR_TABLE_STATUS = 'ID' AND ID_OUR_TABLE IN (SELECT ID_OUR_TABLE FROM (SELECT ID_OUR_TABLE FROM OUR_TABLE ORDER BY AGEING_SINCE ASC ) ) AND ROWNUM < 101 RETURNING UUID BULK COLLECT INTO ?;END;";

 Connection connection = getSession().connection();
 CallableStatement cStmt = connection.prepareCall(query);

 cStmt = connection.prepareCall(query);
 cStmt.registerOutParameter(1, Types.ARRAY, " TMP_TYPE ");
 cStmt.execute();
 String[] updateBulkCollectArr = (String[]) (cStmt.getArray(1).getArray());

`

Got idea from here Oracle Type and Bulk Collect Thanks @Sentinel

Sandeep Khantwal
  • 191
  • 1
  • 7