I continue to see problems with Metastorm / SQL Server when updating and viewing data in form grids.  Particularly in large procedures where database call levels can be high.  All Metastorm procedures need to pull data from a SQL server at some point in the workflow (not including native calls to the Metastorm database) and so this is quite an annoyance.  The problem I see surrounds updating, inserting or deleting data from a table and then refreshing the grid to reflect such changes. Database locks appear to occur (in my case on the KEY) resulting in a ‘frozen’ Metastorm form.

To illustrate the problem I create a grid of telephone numbers and look to the table dbo.TelephoneNos. Next I add a text field and a button to the form that will capture the phone number. The text field will capture the number and the button will run the %ExecSQL to insert the telephone information, finally the grid is marked as having dependants so will refresh based on the button press.

The issue occurs when the button is pressed. The sql is executed and the grid will refresh once the button has completed its actions however the INSERT and the SELECT (selecting the data from dbo.TelephoneNos to the grid) hit the database at the same time and SQL server appears to decide which order to run the processes in. In some instances the SELECT occurs first, keeping the INSERT at a wait status whilst the SELECT holds the KEY of the table. This lock stays in place restricting the INSERT until it times out, releases the KEY lock and allows the INSERT to proceed. At this point Metastorm reports the time out back to the client in the form of the helpful message ‘Root element is missing’ (possibly an invalid XML response with no root element).

The resolve for this (albeit not ideal) is to implement a dirty read on the table. Implement the sql NOLOCK hint in your grids table property = TelephoneNos WITH(NOLOCK) which instructs SQL to read uncommitted data allowing the INSERT to proceed and lock the table. Now I’m not proposing using this hint is good database design, all good DBA’s know this isn’t best practice but this approach may help in this instance and when the data being read is not critical information.