[Tfug] Locking in RDBMS's

[Bexley Hall]

Argh!  This is yet another head-scratcher.
[not for the feint of heart]

In the past, any time I have needed to embed a
database (i.e. "table") in a device, I have 
constrained access to it through the use of
monitors, mutex's and other synchronization
mechanisms.

However, I am now trying to abstract the notion
of the database to a first class object.  And,
"externalize" it.  So, concurrency issues become
more delicate -- since the RDBMS must, itself,
play a role in implementing those policies.

This is new territory for me so I am hesitant
about blindly jumping in and screwing things
up beyond repair  :>

I'm using PostgreSQL as my RDBMS.  Eventually, it
may run on the same hardware as my device.  But,
my *design* should accommodate its running on
foreign hardware (if you think about it, the design
issues are EXACTLY the same as if it was running
"native"!).  In particular, I can't rely on the
RDBMS being "up" at all times (whereas I can
design a memory resident "table" patrolled by
monitors that is "crash-proof", etc.) nor can I
count on bounded access times (which I *can* in
a closed "memory table" implementation).

But, the biggest issue I face is dealing with
"other RDBMS users" (which may be automatons)
manipulating the data while "my" users (client
applications) are accessing or relying on it in
other ways -- a generalization of the classic 
readers-writers problem...

Since folks seem to need concrete "talking examples"
instead of relying on "textbook" abstractions, let
me try to build a realistic sample scenario...

Imagine the HVAC controller alluded to in previous
posts (i.e. the swamp cooler discussion).  One
hypothetical way of implementing it might be:

- examine HVAC *schedule* to determine next intended
  event (i.e., "temperature should be 70 degrees at
  6:00AM, when I awaken").  This is an RDBMS query.
- look at current and "recent past" conditions (e.g.,
  indoor temp, outdoor temp, humidity level, etc.).
  This is another RDBMS query.
- consult knowledge of how current conditions effect
  your *control* over the indoor temperature.  This
  is yet another RDBMS query.
- use the above knowledge base and current conditions
  to predict when you must take action in order to
  achieve the required GOAL at the required TIME
  (e.g., if it is 85 degrees outside and very dry,
  the swamp cooler can lower the indoor temperature
  1 degree for each minute that it runs at a cost 
  factor of X; if humid, the ACbrrr must be used,
  instead -- but it can achieve the same 1 degree of
  cooling in 47.2096 seconds)
- sleep until that time comes (so you don't waste
  computing resources) and, when it does, do what
  you have decided is necessary

Relatively straightforward.  Except for all the
things that can go wrong.  :>

To tie in to the recent discussion about *time*,
obviously this HVAC "task" should wake up if
someone dicks with the current time of day (since
it seems safe to assume that the "6:00AM" mentioned
in the schedule means "6:00AM in whatever CURRENT
sense of time may be in effect *at* 6:00AM").

Likewise, if "current conditions" change (e.g.,
if it gets hotter outside, then it may take longer
to cool the house down to the target temperature
EVEN IF THE INDOOR TEMPERATURE HAS NOT CHANGED).

Or, the *rules* governing how to interpret and
respond to environmental issues may have been
changed (i.e., the "knowledge base" has been
updated and, e.g., it now advises that the cooler
can only drop the indoor temperature *1/2* degree
for every minute the cooler is in operation).

Or, the user may dick with the HVAC schedule and
possibly want the change to take effect earlier,
later or have a different target temperature, etc.
Or, may impose constraints on the HVAC system that
were not present earlier (e.g., do not use the swamp
cooler after September 1 because I will have 
winterized it at that time)

Any of these events should signal() the HVAC task
that it should wake up and reevaluate its decision
as to what its action(s) should be.  Even if that
decision is simply "go back to sleep" -- though,
perhaps, for a *diffferent* length of time...

If the "schedule" is implemented in the RDBMS, then,
clearly, the HVAC task must be signaled whenever
a change is made to that dataset.   This can be
done by defining a TRIGGER within the RDBMS.  That
can signal the task which can restart it to recheck
the criteria/conditions, etc.

So far, so good...

But, what happens if there is a flurry of activity in
the RDBMS causing repeated TRIGGERs?  I don't want
the HVAC task to be repeatedly restarted since that
represents a waste of (computing) resources.

And, I don't want the HVAC controller to take a lock
on the relevant records in the RDBMS as soon as it is
awakened since that ties up the RDBMS for some amount
of time and propagates this delay to all tasks/clients
which are trying to access the RDBMS (e.g., a "user
interface" task that may be deliberately updating
the HVAC schedule!)  Recall that those tasks may also
be foreign so such an action pushes my "needs" onto
other boxes in the "system".  <insert frown here>

One scheme is for the TRIGGER to fire a
*retriggerable*
one-shot when it happens.  And, let this one-shot
restart the HVAC controller task.  This allows any
multiple TRIGGERs to be absorbed within that time-out
without causing the HVAC task to be wastedly repeated.

But, one-shots suck  :>  And, there is no way to
pick the appropriate timeout interval without detailed
knowledge of the other clients that might be accessing
the RDBMS.  And, *any* value picked will only apply
if the RDBMS is at a particular load factor, etc.
E.g., if the RDBMS or other client hardware slow down
due to load increases, then the activity seen by the 
RDBMS as expressed in terms of TRIGGER frequency
will vary making a once appropriate timeout value
now appear "too short".

I *think* the practical solution is for the TRIGGER
to signal an event *and* restart the HVAC task if it
is NOT running.  (i.e. an interlocking set of mutexes)
So, if multiple TRIGGERs occur while the HVAC task
is running, the event ("flag") gets repeatedly set
but the HVAC task is never RE-started.

The HVAC task then, just prior to sleep()-ing or
terminating, must reexamine this event (flag).  If
it is set, it must requery the RDBMS to see if
anything "important" has changed since the conditions
that it evaluated in this most recent invocation
(obviously, it fetched all of the information it
needs from the RDBMS in a single *transaction*).
If so, it rethinks its actions, sets whatever *new*
conditions are appropriate to awaken it, CLEARS
THE EVENT (flag) and then sleep()'s.

This largely mimics how I would do this with an
embedded table -- but the mechanisms wouldn't be
nearly as expensive!  And, wouldn't have as many
things that could go *wrong* with them (e.g., what
if the RDBMS crashes during a query, etc.)

Can anyone shed some light on whether or not this
is the most "robust" approach to take?  Or, is there
an equally "thread-safe" approach that isn't quite
as heavy-handed?  Are there other non-intuitive
issues related to the RDBMS's operation that I have
overlooked?  I'm learning new respect for the issues
that DBMS staff must have to deal with routinely! :>

Thanx!
--don


      ____________________________________________________________________________________
Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/