|
Home > Archive > BizTalk Server Orchestration > December 2004 > SQL Adapter request-respose deadlock
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Adapter request-respose deadlock
|
|
|
| I'm having a problem with deadlocks in a table in SQL server when trying to
using request-response storedprocedure. It works fine for single instance of
orchestration. If i drop multiple files together it shows warning msg in the
eventlog.
Error:
The adapter "SQL" raised an error message. Details "HRESULT="0x80004005"
Description="Transaction (Process ID 85) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the
transaction."
?<?xml version="1.0" encoding="utf-16" ?><Card_Delivery_Res
xmlns="http://sww.shell.com/xsd/Aviation/SAV/ONEHubV2/Delivery/1.0"><?MSSQLError
HResult="0x80004005" Source="Microsoft OLE DB Provider for SQL Server"
Description="Transaction (Process ID 85) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the
transaction."?></Card_Delivery_Res>".
Sp i used (I m passing xml document which checks record exists, if not it
inserts and return 0, else 1)
CREATE PROCEDURE dbo.usp_InsertCardDelivery
@parameter1 text
AS
--here we simply insert the xml into a field
--but it could easily be parsed and inserted
--into a table
SET NOCOUNT ON
--get an xml document for the incoming paramter
DECLARE @iDoc int
DECLARE @TotalCount int
--prepare the document using the namespace so we can do proper xpath queries
EXEC sp_XML_PrepareDocument @iDoc OUTPUT, @parameter1, '<ns0:File_XMLL
xmlns:ns0="http://SQLService/TempProject"/>'
--update in this case, the sample row we have in the database
--this could easily be an insert as well, by checking only those items
--in the xml that do not have an ID
SELECT @TotalCount=count(*) FROM OPENXML (@iDoc, '/ns0:File_XML')
WITH(Field1 char(10) '@Field1',
Field2 datetime '@Field2',
Field3 char(10) '@Field3',
Field4 char(10) '@Field4',
Field5 char(10) '@Field5') t,TempTable
WHERE
TempTable.Field1=t.Field1 and
TempTable.Field2=t.Field2 and
TempTable.Field3=t.Field3 and
TempTable.Field4=t.Field4 and
TempTable.Field5=t.Field5
IF @TotalCount=0
INSERT INTO TempTable SELECT * FROM OPENXML(@iDoc, '/ns0:File_XML') WITH
TempTable
IF @TotalCount > 0
SELECT 1 AS TotalCount FOR XML RAW
ELSE
SELECT 0 AS TotalCount FOR XML RAW
--release the xml document object
EXEC sp_XML_RemoveDocument @iDoc
RETURN
GO
| |
| Matt Milner 2004-12-31, 5:48 pm |
| You could try putting some locking hints on your first select statement
because that is likely locking the temptable table. Use a nolock or some
other hint to keep that one from locking and see if that fixes the problem.
The other thing you could do is combine your two sql statements into one.
Is it only the case that you would inser when all values don't match; that
is, do you have a compositve uinique key using all those fields?
Matt
"Sents" <Sents@discussions.microsoft.com> wrote in message
news:7D7C8703-E5DC-4C9F-9668-B9B8905C849A@microsoft.com...
> I'm having a problem with deadlocks in a table in SQL server when trying
> to
> using request-response storedprocedure. It works fine for single instance
> of
> orchestration. If i drop multiple files together it shows warning msg in
> the
> eventlog.
>
> Error:
> The adapter "SQL" raised an error message. Details "HRESULT="0x80004005"
> Description="Transaction (Process ID 85) was deadlocked on lock resources
> with another process and has been chosen as the deadlock victim. Rerun the
> transaction."
> ?<?xml version="1.0" encoding="utf-16" ?><Card_Delivery_Res
> xmlns="http://sww.shell.com/xsd/Aviation/SAV/ONEHubV2/Delivery/1.0"><?MSSQLError
> HResult="0x80004005" Source="Microsoft OLE DB Provider for SQL Server"
> Description="Transaction (Process ID 85) was deadlocked on lock resources
> with another process and has been chosen as the deadlock victim. Rerun the
> transaction."?></Card_Delivery_Res>".
>
> Sp i used (I m passing xml document which checks record exists, if not it
> inserts and return 0, else 1)
>
>
> CREATE PROCEDURE dbo.usp_InsertCardDelivery
> @parameter1 text
>
> AS
> --here we simply insert the xml into a field
> --but it could easily be parsed and inserted
> --into a table
> SET NOCOUNT ON
>
> --get an xml document for the incoming paramter
> DECLARE @iDoc int
> DECLARE @TotalCount int
>
> --prepare the document using the namespace so we can do proper xpath
> queries
> EXEC sp_XML_PrepareDocument @iDoc OUTPUT, @parameter1, '<ns0:File_XMLL
> xmlns:ns0="http://SQLService/TempProject"/>'
>
> --update in this case, the sample row we have in the database
> --this could easily be an insert as well, by checking only those items
> --in the xml that do not have an ID
> SELECT @TotalCount=count(*) FROM OPENXML (@iDoc, '/ns0:File_XML')
> WITH(Field1 char(10) '@Field1',
> Field2 datetime '@Field2',
> Field3 char(10) '@Field3',
> Field4 char(10) '@Field4',
> Field5 char(10) '@Field5') t,TempTable
> WHERE
> TempTable.Field1=t.Field1 and
> TempTable.Field2=t.Field2 and
> TempTable.Field3=t.Field3 and
> TempTable.Field4=t.Field4 and
> TempTable.Field5=t.Field5
>
> IF @TotalCount=0
> INSERT INTO TempTable SELECT * FROM OPENXML(@iDoc, '/ns0:File_XML') WITH
> TempTable
>
> IF @TotalCount > 0
> SELECT 1 AS TotalCount FOR XML RAW
> ELSE
> SELECT 0 AS TotalCount FOR XML RAW
>
> --release the xml document object
> EXEC sp_XML_RemoveDocument @iDoc
>
> RETURN
> GO
>
>
|
|
|
|
|