| Author |
Message
|
| martinrydman |
 Posted: Tue Dec 07, 2004 3:02 am Post subject: Concurrency problem WBIMB-DB2 |
 |
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
We have a flow that needs to set a counter-value for each message it processes, such that each consecutive message gets the last value incremented by 1. Simple enough, we create a DB2-table to hold a counter. Works fine.
Now, we have the flow running in a clustered environment. This means that two instances of the flow will potentially access the same counter field in the DB2-table. Classic multi-user problem.
How exactly shall we make sure that we don't run into concurrency problems? Fiddling with isolation levels, we seem to either
a) be too lax (the flows seem to spordically access the same value, i.e F1 reads the value, F2 reads the same value before F1 has updated it)
b) be too strict (we get DB2 error indicating that the table is locked and message gets rolled back)
This seems to be a common enough problem, but I can find hardly anything in neither docs nor Google
Any hints greatly appreciated!
/Martin |
|
| Back to top |
|
 |
| JLRowe |
| Posted: Tue Dec 07, 2004 5:21 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
| In the case of (b), you are locking the entire table? Try just locking the row with SELECT .... FOR UPDATE. Make sure that flow's that lock the row complete as quickly as possible. |
|
| Back to top |
|
|
| EddieA |
| Posted: Tue Dec 07, 2004 12:10 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Using the "new" feature in FP4 to call Java functions, I've written a Lock and Unlock function that can be used to ensure that 2 messages flows do not update a common "element" at the same time. Note, that this "locking" is within a single Execution Group only.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| martinrydman |
| Posted: Tue Dec 07, 2004 11:06 pm Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Thanks for your replies!
Locking only a single record won't help, since the problem arises when 2 processes accesses the same record.
It seems that in the b) case what we would need is for F2 to wait (timeout) before deciding an error has occurred, instead of jumping ship immediately.
It would seem that this scenario is common enough to have 'best practice', but maybe not...?
/Martin |
|
| Back to top |
|
|
| JLRowe |
| Posted: Wed Dec 08, 2004 6:06 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
Surely the second process will wait a while for the lock and not just fail immediately?
Have you gone through the DB2 documentation? |
|
| Back to top |
|
|
| martinrydman |
| Posted: Sat Dec 11, 2004 12:47 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi all,
We now seem to have a solution, but it turns out to be a combination of things:
- We make use of FOR UPDATE (Thanks timna!)
- We set the COMMIT level of the ODBC driver to Repeatable Read
- We set the CL parameter WAITFILE in iSeries to *CLS
This is my understanding of why we need all three. Any guru seeing this and shaking his/her head at this point, please feel free to correct me!
The COMMIT level controls the Isolation Level, and in this case we need Repeatable Read. This much we assumed from the start.
WAITFILE make sure that the first process has time to finnish its job without the second process timing out.
FOR UPDATE makes sure the record gets locked in preparation for the subsequent UPDATE.
Not so hard once you get all the pieces in place, but pretty obscure before you get there! 
/martin |
|
| Back to top |
|
|
| kirani |
| Posted: Sat Dec 11, 2004 7:50 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| martinrydman wrote: |
- We make use of FOR UPDATE (Thanks timna!)
|
I thought only this was required.
Have you tried to set a timeout value on DB instead of using these two parameters?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| martinrydman |
| Posted: Sun Dec 12, 2004 1:22 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
We've been experimenting with various things (I don't know exactly what we've tried and didn't, since a collegue of mine has done the actual fiddling), so I can't say for sure.
How exactly do we set the time-out value on the DB?
Thanks,
/Martin |
|
| Back to top |
|
|
| kirani |
| Posted: Sun Dec 12, 2004 11:51 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
|
| Back to top |
|
|
| martinrydman |
| Posted: Mon Dec 13, 2004 1:25 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
|
| Back to top |
|
|
|
|
