Resolved Question About Sysprogress Use Locking _sec-authentication-domain

[Ryszard Musielak]

Hello,

would anyone be able to help me? I have a system that sends me an email when a table is locked for longer. Once in a while (like once a month) I get a surge of message of this kind:

ERROR - _sec-authentication-domain locked by sysprogress since 10:04 29/11/17
Alert Type:   2 - Long Lasting Locks
Date: 29/11/17    Time: 13:04:57

A _sec-authentication-domain record has been locked by user 'sysprogress' since 10:04 29/11/17

Record Details:
        Domain name   =

The record is currently Share-Locked.

It seems like a disconnected session in proshut:

RETURN - show remaining, Q - quit:
usr    pid    time of login           user id     Type  tty                  Limbo?
424  27072 Wed Nov 29 09:36:15 2017   sysprogress REMC                       no

A UNIX process is associated with this:

root     27072     1  0 Nov22 ?        01:36:24 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=3 -D juniper.dbfile="package" -D juniper.config="package.virtualconfig" -D juniper.service="package.virtualconfig.virtual" -D juniper.propertyfile="" -D juniper.cplog="ISO8859-1" -D juniper.cpcase="Basic" -D juniper.cpcoll="Basic" -D juniper.cpinternal="ISO8859-1" -D juniper.convmap="convmap.cp" -D juniper.sqlyearoffset="1950" -D juniper.vpf=true -ss 500k -classpath "/bcp/dlc/jdk/lib/tools.jar:/bcp/dlc/java/progress.jar:/bcp/dlc/java/messages.jar:/bcp/dlc/java/prosp.jar" -SQLStack 0 -SQLStmtCache 0 -SQLCursors 0 -SQLTempStoreBuff 0 -SQLTempStoreDisk 0 -SQLTempStorePageSize 0 -Bt 0 -t 0 -T  -SQLTempMgrLegacy 0 -ipver IPV4 -PendConnTime 90 -SQLQuotedRowidStd 1 -SQLAutoStats 0 -SQLAutoStatThreshold 0 -SQLLockWaitTimeout 0 -SQLTruncateTooLarge 0 -SQLWidthUpdate 0 -H 0.0.0.0

I've had to kill this process and there was no impact, the system was working well without any issues, however I have this problem reoccurring and wanted to ask where does this come from?

There are many other such processes, from the start time they look more like system processes, as they've been running since mid November. In Promon <database> the locking screen shows this:

Record Locking Table: by user number for all tenants
  Usr:Ten   Name      Domain     Chain #                         Rowid Table:Part  Lock Flags   Tran State          Tran ID
  422       racasu        0      REC        4898             152331714   827       SHR  D       None    FWD         1511058882
  424       sysprogress     0      REC        2402                 15672  -207       SHR          Begin   FWD         1510540329

Thanks,
Richard

 

[TheMadDBA]

The SQL engine will put a share lock on several of the underscore tables depending on the version of OE. That session needs to be issuing a COMMIT or a ROLLBACK.. regardless of the transaction isolation level. If it is truly just sitting around then it should be leaving the session.

On the plus side.. unless you are trying to change your domains on a regular basis this will not cause any issues with the application.

 

[Ryszard Musielak]

I normally watch the process. At the moment the impact is none, however in the past I would see those processes stuck at 90% CPU and then I would kill them. Is that ok?

Is this actually a system process, or a post-user process somehow detached from the user session?

They seem to be normal and have been in active for some days:

 ps -ef | grep sqlsrv2 | cut -c1-120
root      3284     1  0 Oct19 ?        00:00:01 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=6 -D juniper.dbfile="package"
root      4637     1  0 Sep18 ?        04:00:50 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=23 -D juniper.dbfile="package"
root     11720     1  0 Sep18 ?        00:32:07 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=24 -D juniper.dbfile="package"
root     12209     1  0 Sep18 ?        04:57:02 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=20 -D juniper.dbfile="package"
root     17676     1  0 Sep27 ?        00:00:40 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=3 -D juniper.dbfile="package"
root     17680     1  0 Sep27 ?        00:00:14 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=4 -D juniper.dbfile="package"
root     18771     1  0 Nov23 ?        00:18:58 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=8 -D juniper.dbfile="package"
root     20097     1  0 Nov23 ?        00:03:28 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=22 -D juniper.dbfile="package"
root     21632     1  0 Nov17 ?        00:01:34 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=28 -D juniper.dbfile="package"
root     27072     1  0 Nov22 ?        01:36:24 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=3 -D juniper.dbfile="package"
root     27388     1  0 Oct09 ?        00:21:14 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=26 -D juniper.dbfile="package"
root     27404     1  0 Oct09 ?        00:05:44 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=27 -D juniper.dbfile="package"
root     27638     1  0 Sep27 ?        00:00:03 /bcp/dlc/bin/_sqlsrv2 -D juniper.serverid=5 -D juniper.dbfile="package"

 

[Rob Fitzpatrick]

The _sqlsrv2 process is a SQL server: a database process, spawned by a SQL broker to handle connections from SQL database clients. These processes, once spawned, run persistently until the database is shut down, whether or not there are currently any SQL clients connected to them.

You can see the relationship between clients and servers in promon 1 | 1 (User Control). The "Srv" column shows the user number of the server to which each remote client is connected. The list of servers can be seen in promon R&D | 1 | 3 (Status: Servers) and in R&D | 1 | 17 (Servers by Broker).

The REMC (remote client) user you see in promon with userid "sysprogress" is a client. Note: sysprogress is a special built-in database account. It's not a good practice to allow this account to be used by users. If I were you I would use it to create a new SQL account and have the application user use those credentials, and then change the sysprogress password. You probably don't want the application user to be a DBA.

 

[Ryszard Musielak]

Hi, thanks for the nice explanation. I do not suspect anyone uses the userid "sysprogress", I know it's a built in system account.

What I suspect it may be a disconnected user session and the _sqlsrv2 could be a "limbo" process, that once belonged to a user and "sysprogress" has inherited it. I don't know if I am thinking correctly. Would that happen if a user was disconnected from a network? If so is is same to kill it?

As for the "promon" options, the "srv" column shows mainly 0 for the users:

User Control: by user number for all tenants
  Usr:Ten   Name      Domain     Type      Wait  Table:Part               Dbkey     Trans      PID Sem Srv Login  Ti
    0       root          0     BROK       --        0                    36937         0     5790   0   0 11/29/17 
    1       root          0     SERV       --        0                        0         0     7249   0   0 11/29/17 
    2       root          0     SERV       --        0                        0         0     7291   0   0 11/29/17 
    3       root          0     SERV       --        0                        0         0     7749   1   0 11/29/17 
    4       root          0     SQSV       --        0                        0         0    10541   1   0 11/29/17 
    5       root          0     SERV       --        0                        0         0    16912   1   0 11/29/17 
    6       root          0     SERV       --        0                        0         0    21529   2   0 11/30/17 
    7       root          0     SERV       --        0                        0         0    23573   2   0 11/30/17 
    8       root          0     SERV       --        0                        0         0    24487   2   0 11/30/17 
   51       root          0     BIW        --        0                    28516         0     7231  17   0 11/29/17 
   52       root          0     APW        --        0                    41595         0     7234  17   0 11/29/17 
   53       root          0     APW        --        0                    37483         0     7237  17   0 11/29/17 
   54       root          0     WDOG       --        0                        0         0     7252  18   0 11/29/17 
   55       Isearle       0     SELF/APSV  --        9                   609871         0     7420  18   0 11/29/17 
   56       mpatterson     0     SELF/APSV  --        8                        3         0     7422  18   0 11/29/17
   57       rsmith        0     SELF/APSV  --        8                 19005696         0     7424  19   0 11/29/17 
   58       salesteam     0     SELF/APSV  --        8                  2392320         0     7426  19   0 11/29/17 
   59       rsmith        0     SELF/APSV  --        0                        0         0     7428  19   0 11/29/17 
   60       mpatterson     0     SELF/APSV  --        0                        0         0     7430  20   0 11/29/17

I don't know what the 0 means in this case.

As for this option:
"The list of servers can be seen in promon R&D | 1 | 3 (Status: Servers)" I don't seem to have that, this is what I do have:

    OpenEdge MONITOR Release 11

    Database: /bcp/pack/package

    1.  User Control
    2.  Locking and Waiting Statistics
    3.  Block Access
    4.  Record Locking Table
    5.  Activity
    6.  Shared Resources
    7.  Database Status
    8.  Shut Down Database
    9.  Currently Connected Tenants

  R&D.  Advanced options
    T.  2PC Transactions Control
    L.  Resolve 2PC Limbo Transactions
    C.  2PC Coordinator Information

    J.  Resolve JTA Transactions

    M.  Modify Defaults
    Q.  Quit

This leaves me a bit confused.

Thanks again for your time.
Richard

 

[Rob Fitzpatrick]

Which version of OpenEdge 11.x are you running?

I do not suspect anyone uses the userid "sysprogress", I know it's a built in system account.

According to your first post, user 424 (a remote client) was authenticated as "sysprogress".

What I suspect it may be a disconnected user session and the _sqlsrv2 could be a "limbo" process, that once belonged to a user and "sysprogress" has inherited it. I don't know if I am thinking correctly. Would that happen if a user was disconnected from a network? If so is is same to kill it?

Regarding user 424, check promon 1 (User Control) and you should have the user's IP address and the PID on their system. You can check netstat on the server or that user's machine to determine whether that process is still running and has an active TCP socket connection to the database server.

I don't follow why you think the SQL server process (_sqlsrv2) is a "limbo" process. Servers are spawned by brokers, relatively early in the life of a database instance, in response to a connection request from a client of a given type. When an ABL client wants to connect and there isn't an available ABL server for it to connect to, the appropriate broker spawns an ABL server (an _mprosrv process). The same is true for SQL brokers and servers. Once that server is running, it remains running for the life of the database, whether it has connected clients or not. Each individual client connects to a particular server, and only that server, but otherwise, processes don't "belong to" each other or "inherit" each other.

As for the "promon" options, the "srv" column shows mainly 0 for the users:

I don't know what the 0 means in this case.

You will note that for the users shown in your promon output here, the user numbers are low (0 - 60) and the value in the "Type" column is "SELF/APSV". This means the processes in question are OE Application Servers and they are connected to the database in "self-service" mode. This is also known as a "shared memory" connection, as opposed to a TCP connection. These processes do not connect to a database server to read and write data; they connect to the database directly, effectively acting as their own server. That is why "SRV" shows zero: they have no server. The "SRV" column will only show a non-zero value for remote clients (type "REMC").

Regarding user numbers: when a self-service process (client, server, utility) connects to the database, it is given the lowest available user number (with the lowest numbers reserved for brokers and servers). When a remote client connects, it is given the highest available user number. So the user number also gives you a clue about the nature of a client or other process, in terms of how it connected.

As for this option:
"The list of servers can be seen in promon R&D | 1 | 3 (Status: Servers)" I don't seem to have that, this is what I do have:

Sorry, that was a bit of promon user-interface shorthand; several instructions in one line.

You see that the main menu has an item "R&D. Advanced options". If you enter "R&D" at the prompt, you will get to another menu with numbered options. Select option 1 from that menu to access the list of "Status" options, a sub-menu. Then in that menu, select option 3 ("Servers"). Note: once you go into R&D, you can't get back out to the main menu. To do that, you need to exit and re-run promon. You can read all about promon in its chapter in the Database Administration manual. The documentation set for your release is available here:
OpenEdge Product Documentation Overview - Wiki - OpenEdge General - Progress Community.

 

[Ryszard Musielak]

I now know that we do use the "sysprogress" user, my developers have to run SQL to run various reports, at the moment this is all we have, as our outsourcing company has never given us any preconfigured user other than the "sysprogress".

I now understand the "server" concept, I managed to get into the R&D menu. I can now see that servers on average have about 0 to 5 users connected.

I suspect the "sysprogress" servers may sometime spin out of control and I know who they belong to, as one of my developers's session would sometimes have to be killed and would leave a sysprogress UNIX process at 98%.

Thank you again for the very long explanation.

Kind Regards
Richard

 

[Cringer]

No idea if this helps, but if the guys are running reports only then we configure the ODBC connections to be READ UNCOMMITTED. That way it's impossible for them to lock anything as they don't have permission to update records.

 

[Rob Fitzpatrick]

No idea if this helps, but if the guys are running reports only then we configure the ODBC connections to be READ UNCOMMITTED. That way it's impossible for them to lock anything as they don't have permission to update records.

Good point. Also, if the users are query-only then a different user account can be configured that only has SELECT privilege on the needed tables. Best not to give users more privilege than they require. Hopefully the outsourcer can sort that out.

 

[Ryszard Musielak]

Hello All and many thanks to your comments. I will have a look at the ODBC connection config and check if READ Only is set. A very good thought indeed

*** And A Happy New Year to All ***

Rich