Recursive Queries, Maximo Lookups and Deadlocks

There are several scenarios where you have a requirement to present data that cannot be queried through a simple select statement but requires looping through the table as the data resides in a parent child hierarchy in the table. Work order hierarchy is one such example where a work order record has its parent listed in one of its column, and the parent’s record would have its own parent listed in its record and so on. So to find all the child work orders to the leaf level under a work order, looping would be required.

Note: Although ANCESTOR tables maintain the complete ancestor hierarchy but at times they can have incorrect data.

This is typically achieved in a relational database through a recursive query and the traditional way is to use CTE or Common Table Expression which uses the ‘WITH’ statement to construct recursive queries. I will not go into details of how to construct or use CTE, their detailed documentation is readily available online.

Oracle has introduced a much simpler syntax to execute recursive queries – the START WITH, CONNECT BY statement. It has a lot of limitation compared to the traditional CTE method but for simple querying its gets the work done in a lot less effort. The START WITH, CONNECT BY statement can also be used in DB2 by enabling the oracle compatibility mode 08.

When it comes to table domains in Maximo, using the traditional CTE is a challenge since in table domains only the where clause can be specified and querying using CTE has an entirely different syntax so it cannot be specified in the listwhereclause. The START WITH, CONNECT BY on the contrary, has the syntax similar to the traditional select statement and can be used in the table domains to query data. Here is an example of the START WITH, CONNECT BY statement that lists all the child work orders of the work order WO-1990 till the leaf level.

SELECT * FROM WORKORDER
START WITH WONUM = ‘WO-1990’
CONNECT BY PRIOR WONUM = PARENT AND ISTAKS = 0;

A little bit about table domains, when you open a lookup in Maximo which displays data through table domain, it doesn’t fetch the entire result set in one go but only that no. of rows that are displayed on the first page of the lookup. When you click the next page on the lookup, the next set of records is fetched and so on. What that means is if the lookup is configured to display only 10 rows at a time, when the lookup is opened, only first 10 records are fetched from the database, now if the next button on the lookup is clicked, the next 10 records are fetched and so on.

This is where it gets interesting, what I have observed is if you use the START WITH, CONNECT BY statement in a domain, when the lookup for this domain is opened, it holds a lock on the table(row level lock) till the time that lookup is closed. This is not the case with non-recursive queries, they never hold a lock, even complex queries with joins and sub-selects.

This is where the problem lies, when the lookup with the START WITH, CONNECT BY statement is opened in one application and another user or the same user in another application tries to do a transaction (update or delete) on the same set of rows as queried by the lookup, the user doing the transaction is presented with a deadlock error. Overriding the isolation level for the recursive query also doesn’t help.

The image above shows an error presented to the user in a similar scenario but in this case the lookup and the transaction performed was on the LOCHIERARCHY object.

So it is better to avoid using START WITH, CONNECT BY statement in lookups and use alternate solutions like ANCESTOR tables or views to display data. Some of the lookups can be tricky and may require lot of effort to avoid using START WITH, CONNECT BY statement but if there are deadlock issues, it’s worth the effort.

Cheers!