| Thys Brits 2002-11-21, 4:23 am |
| Hi,
We have a problem on a system when system peaks that SQL 'reports' that data
insert was successful, but then checking it, the data is not there. A bit
more info:
Server: 4 x Xeon processors, 2GB Memory
Database 3 x 1.5GB data files, on SAN drives (super-fast), with approx 300MB
free on each device.
The server never runs out of memory and processors never peak more than
30-40%, so the server is not overworked.
We have a Windows service, which can run up to 120 threads (configurable),
each of which creates an instance of a VB 6 DLL. The service gets data via
TCP/IP in XML format (2 XML documents), passes it to the VB DLL, which calls
a stored procedure with the XML all parsed into a parent XML tag:
<Parent>
<XML1>...</XML1>
<XML2>...</XML2>
</Parent>
The sp then opens the XML (sp_PrepareXML), retrieves an ID from the XML,
then does three inserts into 3 tables, using the ID for each insert, and
more data from the XML, using OpenXML. Before the inserts, there is a Begin
Tran. After each insert, it checks that @@ERROR is 0, and @@ROWCOUNT > 0. If
all are successful, it does a Commit Tran, and does a select (using FOR XML)
to return the outcome of the inserts to the DLL, if anything fails, it does
a similar Select, but with error message as a result.
When the system gets very busy, the system doesn't report any errors, but
the 3rd insert's data is not there (ie only part of the Transaction is
really committed). Anyone have any idea on this???
The same thing happens for a second stored procedure that also gets called
with the same XML, but does a lot more inserts. Afterwards, none of the
tables in this SP contain any data for that record. This only happens
intermittently, when the server gets busy.
Below is a partial of the SP - do you think this is sufficient? We have had
problems before, which the component did pick up (where SQL ran out of
resources, and could not create the XML Dom object - this was caught by the
application). In this instance, the application is getting the OK from the
stored procedure, so SQL is reporting that everything went through, although
the data is not there.
Begin Transaction
Insert Into XMLClaimMessage
(MessageID, MessageFileName, BatchNo, FunderID, Sender, Recipient,
Subject, Type)
Select @MessageID, '', @BatchNo, @FunderID, @Sender, @Recipient,
@Subject, @Type
If @@ERROR = 0 And @@ROWCOUNT > 0
Begin
Insert Into ClaimStatus
(MessageID, ClaimStatusCode, ChangedDateTime)
Select @MessageID, ClaimStatusCode.ClaimStatusCode, GetDate()
From ClaimStatusCode
Where Description = 'Received'
If @@ERROR = 0 And @@ROWCOUNT > 0
Begin
Set TextSize 64000
Insert Into XMLMessageData
(MessageID, XMLMessage)
Select @MessageID, @XMLIn
If @@ERROR = 0 And @@ROWCOUNT > 0
Begin
Commit Transaction
If @@ERROR = 0
Begin
Select 1 As Tag,
Null As Parent,
'OK' As [Save!1!Result!element]
For XML Explicit
End
Else
Begin
Select 1 As Tag,
Null As Parent,
'Could not insert the claim message' As
[Save!1!Result!element]
For XML Explicit
End
End
Else
Begin
Rollback Transaction
Select 1 As Tag,
Null As Parent,
'Could not insert the claim message' As
[Save!1!Result!element]
For XML Explicit
End
End
Else
Begin
Rollback Transaction
Select 1 As Tag,
Null As Parent,
'Could not insert the claim message' As
[Save!1!Result!element]
For XML Explicit
End
End
Else
Begin
Rollback Transaction
Select 1 As Tag,
Null As Parent,
'Could not insert the claim message' As
[Save!1!Result!element]
For XML Explicit
End
Thanks
Thys Brits (MCSD, MCAD.NET)
|