Data missing after timeout even though COMMIT has been caled prior to timeout

[Scott Cupstid]

We are working on a VB.NET application using SQLClient command objects to
post data to the underlying SQL Server 2000 database. The application is
deployed in a multi-user environment with no more than 10 user accessing the
database at any given time. The host server is supporting approximately 4
other active databases. In general, there are approximately 60 active
connections on the server.

The incident we are trying to resolve has happened about 3 times within the
last 2 years. It has also been isolated to one user. The problem has
happened in different areas of the code, so it does not appear to be related
to coding problem. It seems to be more of a client connection problem.

To describe the incident, the user begins entering transactions into the
application. Each wrapped in a SQL Server transaction (BEGIN TRANSACTION
.... COMMIT TRANSACTION). This continues for several hours. Eventually, the
server generates a time-out for some reason. All of the users accessing the
system seem to get the time-out if they are accessing the database at the
same time. All of the users have also been entering transactions into the
system in the same manner as the target user. After reestablishing a
connection with the server, the target user learns that all of the
transactions they have entered over the past few hours are no longer in the
database.

The primary table where the transactions are stored has a related hsitory
table for audit purposes. The history table shows that the transactions
made it to the database, but it does not show the transactions as being
deleted.

I am looking for ideas that may help us debug the problem. It is difficult
to run traces as the problem is so intermitent. If you have experienced a
similar problem, we are interested in hearing your suggestions or solutions.

 

[Tibor Karaszi]

Here's a possible reason (not sure it applies to you, of course):

Consider the code:

BEGIN TRAN
UPDATE...
DELETE...
--Now, the DELETE is blocked more than the timeout of the client app, or the user cancels the
query.
-- The transaction will still be open.

So, say now that the code tries this again
BEGIN TRAN
UPDATE...
DELETE...
--Say we have no time-out now
COMMIT TRAN

So, what we had is two BEGIN TRAN and only one COMMIT. I.e., the transaction is still open, because
the client app lost track of this. And then you finally end the app, the connection is terminated
and SQL Server does a rollback. This is easy to handle with some checking against @@TRANCOUNT before
you start a transaction.