Is this even possible?

Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspAre both stored procedures and the process that write the identity into the
file wrapped into a transaction?, If not, then there is not a guarantee that
the value being in the file means it was inserted into the other tables.
AMB
"Craig" wrote:

> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:

>Are both stored procedures and the process that write the identity into the
>file wrapped into a transaction?, If not, then there is not a guarantee tha
t
>the value being in the file means it was inserted into the other tables.
Hi thanks for the reply,
In the stored procedure, the data is inserted, then transaction is
commited witha rollback clause. I would have through that if it had
rolledback (Throuch not being able to commit) then it wouldnt be
selectable using a statement after?
its is two completely different stored procedures called from
different ASP pages, why is there no guarantee? Surely a commit means
that the data should be there, and the subsequent select means that
the data should have commited?
Or am I wrong here? Sorry for asking you to explain what you mean, but
I was always under the impression that you couldnt select non
committed data because that should be locked until commited? If your
saying I can insert data using a stored procedure and commit it, for
the data to subsequently not commit (even after it had been selected)
then thats scary?
Thanks again
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||What I meant was that inserting the order header, details and writing the ne
w
order number in a file that is in the file system, should be in a
transaction, if something was wrong then everything is rolled back (I am
using order headerand detail as an example). You can accomplish this using
diff sp, but it does not mean that both sp should run in a diff transaction.
AMB
"Craig" wrote:

> On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
>
> Hi thanks for the reply,
> In the stored procedure, the data is inserted, then transaction is
> commited witha rollback clause. I would have through that if it had
> rolledback (Throuch not being able to commit) then it wouldnt be
> selectable using a statement after?
> its is two completely different stored procedures called from
> different ASP pages, why is there no guarantee? Surely a commit means
> that the data should be there, and the subsequent select means that
> the data should have commited?
> Or am I wrong here? Sorry for asking you to explain what you mean, but
> I was always under the impression that you couldnt select non
> committed data because that should be locked until commited? If your
> saying I can insert data using a stored procedure and commit it, for
> the data to subsequently not commit (even after it had been selected)
> then thats scary?
> Thanks again
> Craig
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 10:05:05 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>What I meant was that inserting the order header, details and writing the n
ew
>order number in a file that is in the file system, should be in a
>transaction, if something was wrong then everything is rolled back (I am
>using order headerand detail as an example). You can accomplish this using
>diff sp, but it does not mean that both sp should run in a diff transaction.[/color
]
Hi,
Thanks again. But I dont quite understand how data inserted in one SP
can be queried using another at a slightly later time if it couldnt
commit. I understand that there might have been a problem inserting
the data in the first place, and understand rollbacks. But I dont
understand how the data could be queried (Selected) in a different SP
without the data being commited.
The application process flow works like this yet the transaction logs
dont even know that this data existed?
Cheers
Craig
>
>AMB
>"Craig" wrote:
>
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Thanks again. But I dont quite understand how data inserted in one SP
> can be queried using another at a slightly later time if it couldnt
> commit. I understand that there might have been a problem inserting
> the data in the first place, and understand rollbacks. But I dont
> understand how the data could be queried (Selected) in a different SP
> without the data being commited.
> The application process flow works like this yet the transaction logs
> dont even know that this data existed?
That could occur if the SELECTing transaction used a NOLOCK or READ
UNCOMMITTED query hint. In essence, it can select uncommitted data
(dirty reads), and if the other transaction rolled back, those rows
would be gone.
David Gugick
Imceda Software
www.imceda.com|||
>That could occur if the SELECTing transaction used a NOLOCK or READ
>UNCOMMITTED query hint. In essence, it can select uncommitted data
>(dirty reads), and if the other transaction rolled back, those rows
>would be gone.
Okay, thats a point, however no lock was specified during any of these
read or writes, so could this be set as default on the server? (This
is not our SQL server)
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Okay, thats a point, however no lock was specified during any of these
> read or writes, so could this be set as default on the server? (This
> is not our SQL server)
This is usually done on a per-query basis or at the client level. You'll
have to check the ADO or ADO.Net connection and query information in the
ASP code and also check the stored procedure code for SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED and NOLOCK table hints.
How are you pulling the identitiy value? Are you using SCOPE_IDENTITY().
If not, you should be.
David Gugick
Imceda Software
www.imceda.com