Oracle - Basics of locking

Basics of Oracle Locking

==================

Locking is implemented through a very basic data structure i.e., linked list. There is a waiter's linked list and there is a owner's linked list. Waiter will move up in queue and will try to gain the resource it’s waiting for.

-- Open one session

conn scott/tiger

SQL> update emp set sal=1000 where empno=7369;

1 row updated.

-- another session

conn scott/tiger

SQL> update emp set sal=2000 where empno=7369;

waiting for other session to commit or rollback.

-- finding the process id of current session

select spid from v$process p, v$session s where p.addr = s.paddr

 and s.sid = (select sid from v$mystat where rownum=1);

SPID

------

5237

Starting strace for the process started by the session we can see below system calls for the session waiting for the lock.

strace -p 5237 -o 5237.log

.

.

semtimedop(688128, {{44, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)

semtimedop(688128, {{44, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)

semtimedop(688128, {{44, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)

semtimedop(688128, {{44, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)

semtimedop(688128, {{44, -1, 0}}, 1, {3, 0}) = 0

semctl(688128, 15, SETVAL, 0x1)         = 0

semtimedop(688128, {{44, -1, 0}}, 1, {0, 100000000}) = 0

As we can see semaphore operations are performed to implement the locking. There will be a waiter’s queue where all sessions will be waiting for the semaphore with id 688128. After every interval, it checks for the resource.

semtimedop - semaphore operations

int semtimedop(int semid, struct sembuf *sops, unsigned nsops, struct timespec *timeout);

Owner process calls semctl function to set the value of semaphore after granting the lock. Next we see waiter process is notified and semtimedop value gets changed.

We can see the semaphore details.

[root@localhost log]# ipcs -s

------ Semaphore Arrays --------

key        semid      owner      perms      nsems     

0x90c3f0a8 688128     oracle     640        154       

[root@localhost log]# ipcs -s -i 688128

Semaphore Array semid=688128

uid=1001    gid=1001   cuid=1001  cgid=505

mode=0640, access_perms=0640

nsems = 154

otime = Sun Jul 16 23:52:28 2017  

ctime = Sun Jul 16 23:52:28 2017  

semnum     value      ncount     zcount     pid       

0          0          0          0          14558     

1          29187      0          0          14558     

2          7985       0          0          14558     

3          32762      0          0          14558     

4          0          0          0          0         

5          0          0          0          0         

6          0          0          0          14562     

7          0          1          0          14564     

pid is the process id linked with the semaphore.

We will see more on locking in another section.