OE DataServer MS 11.4.
We are getting unexpected behaviour using the Data server into SQL Sever.
We can get an exclusive lock on a record via the 4gl, but then the code Releases the buffer it stalls for around 30 minutes and crashes.
I am thinking the 30 minutes comes from the default value of -lkwtmo
When we selected the record via SQL manager it just sits there "Executing query". So think we have a locking issue.
Current thinking is we need to Read-Committed on our queries into SQL server.
We specify -Dsrv TXN_ISOLATION,2 on start up and this certainly does set the isolation level be read committed.
The problem is when we use the data server it creates multiple odbc connections into SQL and only one of these connections have read-commit set.
q1) is it expected that one 4gl connect should spawn multiple odbc connections into sql server (in our case 4)?
q2) how do you get all of the connections to have read-committed? currently one 1 of the 4 connections is read-committed). (we have tried sending the sql that sets the isolation level directly but that too only sets 1 of the 4 odbc connections)
q3) any other ideas why it stalls trying write data back to sql server?
Any help appreciated .
Cheers
Craig
Hello Craig,
>> q1) is it expected that one 4gl connect should spawn multiple odbc connections into sql server (in our case 4)?
By default yes. DataServer will create one "read/write" connection through which it will route any data modification operations as well any non-NO-LOCK queries. Keep in mind some (maybe all) SHARE-LOCK queries might be downgraded to NO-LOCK depending on configuration.
This one connection if refered as the "Master Connection" in the dataserv.lg file.
On top of that you will usually have 5 extra connections for NO-LOCK queries, those are the "Firehose Connections" or "Secondary Connections" depending on where you look at. More can be created on demand, I've seen a client with around 30 connection per prowin32 session. You can control whether those connections are created and how many of them, but if you disable them or limit their usage performance will usually suffer.
>> q2) how do you get all of the connections to have read-committed?
AFAIK you don't. But again, when you use SHARE- or EXCLUSIVE-LOCK the operations will be routed through the master connection so that's the one you need to watch out for. I've never seen anything but NO-LOCK queries through secondary connections and NO-LOCK is pretty much the same as read uncommitted.
>> q3) any other ideas why it stalls trying write data back to sql server?
Hard to tell without looking, but check for blocked stuff in SQL Server. Maybe some other session has an UPDATE lock on the same resource and you are waiting for a conversion from U to X lock. Do you get any useful error message when you crash?
We had an issue with a combination of dynamic and static updates on the same buffer resulting in SYSTEM ERROR: bffld: nxtfld: scan past last field. (16). (bug PSC00355972)
Sprinkling an extra VALIDATE on the tightly scoped static buffer solved our issue.
What I think is happening is -lkwtmo is being hit and the Progress session quits.
I will try to add some more debugging details and scan the db.log for errors.
I believe things are going pear shaped when the record with and exclusive-lock hits a RELEASE statement.
According to the manual
>The following ABL statements will force a physical database write to a MS SQL Server data source before the
>end of a transaction block or the end of record scope
I'll just let it commit the record when the transaction ends.
Fingers crossed.
Craig.
What I think is happening is -lkwtmo is being hit and the Progress session quits.
I will try to add some more debugging details and scan the db.log for errors.
I believe things are going pear shaped when the record with and exclusive-lock hits a RELEASE statement.
According to the manual
>The following ABL statements will force a physical database write to a MS SQL Server data source before the
>end of a transaction block or the end of record scope
I'll just let it commit the record when the transaction ends.
Fingers crossed.
Craig.