Which LockType (ADO)?

[vbGISguy]

We are writing a desktop app (VB6) that connects to SQL 7.0 via LAN. The program actually queries the remote SQL database to extract the next most "important" record using an ADO control. I am using a client side cursor and would like for the queried record to be locked so that the 10 other users of the app who are performing the same job, do not query and edit the same "important" record and wreak havoc on the database. Which LockType should I use?

Have I neglected to list any parameters here for proper diagnosis?

I would like to get a little more information about record locking and which LockType to use under different circumstances...all suggestions for more information about it (forums, books, etc.) are welcomed and appreciated. :)

thanks,
jason

[Dr_Evil]

This would be the best LockType for your requirements...
adLockPessimistic - Pessimistic locking, record by record — the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.

[Clunietp]

You'll also want to use a server side cursor along with the Pessimistic locking scheme. If you only have approx. 10 users then the performance won't be affected too much

[vbGISguy]

I was hoping you wouldn't say that. I changed the LockType to adLockPessimistic and the CursorLocation to adUseServer and had some problems with it. First, there was a -major- (10 seconds to 10 minutes for a form to refresh) performance issue. Secondly, the .RecordCount method no longer worked using a server side cursor.

These two problems however, are in IMHO likely symptoms of the low-end PC we are using to simulate the database cluster for testing this app. But this is just what I think...

Any suggestions or tips on why I am having problems with server side cursors?

Thanks,
Jason

[Clunietp]

10 minutes for a form to refresh? EEK

Increase your CacheSize to 100 or more (the default is 1). Also make sure you have a limited selection of records...

I just bound an ADO data control with a data grid to an SQL 7 table, using a server side keyset cursor with pessimistic locking, and also a cachesize of 1. The performance is just dandy.....But I have 2 450MHz machines over a 100Mbit LAN for my client and server......

[vbGISguy]

We created a few "bots" to represent user's hitting the database for random amounts of time - creating random transactions associated with each ticket/record.

When we fired off 6-8 of the bots at the same time (to simulate workers using the app) - using an adOpenDynamic, server side cursor with locktype adLockPessimistic, some of the "bots" were accessing the same tickets! The bots use programmatic connections to the database not ADO controls. What gives?

**SNIP
rstNextTicket.CursorLocation = adUseServer
rstNextTicket.Open strGetTicketNum, cnULTRA_tix, adOpenDynamic, adLockPessimistic
**SNIP

If 2 "bots" are started at the exact same moment or by some other means become synchronized, how does adLockPessimistic disallow one or the other from accessing the same record? This is probably an easy question, but somehow our robots are working on the same ticket.

Any suggestions appreciated,
Jason

[Clunietp]

by "working", do you mean editing? The record will not become locked until a user begins to edit the record