[AccessD] Redesign the problem to fit the solution

Jim Dettman jimdettman at verizon.net
Fri May 30 08:49:45 CDT 2008


John,

  What I know of JET comes from many sources:

1. Understanding JET locking ver 2.0 by Kevin Collins
2. Understanding JET locking ver 3.0/3.5 by Kevin Collins
3. Microsoft Jet 3.5 Performance Overview and Optimization Techniques by
Kevin Collins
4. Some of the white papers on replication (don't remember which ones, just
that I gleaned a few facts out of them).
5. Access Developers Handbook series published by Sybex.
6. Conversations I had with Kevin and members of the Microsoft product
support team while I was a sysop in the MS Access forum on CompuServe.

  Where I learned of the long value page structure and storage of memo
fields I cannot point to specifically and it may be that I am not
remembering it correctly either.  It's been quite a few years since I've
bothered to learn about the details of what goes on inside JET.

  At one point, shortly after JET 2.0 came out, I had the idea of building
optimization utilities and tools for JET and tried to learn everything I
could about it.  Time never permitted me to play, so eventually I dropped
the idea.  But I did find out a lot about the internal workings of JET in
the process.

  But without solid documentation from Microsoft, it's hard to say what is
really going on within a  long value page. And certainly there could have
been changes made over the years.  In fact, now that I say that, I vaguely
remember something about LVP's and locking being changed in JET 3.5 to help
with page recycling, but I don't remember exactly what.  So there you go;
LVPs were locked in some way in the past (memory is a funny thing).  

 I suppose you could spend some time and test various scenarios to find out,
but I certainly don't have the time or energy to do so any more.  JET as far
as I'm concerned is pretty much dead.  I still use it, but only casually, so
I am not as concerned about the nitty gritty details any more.  Now a days,
I pretty much use SQL Server.

Jim.



 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, May 29, 2008 6:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Redesign the problem to fit the solution

Where do you find this information about memos?  My reading 
(and it was many months ago so I can't lay my hands on it 
either) indicated that it a memo page can in fact hold 
pieces of memos in different records.

You are of course right, I went out and looked up the 
padding thing, it was for record page locking.

John W. Colby
www.ColbyConsulting.com


Jim Dettman wrote:
> JC,
> 
> <<Not entirely true.  The lock is (or may be) only one record 
> BUT it may also lock multiple entire pages of memo storage 
> as well as multiple entire pages of Index storage.>>
> 
>   I've never put it to the test, but it's always been my understanding
that
> a long value page can only be linked to one memo field.  The only
exception
> to that is a memo field only taking up a small number of characters (I
> believe it's 40 in the current version of JET), which would then be stored
> with the "fixed" portion of the record and no separate memo page would be
> used.
> 
>   As far as indexes, yes, operations on them are still done at the page
> level, so you can tie up an index, but an index is only updated once a
> record is saved.  So the issue of locking a record and then walking away
has
> no bearing on that.  You don't lock all the indexes involved with a table
> while your sitting editing a record.
> 
> <<Those page locks can lock other records, and they don't even 
> have to be in the same table, though they might be.>>
> 
>   A page can only belong to one table.  Pages never cross tables.
> 
> <<Assume that the presence of a memo field locks one 4K page 
> of "memo storage" out in the BE.  Assume that there are 20 
> records with memo fields where pieces of their memo data in 
> in that 4K page.  You have just locked 40 records.
> 
> AFAICT Jet does not guarantee that the memo data in a memo 
> field is stored contiguously in the memo storage space, so 
> your memo field might have data scattered over many pages, 
> potentially locking multiple pages.>>
> 
>   Hum don't think so as the long value pages are handled totally different
> then regular pages.  It's my understanding that the memos are a chain and
a
> long value page can belong to only one record. There is a 14 or 16 byte
> pointer (depending on JET version) in the fixed record that points to the
> start of the chain.
> 
>  If this were not the case and it is as you describe, then it would become
> next to impossible for any type of editing to occur for any record as you
> would need to lock not only the fixed portion of the record, but also lock
> every page where part of a memo field existed.  If I had 4 or 5 large memo
> fields in a record, they could easily then end up broken over hundreds of
> pages.  That would be a tremendous amount of overhead to deal with and
with
> anything more then a few users in the same table, would make it next to
> impossible for anyone to be granted an edit lock.
> 
> <<I have actually seen discussions of code to make sure that 
> your memo is "padded" such that it fills up the memo page, 
> thus ensuring that the pages locked for your memo can only 
> lock your record.  Of course this has bad side effects such 
> as using huge amounts of space if the real memo data is 
> actually small.>>
> 
>   I think your remembering that wrong.  This was a common technique to
> handle the lack of record level locking before JET 4.0.  You just added
some
> text fields that were filled so the record size exceeded half the page
size.
> This made it impossible for more then one record to fit on a page and thus
> gave you "record level" locking.
> 
>   I've never heard anyone trying to pad a memo page and I don't see how
you
> could as you have absolutely no control over that process.  You can only
do
> it with the fixed portion of the record because you know the number of
> fields and the types involved, and the amount of overhead on the page.
> 
> Jim.
> 
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, May 29, 2008 5:12 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Redesign the problem to fit the solution
> 
> Jim,
> 
>  >   And I would agree that bound forms do hold a recordset 
> open, but the affects of that can be limited by using a 
> bound form that is bound to only one record at a time and 
> not an entire table.
> 
> Not entirely true.  The lock is (or may be) only one record 
> BUT it may also lock multiple entire pages of memo storage 
> as well as multiple entire pages of Index storage.
> 
> Those page locks can lock other records, and they don't even 
> have to be in the same table, though they might be.
> 
> Assume that the presence of a memo field locks one 4K page 
> of "memo storage" out in the BE.  Assume that there are 20 
> records with memo fields where pieces of their memo data in 
> in that 4K page.  You have just locked 40 records.
> 
> AFAICT Jet does not guarantee that the memo data in a memo 
> field is stored contiguously in the memo storage space, so 
> your memo field might have data scattered over many pages, 
> potentially locking multiple pages.
> 
> I have never seen that specific aspect of the issue 
> discussed anywhere by anybody with real factual answers.
> 
> The point is simply that:
> 
> 1) Memo fields DO store their data out in an extended 
> storage area of the MDB.
> 2) These pages are not locked at the record level, the data 
> is not in the record!  They lock PAGES.
> 3) Multiple memo fields can store pieces of their data in 
> the same page.
> 4) All records containing memo data in a locked page are 
> themselves locked.
> 
> I have actually seen discussions of code to make sure that 
> your memo is "padded" such that it fills up the memo page, 
> thus ensuring that the pages locked for your memo can only 
> lock your record.  Of course this has bad side effects such 
> as using huge amounts of space if the real memo data is 
> actually small.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Jim Dettman wrote:
>> Charlotte,
>>
>>   Yes, that's correct.  No locks is no "Edit locks", so the record
doesn't
>> get locked until you go to update it.
>>
>>   And I would agree that bound forms do hold a recordset open, but the
>> affects of that can be limited by using a bound form that is bound to
only
>> one record at a time and not an entire table.
>>
>> Jim.
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
>> Sent: Thursday, May 29, 2008 4:17 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Redesign the problem to fit the solution
>>
>> Actually IIRC, unless things have changed, NoLocks means that write
>> locks are not applied until the *update* starts. 
>>
>> Charlotte Foust
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Thursday, May 29, 2008 1:07 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Redesign the problem to fit the solution
>>
>> Max,
>>
>> In fact it is not reinventing the wheel.  Bound forms hold recordsets
>> open.  Open recordsets cause write locks as soon as the edit begins.
>>
>> What I am discussing is a system that does not hold recordsets open.
>>
>> Did you know that "bound" combos and lists also hold recordsets open?
>> each and every combo which uses a table or query opens a connection to
>> the BE and holds it open.  I just finished creating a generic callback
>> that eliminates that by caching the data in the table.  This has
>> limitations in that it does not work well with frequently changing data
>> but if you have data that you are willing to cache, it eliminates that
>> connection to the BE.
>>
>> I do not have any solid data yet but I do know that in most cases the
>> second and subsequent times that you open a combo that uses my callback,
>> the combo loads MUCH faster than if it has to get the data from the BE.
>> This is of course off topic, but I am starting to examine some issues
>> that in certain instances can significantly speed up operation of the
>> FE, or solve other specific problems.
>>
>> That is not why I am looking at the unbound form, but an unbound form
>> implemented as discussed below will eliminate the "modify" lock that
>> occurs when a user starts to modify a record and then goes outside to
>> smoke a cigarette.  These locks can be quite troublesome in an MDE BE
>> under specific circumstances.  This unbound form is just another tool,
>> but it is MUCH less useful if it has to be custom engineered for each
>> place you want an unbound form.
>>
>> This read / modify / inspect / write stuff is a CLASS of problem.  It
>> needs a solution for the problem CLASS.
>>
>> I am a bound form user, I like them and if they do not cause issues I
>> intend to use them as long as I use Access.  That said I do not have a
>> usable unbound form tool in my toolbox, for those cases where it can
>> solve specific problems.
>>
>> And yes, I have selected "edited record" in the properties. 
>>   If you have read up on this you will know that Jet's implementation is
>> less than stellar, AND it completely ignores the page lock issue.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Max Wanadoo wrote:
>>> John,
>>> Not wishing to add fuel to any embers that may be smoldering, but all 
>>> of this is just re-inventing the wheel.  Access does all of this and 
>>> lots more in a Bound Form.  As I understood it you just had a problem 
>>> with locking spanned unconnected records.  I think that between what 
>>> Gustav and others have said you have a solution - keep the bound form 
>>> with all its *features*
>>> - extract the memo field to a separate table.  Perform a pseudo lock 
>>> on that if it does not lock to your satisfaction.
>>>
>>> BTW, I am assuming in all this that you have selected "Edited Record" 
>>> as the "Default record option" in the database options window under
>> the "Advanced"
>>> Tab.
>>>
>>> Max
>>>
>>>  
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>>> Sent: Thursday, May 29, 2008 8:24 PM
>>> To: Access Developers discussion and problem solving
>>> Subject: Re: [AccessD] Redesign the problem to fit the solution
>>>
>>> Charlotte,
>>>
>>> If and when I get around to this kind of solution I would:
>>>
>>> 1) Create a data store for the data coming from the record to
>> manipulate.
>>> Call this Original Read.
>>> 2) Create a system for automatically matching field to control.  In my
>>> mind, probably a control naming convention such that the field name is
>>> embedded in the control name?
>>> Something like that.
>>> 3) Read the data from the data store into unbound controls, leaving 
>>> the original record untouched.
>>> 4) Allow the user to edit away.
>>> 5) Create some method to allow the user to signal "edit complete.
>>> 6) Compare original data to control data.  IF any changes were made
>> then...
>>> 7) Pull the same data record from the table into a NEW data store.  
>>> Call this Compare Read.
>>> 8) Compare Original Read data to Compare Read data to discover if any 
>>> data was edited.  IF NOT then LOCK the record in the table at this 
>>> point.  If NO field collisions between Compare data and Modified Data 
>>> (form) then LOCK the record in the table at this point/
>>> 9) Create a third data record.  Call this Write Data.
>>> 10) Copy Compare Read to Write Data.
>>> 11) Update fields with modified data from the controls
>>> 12) Write the Write Data back to the table, releasing the lock
>>>
>>> If there were edits between Original Read and Compare Read AND the 
>>> modified fields collide with Compare read fields THEN error handle.  
>>> The error handler would need further thought.  Notify the user and
>> allow overwrite?
>>> Notify the user and trash changes?  Notify the user and store in some 
>>> temp location for conflict resolution?
>>>
>>> For a generic solution there would have to be system specific rules, 
>>> i.e. in this system we want the users to be told and made to reenter 
>>> the data (trash changes).
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com
>>>
>>>
>>> Charlotte Foust wrote:
>>>> Then I have to point out AGAIN that the demo I suggested does exactly
>>>> what you wanted, but only for a single "table" and with a predesigned
>>>> field layout in the UI.  Allows you to edit/add/delete records in an 
>>>> unbound form.  It does NOT allow you to do it for any record in any
>>>> table, that is an exercise left for the student. ;->   You would
>>>> probably want to use a grid if you wanted to handle "any table" and 
>>>> define the columns on the fly based on an ado recordset.
>>>>
>>>> Charlotte Foust
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list