One of our tables stores configuration data. Two stored procedures interact with this table; One of them uses the data for further processing; The other procedure is used to maintain the data.
Changes to the configuration data were usually done during a maintenance window, and the activation date was set in the future (at least one day ahead). During the backup of the database, the database was brought down so you could be sure that none of the "users of the data"-processes were still connected.
The requirements changed however, the activation procedure needed to run whenever they saw fit. That means that at any time, the business can decide that a new setting needs to be activated.
While there are still transactions active that use this configuration data, changes to this configuration data table can not be done. The need arose to "cut in" the active transactions and make the changes.
We have to allow running processes that consumed the configuration setting that we now want to change to complete with the current/old value. However, we do not want new processes to start before we have changed the setting – i.e. made the new record the active one. So we have to prevent the procedure that wants to activate this new setting from barging in while processes that used that setting are running, but we want to prevent new processes that will use it from actually using it before we activated the new setting.
Current processes continue to run, activate the very moment the last of the current setting-consuming-processes completes and immediately afterwards set free those impatient processes waiting to consume the new setting.
How do we do that?
While updating a record is no problem, the Audit data could be a problem. Consider this:
| Time | Maintenance Session | End User |
| T0 | Active Set: 1 | |
| T1 | Start Process: Reads Active Set 1 | |
| T2 | Change Active Set: 2 | Still Running |
| T3 | Audit shows: Active Set 1 |
Why would it show Active Set 1? It all has to do with read consistency. When the End User started his process, the current Set showed up as
Number 1, so that is the one the End User will see as the Active one. The Audit trail however, will show an ended time of T3. At T3, the Active Set is 2 and not Set 1.
The processes of "Activating A Set" and the "Using the Sets" processes need to be coordinated.
What needs to be coordinated, is the following:
The second type of process cannot start when one of the first type of process is still running. The other way around is equally true, the first process cannot start
to run when the second process is running. A lot of process of the first type can run simultaneously. In a picture it would look like this:
The arrows are sessions that run a stored procedure of either the "Blue" or "Red" type.
The blue arrows represents a process of the first type, many of them running at the same time. The red arrow represents the second process.
It is not necessary for the blue arrows to all start at the same time, but they are not allowed to run while the red process is running.
The initial response was to create some kind of flag-attribute which is set when the Red process starts to run. This will signal all Blue processes not to start.
When a Blue process wants to start, an exception is raised stating that the Red process is running and it can not continue. The user initiating the Blue process will
then attempt at a later time. This is not really user-friendly, but this can be solved; by submitting a database job which will attempt to run the Blue process. When
the Red process is finished, the flag is cleared, the job can finally run.
How can you detect which processes are still running? One way is to let the Blue process also set a flag, letting other processes know it started a run.
When you think about it a little more… when lots and lots of Blue processes are running, how can you (being Red) cut in and stop the Blue ones from being submitted
all the time?
One way of solving this can be by first setting the Red flag, but check the Blue flag before actually doing what you need to do.
A lot of work, shortcuts any one?
That sounds like a lot of work, nevertheless it is possible to solve the requirement. You would need a table to persist the flags, and a couple of stored procedures
to manipulate these flags.
These stored procedures, preferably in a package, should be Autonomous Transactions so they won’t interfere with the transactions done by the Blue and the Red processes.
There are probably a lot of things I did not even think of. It is quite possible to come up with circumstances for this implementation to fail. It is as bullet-proof as
I would like it to be?…
In the beginning of this year, I attended a three-day seminar with Jonathan Lewis. On the last day he painted a similar scenario. The solution Jonathan came up with,
was to use the built-in functionality Oracle already provides you with. Why would you re-invent the wheel?
What he used was DMBS_LOCK to parallelize and serialize the different processes. A lot simpler than the home-grown implementation.
The basic idea is to create a user defined lock handle and depending on the process-type either lock it in a shareable mode or in an exclusive mode.
This implementation is a lot cleaner and requires less code. Most of all, why build something when it already exists?
Here is how you would implement the required functionality using DBMS_LOCK.
Blue Process
The Blue process implements the user-defined lock handle in the ‘Intended Share’ mode. That says it all, we intend to share this lock handle with many different sessions.
lh := dbms_lock.request (123
,lockmode => dbms_lock.ss_mode
);
The ‘123’ in the code section above is the name of the user-defined lock. You can choose any name you want. What might be a good idea is to create a package specification that
will hold all names of the locks in the application instead of hardcoding these.
When you query V$LOCK, you can see this lock show as a UL-type of lock. According to the documentation it stands for User supplied Lock.
SQL> exec blue
PL/SQL procedure successfully completed.
SQL> select * from v$lock
2 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C662E84 6C662E98 144 UL 123 0 2 0 22 0
...
You can also see that ID1 is 123, the name we picked for our user-defined lock. LMODE is 2, this is the Lock Mode we requested and got.
When we start up another session, and run the Blue procedure, we will see an extra entry in V$LOCK:
SQL> select * from v$lock
2 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C662E84 6C662E98 144 UL 123 0 2 0 262 0
6C662FA4 6C662FB8 146 UL 123 0 2 0 19 0
...
The ID1 is the same, the lock mode is the same. Both sessions have the lock, because it is a shareable lock. Both session are running the Blue process.
Red Process
The Red Process requests the lock (with the same name, of course) but, in a different mode, namely the exclusive mode:
lh := dbms_lock.request (123
,lockmode => dbms_lock.x_mode
);
After we execute the Red procedure, the Blue procedures from the previous section are cleared, consequently looking at V$LOCK we see this:
SQL> exec red
PL/SQL procedure successfully completed.
SQL> select * from v$lock
2 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C662E84 6C662E98 146 UL 123 0 6 0 9 0
...
ID1 is the same, we request the same lock handle. The Lock mode (LMODE) is 6, this means it has an exclusive lock. If we start up another session and
run the Red process, we will get blocked. You can also see this in V$LOCK:
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C662E84 6C662E98 148 UL 123 0 6 0 59 1
6C662FA4 6C662FB8 153 UL 123 0 0 6 53 0
...
The session with SID 148 has acquired the lock in an exclusive mode, LMODE 6. The other session with SID 153 is attempting to acquire an
exclusive lock, but cannot get it. The REQUEST column shows the requested lock mode. The BLOCK column in V$LOCK indicates which session
is blocking others. Using the following SQL, written by Tom Kyte, you can find out who is blocking who:
select (select username
from v$session
where sid = a.sid) blocker
, a.sid
, ' is blocking '
, (select username
from v$session
where sid = b.sid) blockee
, b.sid
from v$lock a
, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------- ------------------------------ ----------
ALEX 148 is blocking ALEX 153
Blues and Reds
Theory is all nice, but now let’s run the Blues and Reds at the same time. I have three sessions running a Blue procedure, and one running a Red procedure.
Looking at V$LOCK, this is the result.
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663364 6C663378 141 UL 123 0 2 0 28 1
6C662F44 6C662F58 142 UL 123 0 2 0 50 1
6C662FA4 6C662FB8 143 UL 123 0 0 6 13 0
6C663064 6C663078 153 UL 123 0 2 0 37 1
...
You can see which ones are the Blues (SID 141; 142; 153) , and which one is the Red (SID 143). All Blues have acquired an LMODE 2. The Red one has requested, but not gotten, an LMODE of 6.
What you can also see in V$LOCK is that the Blues are blocking the Red. The Block column shows you this. The Blue processes were started before the Red.
When we do it the other way around, first start the Red followed by the Blue, we see this:
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663364 6C663378 141 UL 123 0 0 2 10 0
6C663064 6C663078 142 UL 123 0 0 2 16 0
6C662F44 6C662F58 143 UL 123 0 6 0 31 1
6C662FA4 6C662FB8 144 UL 123 0 0 2 25 0
Now it is exactly the other way around. The Red is blocking the Blues. The Blues are all waiting patiently.
When we release the Red lock, this will be described later on, the Blues start to run at once.
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663064 6C663078 141 UL 123 0 2 0 12 0
6C662FA4 6C662FB8 142 UL 123 0 2 0 12 0
6C663364 6C663378 143 UL 123 0 2 0 12 0
...
Mixing Blues and Reds
When we start up a Blue process followed by a Red one, followed by another Blue. This is what we will see:
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663064 6C663078 141 UL 123 0 0 2 18 0
6C662FA4 6C662FB8 142 UL 123 0 0 6 24 0
6C662F44 6C662F58 143 UL 123 0 2 0 30 1
...
SID 143 has the lock acquired, the other two are waiting for it. When we release the lock, this is the result:
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663064 6C663078 141 UL 123 0 0 2 111 0
6C662FA4 6C662FB8 142 UL 123 0 6 0 6 1
...
When the Blocking process is out of the way, the process next in line takes over. You can tell which one is next in line, based on the
CTIME column. This represents the time since the current mode was granted. This happens to be the Red process and this
one blocks the remaining Blue process. When this Red process is done, this is what we will see in V$LOCK:
SQL> select *
2 from v$lock
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C663064 6C663078 141 UL 123 0 2 0 7 0
...
Releasing the acquired Locks
There are several ways to release an acquired lock. The built-in DBMS_LOCK package has a function to release a lock.
lh := dbms_lock.release (123);
This will release the named lock.
You can also release the locks whenever you issue a commit. When that is the case, you can specify this when requesting the
lock. The lock will also be released when a ROLLBACK is issued.
lh := dbms_lock.request (123
,lockmode => dbms_lock.ss_mode
,release_on_commit => true );
Another way to release the lock, is to terminate the session. How you release the lock depend on your specific requirements.
Limitations and Truth Table for locks
According to the comments in the DBMS_LOCK package, there is a limitation that it does not support large number of locks efficiently.
A few hundred locks per session should be the limit.
| 1 | NL | Null |
| 2 | SS | Sub Shared |
| 3 | SX | Sub eXclusive |
| 4 | S | Shared |
| 5 | SSX | Shared Sub eXclusive |
| 6 | X | eXclusive |
| NL | SS | SX | S | SSX | X | |
| NL | SUCC | SUCC | SUCC | SUCC | SUCC | SUCC |
| SS | SUCC | SUCC | SUCC | SUCC | SUCC | fail |
| SX | SUCC | SUCC | SUCC | fail | fail | fail |
| S | SUCC | SUCC | fail | SUCC | fail | fail |
| SSX | SUCC | SUCC | fail | fail | fail | fail |
| X | SUCC | fail | fail | fail | fail | fail |
The scripts can be downloaded here.
![ApEx 2.2 released]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "ApEx 2.2 released")
