Record Locking

[rafaelc]

I guess my question is a general one (not specific).

It has to do with table or record locking in a multi-user environment.

I know that ADO recordsets provides Optimistic and Pesimistic locking which allows us to lock records in at a different time of execution. I lets us trap for collisions, simulatneous deletes, updates and so on.

For example, Client "A" and client "B" are update or deleting the same records... lets say "A" gets priority.. and "A" gets the update or delete first.... well then, "B" will have an error raised on his side and he could trap it and inform the user.

But how should it be handled in a Transaction based environment like MTS. I heard you shouldn't use the recordset object in MTS for updates or deletes because you could potetially lock several records and cause bottle necks. I know transactions handle locking, but how do you handle collisions like in my example.
If the updating or deleting scenario described above is handled in MTS component client "B" wont know about cleint "A"s changes and so client "B" will over write "A"'s changes. I've thought of some solutions that will work but I don't know if it is the convetional way of handling it. All the examples of MTS components I 've seen do an .Execute on the connection object or Command object.

If any one as any ideas or suggested reading (for scenarios like this please.. I know there's alot on ADO but in this case ADO recordsets I believe won't work.) Please let me know. Also if I'm just missing something that is obvious please let me know.

Thanks.

[monte96]

Perform your updates and deletes using Update and Delete SQL Statements and execute them using an ADO connection object.