Deadlock at database

[Former Member]

Hi Experts,

I am using a proxy to database scenario in which i am accessing four tables simultaneously.And i am using update_insert as an action attribute for all.Also i have created four statements for four tables.Now i am getting messages in XI as smaller chunks not a single message.So at the database side i am facing a deadlock issue.Error is :

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'IF_Invoice_NO02' (structure 'StatementName'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Transaction (Process ID 77) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Could you please provide any help to avoid this issue and any guidance to resolve this.

[former_member200962 · Accepted solution]

Now i am getting messages in XI as smaller chunks not a single message

Is it that your Proxy is sending the data in different calls? If yes then you need to make sure that the proxy is triggered with all the four data elements at any given time

OR

You need to hold the individual messages in XI and then once you get all the four elements then send them in one go to the DB....one way (that i can think) of doing this by using a BPM to collect all the message and then N:1 mapping and then send

If I got your requirement in a wrong way then please ignore!

Regards,

Abhishek.

[Former Member · Accepted solution]

No I am getting messages in one shot.And also we want the solution without BPM.

[former_member200962 · Accepted solution]

then I suppose that you are making different calls to the DB to Insert/ Update

In this case you are sending incomplete Data structure...suppose you have data for 1 Statement and rest are empty then the rest Statement which are empty seem to be causing the error.

[Former Member]

this means you are trying to update the same table while that row is being updated by another process.

check your locking in database (table level lock/Row level lock)

if you can provide your xml structure(payload) after receiver mapping or SQL statement from audit log then it will be easier to guide.

[Former Member]

Hi Please see the sql statement from audit log for two rows :

UPDATE IF_Invoice_SE02 SET BrandClusterId=14, Customerno=2001905137, Articleno=81131244, CountItems=1, District=SE0039, Net1=0.00, SalesOffice=SE51, Invoicedate=20090724, Documentno=0997007807000880, Discount1=-50.00, Discount_ZYEB=0.00, Discount_ZYBN=0.00, NOS1=0.00, Gross=50.00, SFAOrderno=SE_TOM080172:1, Promotionno=NULL, XIUpdDate=20090729, Modifiedstatus=01 WHERE (Documentno=0997007807000880)

Success

INSERT INTO IF_Invoice_SE02 (BrandClusterId, Customerno, Articleno, CountItems, District, Net1, SalesOffice, Invoicedate, Documentno, Discount1, Discount_ZYEB, Discount_ZYBN, NOS1, Gross, SFAOrderno, Promotionno, XIUpdDate, Modifiedstatus) VALUES (14, 2001905137, 81131244, 1, SE0039, 0.00, SE51, 20090724, 0997007807000880, -50.00, 0.00, 0.00, 0.00, 50.00, SE_TOM080172:1, NULL, 20090729, 01)

[Former Member]

which is your Primary key, hope both the statement is working with different rows of table.

if it is the case ,ask your DBA to make the constraint to row level instead of table level.

if both the queries are operating on same table then normalize your database.