How the Database Storage Manager Uses Semaphores

Posted by George Potemkin on 11-Apr-2020 14:54

It’s the little addition to the article knowledgebase.progress.com/.../19958 (Jul 14, 2000)

The semaphores used by Progress are divided in three types: login, ready and user.
* The login semaphore (a.k.a. LOGSEM, semnum 0) is the resource every process needs to connect to the database and it is only used for that purpose. The default value of the login semaphore is 1. It means that the login semaphore is not locked. Login semaphore is locked when process sets it to zero. If another process is trying to lock the semaphore at the same time it will increase the value of ncount - the number of processes blocking on the semaphore, waiting for it to increment;
* The ready semaphore (semnum 1) controls the access to the database during database startup. While the database is starting the value of the ready semaphore is 1. When database broker writes the message 10471 (Database connections have been enabled) the value is changed to 2. By the way, at the same moment the database mode in the lock file (.lk) is changing from 5 (crash recovery) to 2 (multi-user mode). The value of ready semaphore is not changing during database shutdown;
* The user semaphores are used by the user processes already connected to the database to lock a record or other resource. The last user semaphore is reserved for proshut only (including proshut -C list but not for proshut -0). The values of the user semaphores are 0. When the user is waiting on the semaphore for the locked resource the value of its semaphore stays zero but ncount is changing to 1.

The wchan (“waiting channel”) option will give you the list of the processes that are blocked on the semaphores right now.
On Linux you can run grep semtimedop /proc/[1-9]*/wchan
Or use ‘ps -o wchan’.

You can see more processes than the ones reported by promon as blocked.
The knowledge of the typical number of such processes is a good addition to the statistics of the resource waits per second available in promon or in VST (_ActOther._Other-Wait)
Another way to get the list of such processes on Linux is to run ipcs -s - i <id >for all semsets and to get “pid” for the rows with non-zero “ncount”.

All three types of the semaphores can be put into the same pool and we can refer to this pool as a "semset". The number of semaphores within this set is determined by the Maximum Number of Users (-n) and Maximum Number of Servers (-Mn) startup parameters for each database. Minimal size (nsems) of the single semaphore set is 7: one semaphore (semnum 2) is a spare, the semnum of the user semaphores are beginning from 3.

If the database is using the multiple semaphore sets (-semsets 2 or higher) then one semset (“login semset”) is dedicated to the login and ready semaphores. Its size (nsems) is always 7: 5 semaphores are spare. The minimal size of the user semsets is 2.

ID of the login semset is stored in the master block as mb_semid.
IDs of the user semsets can be found in promon dbname > R&D > debghb > 6 (hidden option) > 16 semaphores.
On Linux we can also identify the login semset and first user semset by the PID of database broker.

The semaphore sets have two timestamps:
otime = last-op = the time the last semaphore operation was completed on the set associated with the semaphore entry;
ctime = last-changed = the time when the set was created or changed.

When the semaphore sets are created at database startup the values of ctime are the same for all sets - it’s the timestamp of the message 333: Multi-user session begin. At this moment the otime is set only for the login semset. The otime of the user semsets is “Not set” on Linux, “no-entry” on SunOS etc.

On SunOS and AIX the ctime of the semaphore sets is not updated after they were created.
On Linux the ctime will be changed:
When the crash recovery is completed then otime and ctime for login semset are re-set to the current time - the time of the message 10471: Database connections have been enabled.
The ctime of login semset is not changing after the database connections were enabled.
The ctime of user semset is changing after each login/logout of user that uses a semaphore in the semset.

The otime seems to be updated by the same rules on all platforms:
The otime of login semset is changing after every login/logout.
The otime of user semset is updated when the user gets the resource it was waiting for on the semaphore.

Any comments or additions?

The goal is the troubleshooting of the situations when database hung. Linux gives us the opportunity.

Example:
USR latch is locked, the semaphores are not locked.

Login semset is normal:

Semaphore Array semid=12517378
uid=0  gid=0  cuid=0  cgid=0
mode=0666, access_perms=0666
nsems = 7
otime = Sat Apr 11 16:02:00 2020  
ctime = Sat Apr 11 15:46:14 2020  
semnum     value      ncount     zcount     pid
0          1          0          0          5973
1          2          0          0          32463

New process (PID 6370) is trying to connect the database but there are no login messages in database log and the process is not visible in promon. Its PID is reported by login semaphore.

The sign of trouble: the value of login semaphore decreased to zero:

Semaphore Array semid=12517378
uid=0  gid=0  cuid=0  cgid=0
mode=0666, access_perms=0666
nsems = 7
otime = Sat Apr 11 16:09:55 2020
ctime = Sat Apr 11 15:46:14 2020
semnum     value      ncount     zcount     pid
0          0          0          0          6370
1          2          0          0          32463

By the way, the process got its own user semaphore (semnum 4):

Semaphore Array semid=12550147
uid=0  gid=0  cuid=0  cgid=0
mode=0666, access_perms=0666
nsems = 13
otime = Not set
ctime = Sat Apr 11 16:09:55 2020
semnum     value      ncount     zcount     pid
0          0          0          0          32463
1          0          0          0          0
2          0          0          0          0
3          0          0          0          32493
4          0          0          0          6370

In other words, the process scanned the Usrctl table and found the empty entry to use but it can’t update the entry because the USR latch is locked.

Now the second process (PID 6920) is trying to connect the database. Its PID is not reported by the semaphores but now we get a queue to the login semaphore because ncount is increased:

Semaphore Array semid=12517378
uid=0  gid=0  cuid=0  cgid=0
mode=0666, access_perms=0666
nsems = 7
otime = Sat Apr 11 16:09:55 2020
ctime = Sat Apr 11 15:46:14 2020
semnum     value      ncount     zcount     pid
0          0          1          0          6370
1          2          0          0          32463

Another example: USR latch is released but now the login semaphore is locked. The user (USR 5, PID 5973) previously connected to the database is trying to disconnect.

Database log says:
ABL 5: (453) Logout by ...

But the process still exists on the system level (ps -p 5973) and it holds the database files opened (lsof -p 5973).

The queue to the login semaphore is increased again:

Semaphore Array semid=12517378
uid=0  gid=0  cuid=0  cgid=0
mode=0666, access_perms=0666
nsems = 7
otime = Sat Apr 11 16:09:55 2020
ctime = Sat Apr 11 15:46:14 2020
semnum     value      ncount     zcount     pid
0          0          2          0          6370
1          2          0          0          32463

We can find all processes on the queue using the wchan option.

# grep semtimedop /proc/[1-9]*/wchan
/proc/5973/wchan:sys_semtimedop
/proc/6370/wchan:sys_semtimedop
/proc/6920/wchan:sys_semtimedop

The quiz: what will happen with the database if we kill -9 the process that is holding the login semaphore? Is the login semaphore a database or system resource?

All Replies

Posted by gus bjorklund on 12-Apr-2020 16:20

> On Apr 11, 2020, at 10:56 AM, George Potemkin wrote:

>

> user semaphores are used by the user processes already connected to the database

to wait for one of many types of resources. semaphores are overkill for this purpose but on UNIX and Linux, there is no better choice.

for example, when a connection wants a record lock and there is a conflict because some other connection has a higher priority lock (e.g. share vs exclusive, exclusive vs exclusive) the requestor inserts a node into the lock table with a status of "queued" and then waits on its semaphore. when the holder of the lock goes to release it, it notices that there is a queued request and signals the requestor's semaphore in order to wake it.

in some cases, such as share lock waiters for the same resource, there may be multiple requesters waiting, each on their own semaphore, and all are awakened one after the other.

historical note: the VMS operating system had a system call to stop a process until it was resumed by another process or a signal handler. on vms, we did not need semaphores for waiting and resuming.=

Posted by George Potemkin on 12-Apr-2020 17:19

Thanks, Gus.

Can you reveal the secret of the queued latches (USR and SCH/SCC)?

How these latches work together with the semaphores?

Both semaphores and latches are the locks – slow locks and fast locks. Does process use, for example, a user semaphore to put itself in the queue to access the USR latch? I can generate the high activity on the USR latch but I don’t see the related activity on the semaphores. I can catch the process when it holds the USR latch but I don’t see any changes of its semaphore status.

Posted by George Potemkin on 15-Apr-2020 14:15

> The quiz: what will happen with the database if we kill -9 the process that is holding the login semaphore? Is the login semaphore a database or system resource?

The answer: kill -9 will undo the changes done by the process to the semaphores.

So why watchdog crash a database if a process was killed by -9 while it was just waiting on semaphore for the buffer lock?
SYSTEM ERROR: User <num> died during microtransaction. (2256)
Should it be fixed in the context of the Five Nines strategy?

This thread is closed