ADO MYSQL se cae con 2 usuarios modificando mismo registro

[lafug]

Ayuda Estimados,

Tengo una aplicacion con ADO y MySql todo va bién hasta que 2 usuarios modifican el mismo registro del recordset, y ahi el programa se cae en el usuario que grabó al final.
GRACIAS de antemano por cualquier ayuda.

estoy definiedo el bloqueo como sale a continuación:

oCmd := TOLEAUTO():New("adodb.command")
oCmd :ActiveConnection(oCon)
oRS := TOleAuto():New("adodb.recordset")
oRS :CursorLocation := adUseClient
oRS :LockType := adLockOptimistic <------------ TIPO DE BLOQUEO
oRS :CursorType := adOpenKeyset
oRs:ActiveConnection(oCon)
oRs:Source := "SELECT * FROM clientes ORDER BY NomCuenta "

[nageswaragunupudi]

Update operation by a user fails if another user updated the record after the user read the recordset and before updating. This is concurrency error.

It is desirable that we should ascertain that the row is not updated by other users before calling oRs:Update() method. Normally oRs:Resync( adAffectCurrent, adResyncUnderlyingValues ) method helps us to know this. But unfortunately not all providers support this method. I very much doubt if Resync(...) is available for ADO MySql, particularly because ODBC is used.

Only remaining alternative is to use the crude TRY .. CATCH .. END. We may examine the ADO error object to make sure it is the concurrency issue. If the Update fails, ReQuery() the recordset, inform the user about the concurrency issue and offer to re-edit.

Note on:
oRS :CursorType := adOpenKeyset
When we open a recordset on the client side (this is what we should do) there is no use of assigning any values like adOpenKeyset or adOpenDynamic. Whatever we want ADO always opens the recordset with CursorType static only.

[Rick Lipkin]

Lafug and Rao

Rao .. you are VERY correct about ReSync() not being universally supported by some Databases .. like Access for example which causes difficulties in networked environments specifically when it comes to workstation updates and visibility.

As far as concurrency I use a signature field in my tables called "Updated" which I increment each time a record has been modified. Before I commit any record .. I fire off a separate recordset back to the master table looking just for the primary key ( row id ) and the "updated" field .. if the buffered up record has the same value as the the table .. I commit the record .. if the master table has a greater value than the buffered 'updated' variable .. I assume someone has slipped behind the user and made a change to the same record therefore making the record about to be saved obsolete.

In many cases ReQuery() is not an option because of how xbrowse saves bookmarks .. and issuing a Requery() and moving back to the same row in xbrowse is not possible.

Struggling with Access and workstation visibility and concurrency myself .. I would much rather use Sql Server ..

Rick Lipkin

[nageswaragunupudi]

Yes Mr Rick. I agree with you. ADO and MsSql are like made for each other.
Baring a few issues Oracle also works quite well.

There could be several strategies a programmer can adopt to handle concurrency issues, but all these involve rereading some data from the server just before writing and in all these cases one has to face the possibility of another committing a change in that minute fraction of a second unless pessimistic locking is used and row is locked. With optimistic locking, finally we need to attempt write and handle the failure finally in some way or other. Even if the probability is very low, our code should contain handling of failure.

May be, TDolphin makes these issues quite simple to handle. I don't have any experience.