More On Mysterious Data Loss In Mssql2k - A Possible Solution?

Hi All,

Further to my previous long-winded question about a situation in which
we appear to be mysteriously losing data from our mssql2k server.

We discovered an update statement, in the stored procedure we believe
is at fault, after which no error check was being performed.

Under certain conditions, this update is fired against the same record
in the same table as the immediately preceding update statement within
the transaction. We are now suspecting that under some circumstances,
these two updates get into a locking conflict that is eventually
forcing the transaction to be rolled back.

However, I'm still left with three questions.

1) Where an update in a transaction gets locked, and an error isn't
tested immediately afterwards (ie no 'IF @@Error<>0' test is made),
would the transaction proceed as normal?

2) Most critically, would statements in the stored procedure that
appear after the COMMIT TRAN statement also be executed, even if an
unresolved lock existed within the transaction?

3) Assuming that (2) does happen, would a SELECT made on another
connection with a 'WITH(NOLOCK)' locking hint be able to see the
changes made in the locked transaction even if the server is set to
READ COMMITTED, and the SELECT takes place some time after the COMMIT
TRAN is issued? More to the point, given (2), how long would the
locked transaction survive before being rolled back after the COMMIT
TRAN has been issued? Is it possible that the COMMIT TRAN takes place,
the transaction is flagged for potential rollback while a lock
resolution is attempted, the stored procedure exists as though
everything was fine, a subsequent SELECT (ie performed as one of the
next operations in the same application) using WITH(NOLOCK) 'sees' the
changes made by the transaction, reinforcing the impression that the
transaction succeeded, and then at some point thereafter the lock is
determined to be unresolvable and the transaction is rolled back,
making it seem as though the data disappeared, even though it had been
SELECTable via a different connection to the server?

Thanks, by the way, to Simon and Erland for your advice on my previous
questions about this problem.

Much warmth,

M Wells