| Author |
Message
|
| vandi |
 Posted: Thu Feb 12, 2009 1:34 pm Post subject: Database transactions |
 |
|
Acolyte
Joined: 13 Dec 2008
Posts: 67
|
Hi All,
I am working on a Message flow where we have multiple database operations. We have more than one instance of the message flow running. I have a doubt regarding database the threads accessing the databases concurrently.
We have a User database on whic we are performing multiple inserts/updates. When he have more than one instance, if one instance does an update/insert on the table, can other instance access the table?
If we have inserts and select on the same database table, if one instance is writing on to the table and othee instance has to do a select on it, will this show any uncommitted data to the select?
Can you pl slet me know how to deal with transactions in databases?
Thanks
vandi |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Feb 12, 2009 2:22 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You will experience Database locking.
Now you can minimise some of this by modifying your select statements to do the job read only.
This may involve using select via the PASSTHRU function.
The amount of locking does depend upon your message rates.
For example, I had a project with one flow that used 3 extra instances of it thus making 4 instances.
We managed 16 messages/sec with normal selects. By changing those wherever possible to read only selects, we got the throughput up to 24/sec.
The biggest block was the instances all trying to update message counters at the same time. By moving this into a single update, the mesage rate went up to 32/sec. We did further tuning and the flow has been in daily use for more that 2 years processing 50+ messages/sec (up to 700,000/day, 5 days a week) and no serious locking problems have been encountered.
Your are probably going to get locks as you indicated that you are trying to read data from a table that is being updated. This could lead to a deadlock situation. Only extensive testing with high message rates will provide you with the data as to the overall significance to the operation of your flows.
You may find that because of the locking issues you get better performance out of the flow with just a single instance. Again testing will tell you if that is the case.
The different databases that broker can use have different ways of dealing with row, column or page locks (I'd have to read the docs to elaborate more). These will all have different effects on how your flow will operate.
_________________
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 |
|
|
| vandi |
| Posted: Thu Feb 12, 2009 5:06 pm Post subject: |
|
|
Acolyte
Joined: 13 Dec 2008
Posts: 67
|
Hi,
Thanks for the info.
I am confused with a scenario like below.
We have a flow with MQ input node's Transaction mode set to Yes. Now if I have database operation ie., insert followed by select in compute node with Transaction mode set to Automatic.
When we use more than one instance on Message flow and if one instance does write on Database and other does read on database. We would get conflicts with the lock.s
Can you pls let me know how do we handle this? |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 13, 2009 12:58 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| vandi wrote: |
| Can you pls let me know how do we handle this? |
The same way you'd handle any other scenario where multiple applications are updating the same database.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Feb 13, 2009 1:57 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
to add to the last reply
Inserting records into a table and reading from it in the same UOW PLUS having multiple threads doing the same is going to result in LOCKS on the table.
This might not be significant to your requirement but (And what I was trying to illustrate in my previous reply) is that with careful design and a lot of testing you can
1) Discover the locks
2) make changes to the flow and how it uses the table to reduce or eliminate these locks.
How you do this does differe between they types of backend database.
Remember that unless you specifically say 'read only' a select places a lock on the bits of the table you have read. Then if in another instance of the flow, the select it does needs the same bit of data in its select, you are going to get some blocking.
In most cases, how you solve that is a DB issue and not directly a Broker one.
You should consult with your DBA's about this... 
_________________
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 |
|
|
| Vitor |
| Posted: Fri Feb 13, 2009 2:06 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
In most cases, how you solve that is a DB issue and not directly a Broker one.
You should consult with your DBA's about this... |
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
