[AccessD] Record locking bug

John

It sounds like your are using Page Locks not Record Locks. I thought that 
Record Locking had to be set and that Page Locking was the default.

Glen


>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
>
>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