jwcolby
jwcolby at colbyconsulting.com
Thu May 29 16:32:52 CDT 2008
It seems the padding I was remembering was for RECORDS, not memo fields. Sorry about that. John W. Colby www.ColbyConsulting.com jwcolby wrote: > 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 >>