Can someone please help me with the following problem. I
have developed a multi-user web based system that deals
with groups of data, the data that makes up a group maps
directly to a set of rows in a SQL Server 2K db. Now, the
data in each group is related so each group needs to be
protected from multi user updates. The approach I have
taken here is to introduce a ROWVERSION column to the
group data table.

Architecturally, there are a number of VB COM+ components
that work against the db, one of these components--
GroupController--is responsible for maintaining and
updating the data in the group table. Access and
modifications to data in the db is carried out via stored
procedures by making use of ADO.

Now in terms of the group table it is imperative that if
any row within a given group--remember that a group is a
collection of 1 or more rows--is modified, then an update
statement must be terminated an appropriate error
displayed to the user informing them that the underlying
data has been modified. When a user navigates to the
group page the HTML for the page has embedded in it the
ROWVERSIONs of each row that makes up the group that was
pulled out of the db. The user can modify one or more
such rows and the GroupController resolves any possible
updates against stale data by calling a stored proc--that
uses UPDLOCKs--on the db to retrieve the rows that make up
a given group, and then compares the ROWVERSIONs of the
groups rows on the db to those sent from the browser. Any
inconsistencies halt any further processing. The trouble
is that this strategy works for 2 concurrent users
attempting to update the data within the same group--i.e.,
one user is notified that their update failed because the
underlying data has been modified. However, this strategy
fails for 3 or more concurrent users. The failure is
rather unexpected in that the database does not actually
get cross contaminated results (which is a good thing),
however, the comparison of the ROWVERSIONs within
GroupController indicates that the ROWVERSIONs are the
same for all concerned users but one, i.e., if 5 users
simultaneously attempt the operation on the same data, 1
will successfully update the db, 3 will have their updates
rejected but no message is displayed, while 1 will have
its update rejected together with an informative message.
Logging results to the event viewer shows that 4 of the
users ROWVERSIONS matched those on the db, while 1 has a
mismatch.

If the default COM+ isolation level is SERIALIZABLE
together with having a get stored procedure that makes use
of UPDLOCKs wouldn't this strategy ensure that only 1 of
the calls will return a record-set of the group rows while
the other requests will wait until the UPDLOCK is released
or a timeout occurs (we have established that no timeout
takes place via extensive logging)? If this is the case,
an UPDATE would have resulted in one or more ROWVERSIONs
being updated and so being inconsistent with what would be
on the browsers. Does anyone know what is going wrong
with this approach? Remember that the ROWVERSION compares
are done in the VB COM+ component not on the db.