Arthur Fuller
artful at rogers.com
Sat May 3 07:10:38 CDT 2003
To be sure, there are apps such as those you describe, where sales reps are contending for tickets or seats or whatever. The way I handled it in the concert app was this: every physical ticket was one row in a table, complete with a gate, row and seat ID. To simplify the data-entry I wrote a form that would let someone create blocks of tickets very quickly (i.e. gate 7, row 1, seats 1-50, &c.) This was necessary because the sales people would have to assign contiguous tickets where possible, and I never could come up with an algorithm that would understand the asssignment contingencies the way peple do intuitively. For example, if you don't have 4 adjacent seats but you do have two pairs -- seats 1 and 2 in rows 7 and 8 -- that will do. After that was done, a multi-select listbox was all the front end required (plus a timer that released the locks after 5 minutes unless the sale was completed). Anyway, your point is valid. That's why I went to the seat=row model. There was no other way that I could see to avoid contention. Even then there was still a possibility, so we locked the rows the moment they were selected. And we didn't have thousands of users selecting seats :-) A. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Henry Simpson Sent: May 3, 2003 3:13 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Desperately Seeking! Arthur: You did some concert booking at come point. How did you manage concurrent attempts to reserve the same seats (records?) in a hall, for transportation and hotel rooms. There are concerts where 20 - 30,000 seats are booked in a matter of hours and some kind of software layer that feeds non overlapping blocks of seats to a number of terminals must be provided by software so each booking terminal could present a range of seat selections. It seems to me that block allocation of the declining available records is an aspect of workflow that is the responsibility of the application developer. In an airline scenario, where you have thousands of agents who might be simultaneously contending for a small block of seats, is there a trivial work flow solution? I would think that the only other way to work disconnected is verify that all the record fields contain the same data at the server as at the client before committing an update or to write a lock flag to a record (with a date/time stamp) that is automatically cleared by some kind of bot application if a reservation isn't committed within a specified time span. For consistency, you could have the client pull the time from a server table (updated every few seconds?) and have the bot clear any stamps that are future times. Hen >From: "Arthur Fuller" <artful at rogers.com> >Reply-To: accessd at databaseadvisors.com >To: <accessd at databaseadvisors.com> >Subject: RE: [AccessD] Desperately Seeking! >Date: Fri, 2 May 2003 14:26:42 -0400 > ><rant> >I don't feel like rekindling any bound/unbound wars, so instead I'll >try another tack. The problem is disconnected recordsets, which are >very cool if the liklihood of simultaneous updates of a row is remote. >In fact, it's not so much a database problem as a business practices >problem, IMO. What the hell are two people updating the same row for? >There's a problem here and it >doesn't concern the database; it concerns the workflow, which by definition >is outside the specifications. OTOH, excellent arguments from the db folk >occasionally persuade management that the problem is indeed outside the db, >and should be addressed by someone other than you. ></rant> > ><reality> >Given that you must prevent simultaneous updates of a set of rows, and >given >that you have taken the unbound path, without a massive rewrite I think >your >quickest option is to revisit all the recordset declarations, setting >Pessimism TRUE in your rs.open() arguments. This effectively offloads the >problem to the db, which is good, since it won't allow your users to >overwrite each other even if your app in theory does. (That's why I always >put all the smarts into sprocs and rules and such, rather than coding them >in Access. Then, even if your app is buggy, it doesn't matter:-) Lock it >all >down with pessimistic recordsets and go from there. You can probably visit >every occurenece with a project-wide search, and paste the revised string >in. ></reality> > ><fantasy> >I'm having difficulty coming up with an example where multiple users >would want to update the same rows. A wife and husband are both phoning >to complain about a VISA charge? Three employees are calling from >Client X to revise the purchase quantities in the details of OrderID >2345? > >Multiple people updating identical rows = a workflow problem, IMO. In a >well-designed workflow, this should never occur. > >Making this point at your next meeting, you are given a 100% salary >boost, several opportunities for sex with strangers, and the pleasure >of watching the entire organization reconfigure itself to your >insights. Suddenly they appreciate how brilliant you are and shape >their enterprise around you. Billions at stake, all on your mind. ></fantasy> > >Arthur > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Marcus >Tewksbury >Sent: May 2, 2003 10:16 AM >To: accessd at databaseadvisors.com >Subject: [AccessD] Desperately Seeking! > > >Can Anyone Help Me? > >I have built a client server application using .adp front end and SQL >Server >back end. Within the application itself I use unbound forms and retrieve >records using ADO recordsets at run time. The way I have initially >deployed >the application is to copy an instance of the .adp to each desktop and run >it locally. The problem has been that people keep overwriting each other's >updates - and changes are not reflected fast enough. > >I have a couple of different thoughts on how to tackle this - either >ratchet >down the ODBC refresh rate, or run a single, centralized copy of the .adp >(which throws up some non-updateable warning every time it starts which I >don't know how to suppress). Of course, I acknowledge that I am a total >newbie, and both of these options may be flawed. > >Thanks a bunch, > >- Sherri _________________________________________________________________ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com