jwcolby
jwcolby at colbyconsulting.com
Thu May 29 16:12:13 CDT 2008
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 >