ADO batch optimistic and record locking

[formerTexan]

Hello folks,

Am working with A2K and ADO 2.5 version in a FE/BE multiuser setting. I was somewhat mortified to discover that setting an ADO recordset's lock type to Pessimistic simply defaults to BatchOptimistic. This apparently holds for both server and client side connections. So i have several related questions.

1. Am I correct in thinking that the default connection is Server-side, rather than Client-side?

2. With Batch Optimistic locking, is the default connection still Server side?

3. Must I always use the batch update method with "Pessimistic" or Batch Optimistic locking?

4. With Batch Optimistic locking, at what point is the record locked and then unlocked? I am guessing not until the Batch Update method is called.

SO pessimistic locking is offered, but apparently not actually provided. Therefore, how do I reliably give a user exclusive use of a BE table or record while retrieving and updating a record (in this case updating a invoice number counter).

Thanks in advance,
Bill

 

[jsteph]

I would use a transaction, and make your updates in sql via SP or pass-thru queries. I *never* use ADO recordsets for updating. Ever.
--Jim

 

[formerTexan]

Thanks Jim,

Would you care to elaborate on why "Never". Is ADO that unpredictable in terms of its recordsets? Before I posted the question, I had a review of a couple of books and a dozen Google hits. There is certainly a lot of happy gloss on the subject and a noticeable lack of warnings (at least where I looked).

Thanks,
Bill

 

[jsteph]

Bill,
It's not ADO, it's the updateable recordsets that I frown upon. I should have included using the ADO.Command to .Execute the sql, in fact that's the mechanism I use most--I mentioned the pass-thru because that's the quickest (in terms of coding time) way to get things going.

But updateable recordsets are slow, and you have less control. You may have many users out there with recordsets bound to forms (using the data control in VB, for example), and there are locks happening that may not be necessary (for instance, the user is just browsing), and there's a lot of unnecessary traffic going on that isn't needed.

My preferred method is to use an ADO 'firehose' cursor recordset (readonly, forwardonly). Let's say you have a code module and you're doing some logic in a loop that may or may not update/delete/insert based on some logic. You loop through the readonly recordset, and update via an ADO.command object, using the record key obtained from the recordset. The recordset is much, much faster, and the updates are much faster, and you have more control, and mainly you're not holding locks that may never be used.

So, when you had said 'multiuser', I thought I'd point out that what's easier to code may be fine for a small situation with just a few users, but having a lot of pessimistic recordsets open for many, many users can cause contention problems. In my opinion having updateable recordsets all over the place is too much for the server to keep track of when you get into the dozens or hundreds of users.
--Jim

 

[formerTexan]

Jim,

Thank you for expanding on that.

I think I see a corellary in that PK's will need to be generated locally (on the user's machine) to reduce traffic. Hence I guess a GUID will be needed. And a change in PK data type from numeric to string. Hmmm, glad I am not too deep into this project.

Thanks again,
Bill

 

[jsteph]

Bill,
I'm not sure what you mean by the PK should be generated locally, or the need for a GUID. If you're doing some sort of replication, then yes you'll need a GUID, but I believe sql-server has a mechanism for that.

As far as the PK, if it's a meaningful key, ie not a random or autogenerated number--then yes, the user might pick one out as he enters a new record, but for Identity columns which are a common PK for sql server tables, that's generated on the server and you can retrieve it for local use if needed as part of a stored procedure that does the insert.
--Jim

 

[formerTexan]

Jim,

The use of Access's indigenous autonumbers for PK's is precluded for a number of reasons.

Generating the PK's from the "server" (BE) seems problematic. IF I use something like an incrementing number table for PK's, then I foresee extra network traffic, and concurrency conflicts since the table will need to be locked for the duration of some transactions while a series of PK's is retrieved and updated from the table.

Perhaps I have tunnel vision here, but that leaves using a GUID for the PK and that might as well be generated from the client side rather than the server.

Am I missing anything crucial here?

Cheers,
Bill

 

[jsteph]

Bill,
I had been under the assumption that you were using a SQL database for the backend. If it's Access, then you're on the right track that the Autonumber is generally frowned upon. However, I'm not sure what Access has natively that corresponds to a GUID, except for it's replication GUID, which, in my opinion, is even more frowned upon, especially if you're just using it as a means to generate local keys that won't conflict globally.

If the inserts are one at a time (which is what I assume with an Invoice number), as opposed to a large batch, and this is an Access backend, then using you're own seed table on the backend is what I'd reccomend, and I'd use DAO to access it. There are some faq's here detailing different methods, but with DAO there are recordset options that reliably lock the seed table. You'd create a function that attempts to lock the table, (and this should have code that traps the 'in use' error and waits a few milliseconds and tries again) and then increments the number, unlocks the table, and returns the number.

Yes, this will result in some added traffic, but in my opinion it's a cleaner solution than, say, prefixing a local front-end's seed table with, say a computername or some random prefix number, which would be the 'local' option.
--Jim

 

[formerTexan]

Thanks again Jim,

Yes, it is an Access BE and since there is a good possibility of migrating to a true server BE in a few years, I have promised to stick to ADO code as much as possible.

I have used a Base 36 generated string before as a PK and feel confident about its functioning. And it can be generated from the local machine. The only drag is that it results in strings for a PK and the last time I tried an ad hoc test, there was about a 50% deterioration in performance compared to using a numeric PK. Compromises, compromises.

I appreciate the feedback on the recordset types and have made some changes accordingly.

Stay well,
Bill