ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportGetting ProcessID was deadlocked on lock resources error

Post new topicReply to topic
Getting ProcessID was deadlocked on lock resources error View previous topic :: View next topic
Author Message
wmb_dp_arya
PostPosted: Mon Nov 14, 2016 4:36 am Post subject: Getting ProcessID was deadlocked on lock resources error Reply with quote

Novice

Joined: 24 Mar 2015
Posts: 11

We are getting below error when the message flow is updating the records in the SQL Server database and the message broker version is 8.5
Error:
SQL exception-40001//1205//[unixODBC][IBM][ODBC SQL Server Legacy Driver][sqlserver]Transaction(ProcessID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.Rerun the transaction.
Back to top
View user's profile Send private message
wmb_dp_arya
PostPosted: Mon Nov 14, 2016 4:39 am Post subject: could you plz help Reply with quote

Novice

Joined: 24 Mar 2015
Posts: 11

Could you please suggest what can be done
Back to top
View user's profile Send private message
adubya
PostPosted: Mon Nov 14, 2016 4:54 am Post subject: Reply with quote

Partisan

Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK

You need to determine which competing applications are causing the deadlock. Do you run multiple instances of the message flow which is reporting the error ?

There are some simple principles which you should follow when updating multiple database tables in a multiprocessing environment. Do some digging on these and see if your broker flow (and whatever it's deadlocking with) are following these.
_________________
Independent Middleware Consultant
andy@knownentity.com
Back to top
View user's profile Send private message Send e-mail
wmb_dp_arya
PostPosted: Mon Nov 14, 2016 5:29 am Post subject: Reply with quote

Novice

Joined: 24 Mar 2015
Posts: 11

Andy thanks for replying flow was deployed multiple instances of the broker and we will get the xml as the input and will contain multiple structures and need to updated the database with the records.
Back to top
View user's profile Send private message
wmb_dp_arya
PostPosted: Mon Nov 14, 2016 6:06 am Post subject: Reply with quote

Novice

Joined: 24 Mar 2015
Posts: 11

Andy can u let me know what steps we can take to resolve
Back to top
View user's profile Send private message
adubya
PostPosted: Mon Nov 14, 2016 6:12 am Post subject: Reply with quote

Partisan

Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK

OK. If you'v multiple instances running then you need to consider the following scenario (and guard against it).

Instance 1

Updates row with key "A"

simultaneously

Instance 2

Updates row with key "F"


then....


Instance 1 attempts to update row with key "F"

and instance 2 attempts to update row with key "A"

both instances are attempting to update a row which is locked by the other instance. This is a deadlock situation (which the database engine can detect and generate an error - as you've seen).

If the code were modified to update rows in Key order then this situation can be avoided. This isn't the only way a deadlock can occur but I've seen it happen!

Another scenario is if multiple tables are being updated but the order the tables are accessed can vary between two different instances of a flow.

If you can find out which table is the cause of the deadlock then investigation and problem resolution will be much easier!
_________________
Independent Middleware Consultant
andy@knownentity.com
Back to top
View user's profile Send private message Send e-mail
smdavies99
PostPosted: Mon Nov 14, 2016 10:39 am Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6077
Location: Somewhere over the Rainbow this side of Never-never land.

I have seen this quite often with SQLServer.

I solved it by doing the following.

1) Change all SQL Selects that were for reference data to use PASSTHRU
2) Change the Query to use
Code:

with (NoLock)

You can't include the locking in the straight ESQL select.

In most cases, the problems went away.

The different DB's that IIB use have different data locking philosiphies. you need to understand them in order to design your flows to make the best use of the DB resources and features with IIB.



YMMV and all that.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.
Back to top
View user's profile Send private message
wmb_dp_arya
PostPosted: Mon Nov 14, 2016 10:47 am Post subject: Reply with quote

Novice

Joined: 24 Mar 2015
Posts: 11

smdavies99 wrote:
I have seen this quite often with SQLServer.

I solved it by doing the following.

1) Change all SQL Selects that were for reference data to use PASSTHRU
2) Change the Query to use
Code:

with (NoLock)

You can't include the locking in the straight ESQL select.

In most cases, the problems went away.

The different DB's that IIB use have different data locking philosiphies. you need to understand them in order to design your flows to make the best use of the DB resources and features with IIB.



YMMV and all that.
Thanks a lot smdavies and im updating the records using passthru statement so u want me include with no lock in the update query statement?
Back to top
View user's profile Send private message
smdavies99
PostPosted: Mon Nov 14, 2016 10:59 am Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6077
Location: Somewhere over the Rainbow this side of Never-never land.

No, not the UPDATE. If you read my post, I did say the SELECT statements.

If you are reading a table/tables for reference purposes then the changes I made to add the "with (NoLock)" is appropriate.

You can't do an update without some form of locking.
If you are doing a select and you will later update that row then you should not use the "with (NoLock)".

It is all down to starting to understanding the DB Locking and coding your flow to make best use of it.

If you are not doing any reference selects then you may well have to engage the DBA's to work on a design that avoids the DEADLOCKS. There are really a NO-NO in the DB world.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportGetting ProcessID was deadlocked on lock resources error
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.