COM+ Transaction isolation

[rea123]

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.

 

[AlessandroR]

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