Jim Dettman
jimdettman at verizon.net
Thu May 29 17:02:01 CDT 2008
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