jwcolby
jwcolby at colbyconsulting.com
Fri May 30 08:59:59 CDT 2008
I am writing a little test suite to test this whole area. Should be a few more hours of coding then results will start popping out. 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 >>>