[MUD-Dev] Comments on the DB layer

[clawrenc at cup.hp.com]

In <199705130341.WAA08728 at dfw-ix4.ix.netcom.com>, on 05/12/97 
   at 08:52 PM, "Jon A. Lambert" <jlsysinc at ix.netcom.com> said:

>> From: clawrenc at cup.hp.com
>>    at 10:41 PM, "Jon A. Lambert" <jlsysinc at ix.netcom.com> said: >>

...deletia...
>For "public" attributes/object attributes: 
>  SELECT column type length FROM SYSCOLUMNS WHERE TABLE = class; For
>user type attributes:
>  SELECT column foreigntable FROM SYSKEYS WHERE TABLE = class; For
>"private" attributes/class attributes and methods
>  SELECT column type length FROM ClassTable WHERE CLASS = class; For
>method code:
>  SELECT binary_blob FROM MethodTable WHERE CLASS = class AND 
>      METHOD = method; 
>For Inheritance:
>  SELECT child FROM ChildTable WHERE CLASS = class;

I'd love to get into this in greater detail -- I just don't have the
time right now.  I've flagged this mssage as one to return to.

>> >3) I can see how you would get numbers of killed mobiles by checking
>> >    how many old objects of the type were dead.  I don't see how you 
>> >    XREF with the weapons or spells, unless you store this info with
>> >    the dead mobile object or the weapon or spell object undergoes a 
>> >    state change requiring it to be stored with the same transaction 
>> >    time. 
>> >
><snip>
>> 
>> -- Iterate across the list and record the transactions which deleted
>> them.
>> 
>> -- Iterate across those transactions and list all the player objects
>> referenced by the transaction.

>These two statements lead me to suspect you are storing transactions
>as objects.  No?

Not quite.  I am in several minds here (a state which is becoming a
habit lately).  In my original model I had trasactions stored ala:

  StartTransactionMarker
  TransactionID
  OldObjectVersionID->NewObjectVersionID
  OldObjectVersionID->NewObjectVersionID
  OldObjectVersionID->NewObjectVersionID
  OldObjectVersionID->NewObjectVersionID
  EndTransactionMarker

(This being one entry in a cache flush block)

This of course means that every change to the DB gets represented by a
write to the DB.  Expensive, especially as I'm already only
periodically flushing the cache to disk.

It then becomes fairly obvious to change the cache mechanics to have
the cache internally compress the object changes between cache
flushes.  eg:

  ObjectA is loaded into the cache.
  ObjectA is changes into ObjectA'.
  Changes again into ObjectA'2.
  Changes again into ObjectA'3.
  Changes again into ObjectA'4.
  Changes again into ObjectA'5.
  Changes again into ObjectA'6.
  Changes again into ObjectA'7.
  Cache is flushed.

The entry in the log, and the physical IO to the DB would be:

  ObjectA->ObjectA'7.

With all the other components lost.  It should make for a huge
comparitive performance boost, but a lot of state data is lost.

The next idea was to intelligently store deltas as mentioned above so
I don't lose all this state data.  Again:

  ObjectA is loaded into the cache.
  ObjectA is changes into ObjectA'.
  Changes again into ObjectA'2.
  Changes again into ObjectA'3.
  Changes again into ObjectA'4.
  Changes again into ObjectA'5.
  Changes again into ObjectA'6.
  Changes again into ObjectA'7.
  Cache is flushed.

The physical IO now looks like:

  Compute what changed for ObjectA->ObjectA'
  Create a delta object containing the original values.
  Write it to the DB and "connect" it to ObjectA as the prior version.
  Compute what changed for ObjectA'->ObjectA'1
  Create a delta object containing the original values.
  Write it to the DB and "connect" it to ObjectA as the prior version.
  Repeat up to ObjectA'7.
  Update the "current" ObjectA entry with the current values.

This saves a lot of IO bandwidth, but still loses out on the number of
IOs, which is still very high.  The next idea is to do exactly the
same thing, except that instead of writing each individual delta
object to the DB, to make one master delta object for the entire cache
flush.  This master delta object would contain things like:

  ObjectA:
    AttributeXTranasactions [tranID, tranID, tranID, ...]
    AttributeXValues [value, value, value, ...]

with the two arrays mapping to indicate the attributes value as of any
transaction that affected it (or more likely some more compressed/less
redundant version)
  
Don't know how much I'd save there.

>The type of logging I am doing knows nothing about the transaction
>itself.  It can be used for transaction rollback also.  
>
>Here's an example of what I'm thinking (its likely to pose problems
>*hehe):
>
>  Bubba> drink water from canteen.
>
>Lets assume that an event is issued and during the course of its
>execution it will modify 2 objects, Bubba and the canteen.  Bubba
>will have his thirst attribute modified and the canteen will have its
>weight attribute modified. Assume the event handles the canteen first
>then Bubba.
>
>If this is successful it might be logged as follows:
>
>  tranid   
>  00001 ,  start tran
>  00001 ,  old canteen , new canteen
>  00001 ,  old bubba , new bubba
>  99999,  canteen disk commit
>  00001 ,  end tran
>  99999,  bubba disk commit
>
>The 99999 tranids come from the cache manager and may be asynchronous
>like I have shown.
>
>Now for rollback lets assume player Jon blows Bubba away before he
>can quench his thirst.
>
>  tranid   
>  00001 ,  start tran
>  00001 ,  old canteen , new canteen
>  00002 ,  old bubba , new bubba  <--- bubba dies here
>  <--- bubba's drink event fails here, cause bubba has changed state 
>  00001 ,  old bubba , new bubba ---> never logged
>  99999,  canteen disk commit  
>  00001 ,  end tran   ---> never logged
>  99999,  bubba disk commit

>Rollback would read the log backwards looking for tranid 00001,
>restoring canteen back to its original state and mark all the
>restored  objects dirty again.

Why are you letting your failed events and partial object state (ie
intermediate state of an object during an event's execution) to be
known by the DB and thus potentially the rest of the world?  This
would seem to allow logical consistancy problems:

  EventA starts
  Event A modifies ObjectA.
  EventB starts.
  Event B checks modified state of ObjectA and on that 
    basis modifies ObjectB.
  EventB commits.
  EventA fails and rolls back the change to ObjectA.

Oops.  You're maintaining internal clean/dirty flags for changes that
aren't commited yet right?  I guess that would cover it.

Aside:  Ahh.  This doesn't even begin to happen for me as events that
fail to C&C as in Bubba's drink event don't exist as transactions and
have no data to rollback (its all local and deleted when the event
fails),  The DB only ever knows about successful events.  Nobody cares
bout the rest.

>Server bootup/recovery would read the log differently, probably
>forward from the last 'syncpoint'  making sure that all objects 
>between 'start trans' and 'end trans' had 'disk commits'.  

>Does this seem workable?  This log could be certainly remain in 
>memory and be flushed out to disk at syncpoints.

...thinking...

>> I can move backwards along the player object-version line, I can
>> examine their inventory.  Heck, if I also store transaction owner's
>> with the transactions (probably a good idea), I could actually
>> recreate and watch the whole fight, blow by blow as it happened, along
>> with watching Bubba call in his Wiz friend to...).  Just roll the DB
>> back to that time, and replay.  It makes snooping a thing of the past.

>The transactions I log above are probably too generic to show what
>originated them and why.  They are just sequences of otherwise
>arbitrary state changes.  

As even user commands are events making a call to the parser object
with the entered string as an argument, I should, theoretically, be
able to animate an entire MUD replaying everything exactly as it
happened back when, including all IO etc (well, the fact of the IO and
the content of the IO if not making an actual IO down the network).

>> Odds to dollars your OS is not quite this stupid (I guess we're
>> talking Win NT here, so it actually may be pretty likely). 

>Hey, do I detect an OS bias here?  At least I'm not using a  cheap
>imitation *gurgle* ;-)

Who me?  Nahh.  I am the very model of a modern major MS-bigot. 
<tadah!>  I *like* OO environments.

>> Re-opening the file every IO and then closing it to keep everybody in
>> sync is pathetically expensive.  The standard solution is to run file
>> IO's thru a dup()'ed or dup2()'ed handle.  
>> 
>Yep, the trusty mainframe will let you thrash the hell out of it with
> re-opens too.  

><code snippit snipped>

>This might work if close() doesn't attempt to free handles/buffers. 
>I will have to test it.  Thanks for the idea.

It works as a general form under DOS, Unix (cf Tanenbaum), and OS/2. 
Of course MS may have broken forgotten to break this too -- you can
always hope.

--
J C Lawrence                           Internet: claw at null.net
(Contractor)                           Internet: coder at ibm.net
---------------(*)               Internet: clawrenc at cup.hp.com
...Honorary Member Clan McFUD -- Teamer's Avenging Monolith...