| Author |
Message
|
| wmb_dp_arya |
 Posted: Mon Nov 14, 2016 4:36 am Post subject: Getting ProcessID was deadlocked on lock resources error |
 |
|
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 |
|
 |
| wmb_dp_arya |
| Posted: Mon Nov 14, 2016 4:39 am Post subject: could you plz help |
|
|
Novice
Joined: 24 Mar 2015
Posts: 11
|
| Could you please suggest what can be done |
|
| Back to top |
|
|
| adubya |
| Posted: Mon Nov 14, 2016 4:54 am Post subject: |
|
|
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 |
|
|
| wmb_dp_arya |
| Posted: Mon Nov 14, 2016 5:29 am Post subject: |
|
|
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 |
|
|
| wmb_dp_arya |
| Posted: Mon Nov 14, 2016 6:06 am Post subject: |
|
|
Novice
Joined: 24 Mar 2015
Posts: 11
|
| Andy can u let me know what steps we can take to resolve |
|
| Back to top |
|
|
| adubya |
| Posted: Mon Nov 14, 2016 6:12 am Post subject: |
|
|
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 |
|
|
| smdavies99 |
| Posted: Mon Nov 14, 2016 10:39 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
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
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 |
|
|
| wmb_dp_arya |
| Posted: Mon Nov 14, 2016 10:47 am Post subject: |
|
|
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
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 |
|
|
| smdavies99 |
| Posted: Mon Nov 14, 2016 10:59 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
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 |
|
|
|
|
