April 7, 2010 at 11:29 pm
Hi,
I have 2 tables (request and result).
Table request have the following columns:
- ID (running int)
- mobile_phone (varchar)
- date_inserted (datetime)
- parameters (varchar)
- consumed (0 or 1)
- matched (0 or 1)
Table result have the following columns:
- ID (running int)
- mobile_phone (varchar)
- date_inserted (datetime)
- parameters (varchar)
- results (varchar)
And I have a multi-thread application running that is taking each entry from the request table where consumed = 0, send it to a remote process via HTTP, then update the consumed = 1.
When we receive the result from the remote process (via HTTP as well), we then try to find a matching request by selecting the most recent request (within 5 minutes) with matching mobile_phone number and matched = 0. We have to use this matching method because the remote process doesn't give us any form of transaction_id. (We have no control over this remote process).
After finding the matching request, we insert it into the result table (which is then being used for other purposes).
Now the problem is, sometimes my customer input several requests from the same mobile_phone with DIFFERENT parameters into the request table. My application would send all those requests to the remote process, and there are cases when I received the results (all of them) at the same time. So instead of each result taking different request, what happened is that they all are being matched to 1 request.
To illustrate the scenario, let's say we have 3 entries, and after the process, this is what we have:
For table request:
(ID,mobile_phone,date_inserted,parameters,consumed,matched)
1,'16177709089','2010-04-08 04:00:00','Apple',1,0
2,'16177709089','2010-04-08 04:00:00','Orange',1,1
3,'16177709089','2010-04-08 04:00:00','Banana',1,0
For table result:
(ID,mobile_phone,date_inserted,parameters,result)
1,'16177709089','2010-04-08 04:01:00','Orange','some result'
2,'16177709089','2010-04-08 04:01:00','Orange','some result'
3,'16177709089','2010-04-08 04:01:00','Orange','some result'
What we should have is:
For table request:
(ID,mobile_phone,date_inserted,parameters,consumed,matched)
1,'16177709089','2010-04-08 04:00:00','Apple',1,1
2,'16177709089','2010-04-08 04:00:00','Orange',1,1
3,'16177709089','2010-04-08 04:00:00','Banana',1,1
For table result:
(ID,mobile_phone,date_inserted,parameters,result)
1,'16177709089','2010-04-08 04:01:00','Apple','some result'
2,'16177709089','2010-04-08 04:01:00','Orange','some result'
3,'16177709089','2010-04-08 04:01:00','Banana','some result'
This is part of our SQL Statement that does the matching and update:
SELECT TOP 1 @ID = ID, @parameter = parameter
FROM request WHERE date_inserted >= @5_mins_ago and mobile_phone = @mobile_phone;
UPDATE request SET matched = 1 WHERE ID = @ID;
How come when 3 results coming together, all 3 of them are able to pick only one request (in our sample case, is parameters = Orange). Shouldn't it be some kind of locking?
Any advise is appreciated.
Thanks,
Adrian
April 8, 2010 at 12:03 am
Is the ID unique for each mobile_phone and parameter combined?
April 8, 2010 at 12:11 am
Yes, but note that ID for table request and table result is unrelated.
Plus, we cannot use the ID for matching because the remote process (done via HTTP) is asynchronous
April 8, 2010 at 12:18 am
you have given how your desired result must be withour showing how your initial table looked like..please post some sample for the intial table and even clearer description of what your query must perform.
for starters, i have done the table creation sctripts and some insert statements..if not me, others can be helpful for u using these.
Table Scripts
IF OBJECT_ID('REQUEST') IS NOT NULL
DROP TABLE REQUEST
CREATE TABLE REQUEST
(
ID int
,mobile_phone varchar(20)
,date_inserted datetime
,parameters varchar(20)
,consumed BIT
,matched BIT
)
IF OBJECT_ID('RESULT') IS NOT NULL
DROP TABLE RESULT
CREATE TABLE RESULT
(
ID int
,mobile_phone varchar(20)
,date_inserted datetime
,parameters varchar(20)
,RESULT VARCHAR(20)
)
Sample Rows for the REQUEST Table
INSERT INTO REQUEST (ID,mobile_phone,date_inserted,parameters,consumed,matched)
SELECT 1,'16177709089','2010-04-08 04:00:00','Apple',0,0
UNION ALL
SELECT 2,'16177709089','2010-04-08 04:00:00','Orange',0,0
UNION ALL
SELECT 3,'16177709089','2010-04-08 04:00:00','Banana',0,0
Please provide us even further , clearer requirement (no offence meant mate, just that ur original post kinda confused me) , then we will surely address your need 🙂
April 8, 2010 at 12:32 am
OK. You got the tables correct. The request table is initially like what you've shown.
Now, what will happen is:
1). I got a multi-thread application which will select each entry from the request table and send it to a remote process via HTTP.
2). After a while (few seconds) that remote process will send back the result to my application via HTTP as well. Thus it is an asynchronous process.
3). Once I received the result of the process, I have to match it with the request (to grab the value of parameters)
4). Finally input those into table result
What has been the problem is step #3.
If the results come in at different time, it's no problem. Because each result will be matched to each request correctly (after matching, it will update the matched=1).
Or if the requests are for different mobile_phone, then it's no problem as well.
The problem will happen if the requests are for the same mobile_phone and the results come in at exactly the same time. It seems as if the matching process happens exactly at the same time, so it only match those 3 results to 1 request (while the other 2 are ignored, i.e. matched is still 0).
The value of the parameters for the results is then wrong.
Sorry If it's a bit confusing, but do you understand what is the problem?
April 8, 2010 at 12:45 am
2). After a while (few seconds) that remote process will send back the result to my application via HTTP as well. Thus it is an asynchronous process
What and how will your remote process send back, the mobile phone number only or mobile_phone+parameters or mobile_phone+parameters+date_inserterd ?? what nature is your result of?
Cuz, if u get "parameter" as well from ur remote process, then u have no problems in updating. but from your matching query i am finding that u are not receiving the "parameters" from your remote process. please clarify on this.
Sorry If it's a bit confusing, but do you understand what is the problem?
To be honest, no 😀 :-(... but i am trying to 🙂
April 8, 2010 at 12:51 am
🙂 Thanks for trying... I know I'm bad in explaining
The remote process is 3rd party, and I don't have any control over it.
I can only send mobile_phone to them, and they will return us mobile_phone and some_results.
The value of the parameters are not being passed in any way. That's why I need the matching process to get the value of the parameters.
April 8, 2010 at 1:03 am
Will something like this help you?
UPDATE REQ
SET consumed = 1 , matched = 1
FROM REQUEST REQ
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(PARTITION BY mobile_phone ORDER BY mobile_phone, parameters) ROW_NUM
,mobile_phone
,parameters
,consumed
,matched
FROM REQUEST
) ROW
ON
REQ.mobile_phone = ROW.mobile_phone
AND REQ.parameters = ROW.parameters
April 8, 2010 at 2:52 am
Hmm.. not really. Actually I'm looking for the capability so that when 2 or more results came in simultaneously, they will not be matched to a single request (with some kind of locking mechanism).
April 8, 2010 at 3:51 am
adrian.sudirgo (4/8/2010)
Hmm.. not really. Actually I'm looking for the capability so that when 2 or more results came in simultaneously, they will not be matched to a single request (with some kind of locking mechanism).
i think this is taken care of..thats the reason i have partitioned the data by mobile_phones and parameters , so taht the data will be grouped distinctly... anyways, up to u to take a call... i will go thro your full post once again and try to find out another solution mate..
April 8, 2010 at 8:31 pm
Thanks mate! 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply