Jürgen Welz
jwelz at hotmail.com
Fri Jun 4 22:19:38 CDT 2004
A2K, Tools menu, Options, Advanced Tab: check 'Open databases using record-level locking'. This results in Access placing the records 1 to a data page resulting in significant bloat with small records. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "jwcolby" <jwcolby at colbyconsulting.com> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "AccessD" <AccessD at databaseadvisors.com> >Subject: [AccessD] Record locking bug >Date: Fri, 4 Jun 2004 22:37:39 -0400 >MIME-Version: 1.0 >Received: from mc3-f34.hotmail.com ([64.4.50.170]) by mc3-s14.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Fri, 4 Jun 2004 19:41:28 -0700 >Received: from databaseadvisors.com ([209.135.140.44]) by >mc3-f34.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Fri, 4 Jun 2004 >19:41:09 -0700 >Received: from databaseadvisors.com (databaseadvisors.com >[209.135.140.44])by databaseadvisors.com (8.11.6/8.11.6) with ESMTP id >i552bhQ03506;Fri, 4 Jun 2004 21:37:43 -0500 >Received: from ColbyConsulting.com (svr5.tokios.com [69.41.224.26])by >databaseadvisors.com (8.11.6/8.11.6) with ESMTP id i552bXQ03410for ><AccessD at databaseadvisors.com>; Fri, 4 Jun 2004 21:37:33 -0500 >Received: from colbyws [67.86.211.79] by ColbyConsulting.com with >ESMTP(SMTPD32-7.15) id A1ECA40134; Fri, 04 Jun 2004 21:37:32 -0500 >X-Message-Info: NDMZeIBu+sr7DfMTWP42/0050WvlLgKmQQvmdm1pSeQ= >Message-ID: <000001c44aa6$1224ba80$7e01a8c0 at colbyws> >X-MSMail-Priority: Normal >X-Mailer: Microsoft Outlook, Build 10.0.6626 >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 >X-MIME-Autoconverted: from quoted-printable to 8bit by databaseadvisors.com >idi552bXQ03410 >X-BeenThere: accessd at databaseadvisors.com >X-Mailman-Version: 2.1.4 >Precedence: list >List-Id: Access Developers discussion and problem >solving<accessd.databaseadvisors.com> >List-Help: <mailto:accessd-request at databaseadvisors.com?subject=help> >List-Post: <mailto:accessd at databaseadvisors.com> >List-Subscribe: ><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=subscribe> >List-Archive: <http://databaseadvisors.com/pipermail/accessd> >List-Unsubscribe: ><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=unsubscribe> >Errors-To: accessd-bounces at databaseadvisors.com >Return-Path: accessd-bounces at databaseadvisors.com >X-OriginalArrivalTime: 05 Jun 2004 02:41:09.0490 (UTC) >FILETIME=[8EDA1520:01C44AA6] > >I think I have stumbled on a record locking bug. > >For an Insurance company call center, I have built a system of events. >Events happen to a claim, the claim is received, it is pended (waiting for >action), opened, closed, phone calls made etc. These events may or may not >cause a change in status. The receive event causes a received status, the >Open event causes an Open status. A Phone cause does not cause a status >change. If the claim is in an open status then it remains in an open >status >etc. > >The managers can define the events that can occur, whether they cause a >status change etc. Via a state machine, I look at the current status and >enable specific events. Obviously if a claim is in the open status, then >it >can have many different events occur, but it cannot have a received event >occur because it was already received and can only be received once. If a >claim is denied, it can be appealed, but it cannot be closed, because it >was >never opened. > >So... There are many tables. There is a claim table ad a claim event >table. >The claim event is where we log events happening to the claim. It is in >this table specifically that I am noticing the locking issue. When I >designed this db I ported existing data from thousands of claims. The old >system didn't have an event table, it had a bunch of fields such as >openedDate1, closedDate1, openedDate2, closedDate2 etc. Hmmmm... > >Anyway, in order to port the data I did my best to figure out how these >fields mapped to events and created a set sequence of: > >Received >Pended > >Then Opened or denied depending on what was in these fields. Thus each old >claim would have at least 3 or 4 or more events to indicate that it was >received, that it was opened, closed, possibly opened again, possibly >closed >again etc. > >OK... With that groundwork... > >I have discovered that if I build a little query that pulls all the events >for claim 800: > >EventID >ClaimID >SomeField > >And another identical query pulling all events for claim 801 (actually >about >10 claims on either side of the claim)... > >It certainly appears that it is locking the block that the event records >are >built in. When I start an edit and don't save, other events with PKs close >to that event are also locked. > >I have pulled just the table of events into a new db, and designed these >identical queries and I am seeing the same thing. I am convinced it is a >bug. I see it on my dev machine at the client (Win2K A2K), my dev machine >at home (Win2K, A2K or AXP), but not on another machine running OfficeXP at >the client. > >Truly bizarre. > >I cut most of the fields out of the record including the only memo, both to >drop the size of the test db as well as to see if any of these other fields >were "causing" the problems. No help, same symptoms. > >The current db is just that one table and the two queries and is absolutely >reproducible on my machine here at my home office, running Win2K A2K SP3 or >Axp > >I have been under the impression that Access no longer locks an entire >block, but will lock just the individual record. This testing seems to >indicate that is not true. > >I have this db available for anyone who would like to look at this. My >users have complained of locking issues and I have been unable to explain >the goings on given my understanding that A2K and above locked just the >edited record. Perhaps this is not always true? > >John W. Colby >www.ColbyConsulting.com > > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ Add photos to your e-mail with MSN Premium. Get 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines