| Author |
Message
|
| mqlover |
 Posted: Mon Sep 07, 2015 7:25 pm Post subject: reading the database by 2 brokers |
 |
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
I have 2 brokers talking to the same DB. There is a unique key in the db which is required for every transaction coming through the broker.
A very weird situation i have encountered. One msg came from broker 1 and extracted the sequence number(unique key) from DB. At the same time another msg came from the broker and extracted the sequence number(unique key) from DB. Unfourtunately, while the second broker received the duplicate key which was alredy passed to the transaction which came in from broker1. There are no additional instances running on the EG. Kindly could anyone help me understand how this could have happened?
Thanks in advance |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Sep 07, 2015 10:06 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
where and when do you update this key?
How do you read the DB? (esql please)
What sort of DB is it? (SQLSErver, Oracle, DB2 etc)
Are you putting any explicit locks on the table when you read it?
All in all, you need the help of an experienced DBA to make sure that you have transactional integrity over this data.
Please bear in mind that you may experience a slowdown in message throughput.
_________________
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 |
|
|
| mqlover |
| Posted: Tue Sep 08, 2015 1:37 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
Thanks for your reply.
The key is read from the DB2 database and incremented by 1 and updated in the table back again.
DB : DB2
The scenario so happend that 2 brokers sent a request and the key from DB was accessed almost the same time just 5 milisec time difference.
Here i would like to know if the db creates any lock(anything by itself) nothign coded by applicationn, while reading seq num from db ?
I hope now am clear |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Sep 08, 2015 1:54 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
google for the following
DB2 force update lock
Your problem is not to do with Broker. It is a DB problem, specifically a DB locking problem.
While you are using the retrieved ID on node A, Node B can't get a value until you have updated the table and released a lock.
Then discuss with your DB2 Admins and then re-write your select command.
hint: You can't do this with an ESQL select. You will have to code the select command and send it to the DB using PASSTHRU.
The DBA's should be able to help you on the locking.
_________________
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 |
|
|
| maurito |
| Posted: Tue Sep 08, 2015 2:12 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Sep 08, 2015 2:24 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
That might be true but the OP has not told us what sort of data they are dealing with. It might be that the ID is not suitable for these two data types.
_________________
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 |
|
|
| maurito |
| Posted: Tue Sep 08, 2015 2:26 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| smdavies99 wrote: |
That might be true but the OP has not told us what sort of data they are dealing with. It might be that the ID is not suitable for these two data types. |
Correct. So I am giving the OP an alternative. It is up to him/her to decide whether it is what they want or not. |
|
| Back to top |
|
|
| mqlover |
| Posted: Wed Sep 09, 2015 12:02 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
I will discuss with my team regarding the approach.
Just to check one more thing:
We have a table which stores the sequence number, the broker flow retrieves the sequence number and then increments the sequence number by 1 and updates the DB.
If two threads come simultaneously,i.e 2 transactions come from broker 1 and broker 2 with a negligible time difference then is it possible to read the table concurrently? i.e the sequence number passed to broker1 is say 10, even for broker2 the sequence number is 10 itself. Ideally this should not have happened. How could this issue happen? Wont a DB lock occur? When thread 1 is reading the DB, then sequentially the steps follow such that increment and update of DB is done. IN that time if thread2 comes to read the database then wouldnt the thread1 create lock on DB and stop from reading the sequence number from the DB2?
Kindly need help on this. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Sep 09, 2015 12:32 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqlover wrote: |
I will discuss with my team regarding the approach.
Just to check one more thing:
We have a table which stores the sequence number, the broker flow retrieves the sequence number and then increments the sequence number by 1 and updates the DB.
If two threads come simultaneously,i.e 2 transactions come from broker 1 and broker 2 with a negligible time difference then is it possible to read the table concurrently? i.e the sequence number passed to broker1 is say 10, even for broker2 the sequence number is 10 itself. Ideally this should not have happened. How could this issue happen? Wont a DB lock occur? When thread 1 is reading the DB, then sequentially the steps follow such that increment and update of DB is done. IN that time if thread2 comes to read the database then wouldnt the thread1 create lock on DB and stop from reading the sequence number from the DB2?
Kindly need help on this. |
did to read into DB2 forced Locks as I suggested before?
Did you read this?
http://www.ibm.com/developerworks/data/library/techarticle/dm-0501melnyk/
especially the second paragraph.
Speak to your DB2 Admins and setup a simple POC to test it out.
Don't just rely on the words of 'some strangers on the internet'.
_________________
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 |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 12:32 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
I will discuss with my team regarding the approach.
Just to check one more thing:
We have a table which stores the sequence number, the broker flow retrieves the sequence number and then increments the sequence number by 1 and updates the DB.
If two threads come simultaneously,i.e 2 transactions come from broker 1 and broker 2 with a negligible time difference then is it possible to read the table concurrently? i.e the sequence number passed to broker1 is say 10, even for broker2 the sequence number is 10 itself. Ideally this should not have happened. How could this issue happen? Wont a DB lock occur? When thread 1 is reading the DB, then sequentially the steps follow such that increment and update of DB is done. IN that time if thread2 comes to read the database then wouldnt the thread1 create lock on DB and stop from reading the sequence number from the DB2?
Kindly need help on this. |
as suggested, discuss with your db2 administrators. Then you have the options given in previous posts, use the one that suits your design.
A simple SELECT does not lock the record. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Sep 18, 2015 10:55 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
The usual pattern is update first , then select..
Update (table) set key = key + 1
Select key from (table).
The above sequence will ensure each broker gets its own key..even when they ask the key at the same time.
Change the sequence to select first, and you will have the buried update / duplicate key issue.
_________________
Regards |
|
| Back to top |
|
|
| inMo |
| Posted: Fri Sep 18, 2015 12:38 pm Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
As I see it, the issue is that the "sequence" in question is just a number, and nothing more. The broker seems to be loading that number into memory, manipulating it, and then updating the DB with a new number.
As noted earlier, the intent of this number isn't known, so it is hard to say what is the best solution. However, the current solution is broken and the symptom will surface again.
What is needed is a sequence. Any of the approaches noted are worth considering. I'm endorsing the DB2 Sequence object.
Re-stating the article link:
http://www.ibm.com/developerworks/data/library/techarticle/0302fielding/0302fielding.html#sequence |
|
| Back to top |
|
|
| mqlover |
| Posted: Mon Oct 05, 2015 12:15 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Thanks for the suggestion. How about using the PASSTHRU statement for select?
Yeah the sequence number is kind of unique id which is passed downstream systems. The current issue is that as multiple threads access/read the sequence number at the same time the upstream systems are affected, so want to lock the database until the read and increment of the sequence number is not over. i.e no other threads or incoming msgs can read the database row. |
|
| Back to top |
|
|
| mqlover |
| Posted: Mon Oct 05, 2015 10:08 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
I have been given one more suggestion as to do a select with update, but just wondering how can this be done.
As in my code we do a "select sequence number from database" and then check for few conditions whether the sequence number is NULL or has reached MAX etc and then update the table by incrementing the sequence number.
Can anybody guide me how can a select with update be done? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Oct 05, 2015 10:13 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mqlover wrote: |
I have been given one more suggestion as to do a select with update, but just wondering how can this be done.
As in my code we do a "select sequence number from database" and then check for few conditions whether the sequence number is NULL or has reached MAX etc and then update the table by incrementing the sequence number.
Can anybody guide me how can a select with update be done? |
Do a select for update to lock the row and do the update for position of cursor... probably better done in a stored proc...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
