Hi all,
 
I am writing a (C++) COM+ application, with a view to supporting a wide range of different databases.  I am implementing my persistence code using ODBC.  However, I am having trouble with transaction isolation.  The scenario is something like this.  I have a class, MyClass, containing a method, MyMethod, which should run in a distributed transaction.  The method looks something like this:
 
CMyClass::MyMethod(BSTR key)
{
    struct StateInfo state;
    GetState(key, &state);    // SELECT * FROM StateTable WHERE Key = key
    
    // Perform logic, updating contents of state
 
    SaveState(key, state);    // UPDATE StateTable SET ... WHERE Key = key
}
 
Here "key" is some unique identifier for an object's state.  The trouble is that (due to the object-per-client model), a second object could call MyMethod for the same logical object (same key), between GetState and SaveState, thereby losing the update made by the first call.  Clearly I need to lock the row in question.  What is the best way to do this?  Ideally, I would like to do so in a database independant way.
 
All the best,
 
Bob.

There no way to lock a table row that is dbms independant, except UPDATE.
I think the only way is:
UPDATE TableName
SET Key = Key
WHERE Key = 'XYZ'

SELECT FROM TableName WHERE Key = 'XYZ';

UPDATE ...

This works in every DBMS, the only constraint you must have is that the method is inside a transaction and not in AUTOCOMMIT mode.
Regards
Alessandro