473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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


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.

Jan 26 '07 #1
1 1479
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.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Cupstid" <sc******@tampabay.rr.comwrote in message
news:Ok**************@TK2MSFTNGP02.phx.gbl...
>
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.
Jan 26 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: M Wells | last post by:
Hi All, I'm trying to track down a mysterious problem we're experiencing in which updates and inserts to tables in our mssql2k server appear to be 'disappearing.' To explain our situation: ...
1
by: M Wells | last post by:
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...
3
by: Ray | last post by:
I am having my first experience using BLOB as a row in a table. I am using it to insert graphics for labels we print. I have no problem inserting into and select from the table. The graphic is...
2
by: Chris Langston | last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server that is experiencing strange shutdown problems. We are using ASP.NET v1.1 and our application is written in VB.NET ...
3
by: Saket Mundra | last post by:
I have multiple web forms in my application. The user after logging on is directed to form1 where he enters information desired. Once finished he is directed to form2 and the same procedure goes on...
3
by: DougS | last post by:
We have an ASP.Net (framework 1.1) app that does a lot of database reads and updates. The app has a dozen pages and we're about 90% done and all of a sudden I'm getting this error: Timeout...
2
by: Mark S. Milley, MCAD (BinarySwitch) | last post by:
This is an interesting problem... I'm using MS Access for the backend for a simple task list web application. (I know, I know, but Access is all my Web Host will allow without killing me in...
9
by: David Harris | last post by:
Ok, so I'm semi-new to .NET, having done everything manually with SQL code back in VB6. So before I program this up completely manually again, I thought I'd ask for better ways to think through...
3
by: M Bourgon | last post by:
On SSMS 9.00.3042.00, any query that runs longer than 10 minutes gets the following error message: The statement has been terminated. Msg -2, Level 11, State 0, Line 0 Timeout expired. The...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.