-- Demo case: How sempahores are used to protect resources
-- Date: 13-05-2019
-- Demo case run on Oracle version: 12.2.0.1

-- Setup data
SQL> drop table t1;
SQL> create table t1 (a number);
SQL> insert into t1 values (1);
SQL> commit;

-- Session 1
SQL> update t1 set a=1 where a=1;

-- Session 2
SQL> update t1 set a=1 where a=1;

-- Trace system calls from session #2 after update
[root@OEL ~]# strace -tp 5773
Process 5773 attached
18:39:12 semtimedop(524290, {{54, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
18:39:15 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 44000}, ru_stime={0, 10000}, ...}) = 0
18:39:15 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 44000}, ru_stime={0, 10000}, ...}) = 0
18:39:15 semtimedop(524290, {{54, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
18:39:18 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 44000}, ru_stime={0, 10000}, ...}) = 0
18:39:18 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 44000}, ru_stime={0, 10000}, ...}) = 0
18:39:18 semtimedop(524290, {{54, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
....

-- Show sempahore details
T122DB [oracle@OEL scripts]$ ipcs -si 524290
Semaphore Array semid=524290
uid=502	 gid=500	 cuid=502	 cgid=500
mode=0600, access_perms=0600
nsems = 152
otime = Mon May 13 18:39:46 2019  
ctime = Mon May 13 18:39:46 2019  
semnum     value      ncount     zcount     pid  
....
54         0          1          0          5773      
....

-- Session 1
SQL> commit;

-- Trace system calls from session #1 for commit
[root@OEL ~]# strace -tp 5740
Process 5740 attached
....
18:41:36 semctl(524290, 54, SETVAL, 0x7fee00000001) = 0
....

-- Trace system calls from session #2 after commit in session #1
[root@OEL ~]# strace -tp 5773
Process 5773 attached
....
18:41:33 semtimedop(524290, {{54, -1, 0}}, 1, {3, 0}) = 0
....