Uniface User Forum

We have a customer using SQL server 2017, connecting via the "ODBC driver 17 for sql server", from Uniface 9.7, using either MSS U5.1 or 5.2 (we've tried both). 

Occasionally (one in a thousand?), when proc issues a retrieve against a view in the database, the system seems to reach the read trigger, stop and never return. 

The entity is defined as no updates in both the model and the component, and there's been no opportunity to update any fields, as this is the first read in the retrieve. 

Moreover, the locked service (userver process), seems to be capable of locking something else up, as other services will begin to queue behind this process, and only become free when the offending service is killed. 

We have the lock timeouts set to infiinte, to prevent missed writes on the database, but this code should neither lock nor check for locks on the read.... 

When it's functioning properly, the retrieve and subsequent loop takes sub 2 seconds to run, so I can't check for locking behaviour at this point, because my reactions aren't fast enough. 🙂 

I think that when it's stopped, the sql script we use to check locks in the system is also locked up.... 😔, but as it doesn't happen often, so it's not repeatable, and it holds up production code while it's in play, we don't have long each time it happens to try diagnostics. 

I suppose that's what I'm looking for, anyone with a deeper understanding of either the uniface MSS driver behaviour, or SQL server management, to give me a clue what diagnostic I could try to see if I can bottom the problem. 

This customer is not the only one on this version of SQL server, or this version of uniface and drivers, but they are the only one reporting the problem. 

Sorry for the ramble, but I've tried to include everything I could think of. 

Iain

Hi Iain,

From your description it seems to me a locking issue.
Could you check if those basic tables, used to compose your view, have active locking on same rows you are trying to read with your view?

We faced this issue a couple of years ago on the "default" SQLserver configuration and we were able to reproduce it in our Uniface application.

We made this way:
1) Issue a lock on an occurrence of a table either with Uniface lock or manually with "SELECT FOR UPDATE".
2) Try to read that same occurrence of that table through a view either with Uniface form or manually using standard sql interfaces.
We went locked! Digging on it we found it is a specific SQLserver behaviour; other databases are much more user friendly in the same context.

Our solution was to switch our SQL Server database configuration from "Committed Read" to "Committed Read with Snapshot", managing the two related side effect:
a) Pressure on TempDB usage
b) Careful transaction planning and implementation through all application functionalities

It worked like a charm and we never experience those unusual locking situation anymore, neither with views or with tables, but we are still working on one of the two side effects from time to time.

Hope it helps.

Regards,
Gianni

No locks in the database at the time this is occurring as far as I can tell. Will use more complex lock examination above next time. 

Hi Iain,

there are more lock types involved into SQLserver not only the exclusive lock we associate to "SELECT FOR UPDATE". There is a matrix into SQLserver documentation documenting quite extensively the compatibility between all combinations.
If you just need to solve the lock combination between views and tables use SELECT ...WITH NOLOCK creating your views.
If you want a global solution having SQLserver using a mature locking strategy (read does NOT lock writes/deletes & writes/deletes does NOT lock reads & writes/deletes lock writes/deletes) look for "read committed snapshot modes"; it's a more tricky path but it is IMHO the final solution.

My/Our experience went this way from 2013 up to now where multiuser coherency was a MUST.

Regards,
Gianni

Hi Iain

You said, that the view will never check locks, you can use "WITH(nolock)" as option in a SELECT-clause

Are the "parameters" for the view variable at each call?
E.g. the columns will be assigned before the retrieve "by chance"
Then it could be, that the access strategy of MS-SQL is suboptimal and will have a full table scan
In particular NULL values are a candidate for such long runnig request.

As we do suffer from this in a few cases, we set "locktime" to a high value.
"maxlockwait" is only few seconds. If a row is still locked after this few sesonds, then it will bew lock also after a longer time (e.g. by a other process hanging)

To check locks, there is a nice tool to see locks on a database
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Ingo

SQL server can sometimes decide to escalate locks from row level to table level. You can influence this with MSS connector parameters: see lock granularity (uniface.com) and lock isolation (uniface.com). Have you also set the entity "no updates" property and/or commented out lock/write/delete triggers?

Yes, all set to no updates, triggers commented. 

So, it just happened again, (first time today), and whilst I was investigating (during which the userver process was showing busy for > 10 minutes), there were no locks in the database. 

Worse, there were 5 other uservers all showing busy, with no locks in the database, and when I killed off the one which had been busy longest (utoggle shows it was waiting for a read in the call stack) the others all started going again. 

This means if it is locks, they are invisible to the script we use to check them. 

I have downloaded and created the beta_lockinfo stored procedure referenced by the link Ingo listed (as it's sql server 2016) and will try that next time, 

So I went through the view and switched everything to "with (nolock)" as a hint in the view. 

This works fine in the management studio. When I try and retrieve the data in the view I get an error about different lock hints against one of the tables. 

That table is referenced in the service (and a single record has been retrieved previously), but it is set to 'no updates' in the service. This service is the only one running in this userver (because it's in debug mode). 

What, if anything, can I do about this? 

The lock inspector above is telling me all the locks are on 'read uncommitted'. I may try the snapshot one mentioned, but that's going to take a while to test out before I can put it on production.... 

Hi Iain

Problem is, that UnifAce will add a hint too : "WITH(rowlock)"

Maybe it's possible to remove this by setting in USYS$MSS_PARAMS
e.g. "gran:xyz" and "iso:xyz"

But then you will have other challenges to solve 🙂 or 😔

Long, long time I ask UnifAce to provide as with switches/options to override the default.
"Nope, not possible, because <some_variable_text>"

as we all in lockdown andthere is nothing todo over weekend, I can play around and check if there is any solution

Ingo

Hmm, I wonder if I could create a second path for the data, and run just this view on that path? Might work. A lot of faff though.