[AccessD] Redesign the problem to fit the solution

jwcolby jwcolby at colbyconsulting.com
Fri May 30 08:58:49 CDT 2008


 >Now a days, I pretty much use SQL Server.

LOL, a good strategy where you can.  Unfortunately I still 
deal with companies firmly wedded to their MDB BEs.

John W. Colby
www.ColbyConsulting.com


Jim Dettman wrote:
> 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
>>>



More information about the AccessD mailing list