Tony Andrews (an******@onetel.net.uk) writes:
Yes, my suspicion is that it is possibility (1). The application is
written in ASP using ODBC to connect to the database. Autocommit is
used. The logic is something like this:
1) Insert record into table
2) Redirect to another page to print out a certificate
Allegedly, there are a few people holding certificates where no
corresponding record exists in the database. There is no code in the
app. that deletes this data, though of course it could be deleted
manually.
I have verified that if the insert fails (e.g. if I add a check
constraint that will always be violated), the ASP page aborts and does
not redirect to the next page.
Are there any particular scenarios I should look out for?
There are a few nasty situations, which must be handled properly in
application code.
Say that you call a stored procedure which starts a user-defined
transaction. That stored procedure runs for a longer time, because of
the query or because of blocking. By default, all client libraries
have a timeout which sets in after 30 seconds (if no data have been
seen), and cancels the query. Contrary to what you may think, this DOES
NOT rollback the transaction! Thus, in your error-handling code you
need to issue "IF @@trancount > 0 ROLLBACK TRANSACTION".
Note here that even you don't use stored procedures but issue plain
INSERT statements, this can happen if there is a trigger on the table.
A trigger alwyas executes in the context of a transaction. (You may
get an automatic rollback when you cancel execution in a trigger; I
have not tested this case, but I would not trust on getting a ROLLBACK.)
There are some variations of this theme. If a stored procedure refers
to a non-existing table, the procedure will abort on that statement,
without rollback any transactions it may have started. Again, the
client could as a matter of routine always issue rollback in case of
an error.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp