ASP, ADO, Access 2000, client cursors, adlockpessimistic issue.

<i><b>Originally posted by : Mark Rodrigo (mark.rodrigo@towill.com)</b></i><br />Hello all:<br /><br />I am using ADO within ASP to update my Access 2000 db for a multi-user application,<br />and understand that with such a client side cursor that one cannot use pessimistic locking.<br />I believe this actually becomes an optomistic lock. For this application, I would like to lock-out <br />the current record(s) during an update. I would settle for a subsequent user attempting to<br />update the current record after the first user saves the changes(update) being warned of a <br />a change having already been issued as they try to update. Any ideas on this one? Anyone<br />using something like rs.properties("Update Criteria") = asCriteriaAllCols for this to<br />compare for differences in record values?<br /><br />Thanks so much<br /><br />Mark <br />

<i><b>Originally posted by : Mark Rodrigo (mark.rodrigo@towill.com)</b></i><br />Hello all:<br /><br />Yes, be pessimistic about pessimistic locking via ASP and ADO to an Access 2000 DB. I <br />have had little or no success in "locking" a record the way all literature has described it.<br />To lock a record, you should only have to change a value in a recordset for one field to <br />create the lock, and upon Update the lock should be removed. This does not seem to work.<br />I have successfully Updated the field from another machine when it was supposed to have <br />been locked. What I suggest is not using pessimistic locking, but pursue the following<br />theoretical second method provided by Peter Scribner(peter@esurety.com):<br /><br />"I got out my Wrox Press book "Professional ADO RDS Programming with ASP" and I'll quote: <br /><br /> 'Pessimistic locking ensure that the records you are sitting on cannot be updated by anyone else, until you release them." <br /><br />That seems pretty clear. The question then is: "When does the lock become effective?". The answer is 'as soon as possible'. Not much help there. <br />But let's assume for a minute that the lock attempt is made between the Update command and the change actually hitting the database - I figure that's<br />approximately 9.2 nanoseconds. <br />If the lock is successful the database updates. If it isn't successful then the lock retries until it either becomes sucessful or hits the number of tries<br />allowed at which point, presumably, it fails. The other scenario could be the record locks as soon as editing begins and is released when the related<br />Update completes. Either way the longevity of the lock has to be pretty short after which a subsequent update would be allowed. So if I open a<br />recordset and leave my station for 5 minutes and during that five minutes another user updates the record, what do I see on my screen when I get<br />back? Gotta be the original set of data, right? So a third scenario might be that the lock takes place when the first user fetches the recordset. But<br />then he goes to lunch and nobody else in the place can do squat because he's got a 2,000,000 item recordset locked up. Bad juju either way and<br />I'm afraid I don't know enough to provide the answer to just how it works. <br /><br />Wrox's book also suggests that one can use an optimistic lock and include code that detects changes to the 'underlying' value. The underlying value<br />is the fvalue that 'was/is' in the field and it is possible to check through code whether the underlying value is still the curretn value. If it is then the<br />record hasn't changed; if it isn't then another user has changed the record between the time you read and the data and now. Through such a<br />comparison your code disallow a '2nd' update that is no longer based on a current record. <br /><br />I haven't seen anything that suggests that a user can be prevented from seeing or attempting to update records while another user is in that process<br />although that's the implicit promise of the pessimistic lock. <br /><br />Lot of words, Mark, to say that I can't answer your question either from empirical tests or a more precise explanation of how the pessimistic lock<br />works. Sorry. <br /><br />The second method above seems almost more promising to me because you can prove it. Rather than try to go through the specifics of that code<br />here and because it's related to a bunch of other things I think I need to suggest that you should get your hands on the Wrox book."<br /><br />Best of luck<br /><br />Mark<br /><br /><br />------------<br />Mark Rodrigo at 12/6/2000 6:56:15 PM<br /><br />Hello all:<br /><br />I am using ADO within ASP to update my Access 2000 db for a multi-user application,<br />and understand that with such a client side cursor that one cannot use pessimistic locking.<br />I believe this actually becomes an optomistic lock. For this application, I would like to lock-out <br />the current record(s) during an update. I would settle for a subsequent user attempting to<br />update the current record after the first user saves the changes(update) being warned of a <br />a change having already been issued as they try to update. Any ideas on this one? Anyone<br />using something like rs.properties("Update Criteria") = asCriteriaAllCols for this to<br />compare for differences in record values?<br /><br />Thanks so much<br /><br />Mark <br />