Solved

My SP returns same records to more than one server. How to avoid this.

Posted on 2011-03-17
11
265 Views
Last Modified: 2012-05-11
Below is my SP.

I want to make sure the SELECT statement returns only the rows updated by UPDATE statement.

Actual problem is - my application is running on more than one server and this SP (select statement) is returning the same rows to more than one server. It should not do like that. It should NOT return the same records to any other server.

Please suggest me how to achive this.
BEGIN
       
        UPDATE GPQ_MQ_DATA 
        SET WNDW_SVC_ID = i_server_id  
        WHERE MQ_ID IN (SELECT MQ_ID
                        FROM (SELECT MQ_id, ROWNUM rn
                                FROM (SELECT MQ_ID
                                        FROM GPQ_MQ_DATA
                                        WHERE   STATUS = '1'
                                        AND NVL (WNDW_SVC_ID, 0) = 0
                                        ORDER BY   MQ_ID
                                      )
                              ) x
                        WHERE rn <= i_rec_count
                        );
        
    
    OPEN o_recordset FOR 
    
        SELECT MQD.MQ_ID, MQD.MQ_TYPE, MQD.MQ_DATA_COL.GETSTRINGVAL() XMLDATA, MQD.S_ROW_UPD
	    FROM GPQDBO.GPQ_MQ_DATA MQD
        WHERE WNDW_SVC_ID = i_server_id  
        AND STATUS = '1';                                                 -- 1 scheduled to process. 

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            raise_application_error (-20101, SQLERRM, TRUE);
            
END;
/

Open in new window

0
Comment
Question by:GouthamAnand
  • 5
  • 5
11 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
I can only tell that either something is wrong with your update statement or something incorrect in the way you are using your select statement for the OPEN o_recordset.

Can you give some sample data for 4 or 5 records in the GPQDBO.GPQ_MQ_DATA table ?

Also explain in more details like how the data looks and what you want to update and what you open the recordset for ?

Difficult to assist you without having this information.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Some questions:
1. Is this an Oracle question or a SQL Server question?  Yes, you posted it in the Oracle section of this site, but use of the abbreviation "SP" is more common among SQL Server users than among Oracle users.

2. Your references to the table (or view?): GPQ_MQ_DATA are inconsistent.  Most of them have no schema prefix, but in the "OPEN ...FOR" part of the procedure you use a schema profix with this object name?  Is that difference intentional?  

3. We have no idea how big the table GPQ_MQ_DATA is, but a couple of the things you do in this procedure could cause huge performance problems if the table is large.  These are: the "NVL (WNDW_SVC_ID, 0)" which can prevent the use of an index; and the use of "rownum" in a subquery to limit the records being returned by the outer query.  Unfortunately, Oracle will take the time and resources to find and fetch all of the matching records, then only return some of them.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Hi,

1. This is an Oracle question, by mistake I used SP.

2. GPQ_MQ_DATA is a table.

3. GPQ_MQ_DATA is a huge table. If you foresee the performance problems with my Update statement, Please suggest the other way of writing same Update statement.

4. Rows keep accumulated to GPQ_MQ_DATA continuously from an application. My application will be running on '2' servers and each server should pick the 'n' number of records specified and process them and update the same table.

Here i_server_id  is the server ID which picking the records. While picking the records it has to pick from bottom, not from top.

I need to make sure the no two servers are picking the same record(s) to process.

earlier my update statement was as below -

UPDATE GPQ_MQ_DATA
        SET WNDW_SVC_ID = i_server_id  
        WHERE MQ_ID IN (SELECT MQ_ID
                        FROM GPQ_MQ_DATA
                        WHERE STATUS = 1
                        AND ROWNUM <= I_REC_COUNT
                        );
But this one is updating from the top of the table. And as the accumalation of records/rows is much faster than the records being processed, some records are not at all gettting processed by any server. Hope you understood why.

For example - within one minute 100 rows came in. 1st server picked 10 records. By the time 2nd server pick next 10 records, 100 more came in. Just think...both the servers process always top records and leaving the bottom records.

Thats why I wrote the update statment like that to pick the records from the bottom of the table and can process all the records.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Oracle does not have an efficient way to find records based on a null value, so if the records you need to find really do have a null value for: WNDW_SVC_ID, and if the table is huge, you need to try to find a different efficient way to find them.  Can you add a default value of "0" (zero) for this column?  Or, can you add a "before insert" trigger to set a default value for this column?  If you can't do either of those, can you at least add a new column to the table with a default value of "Y"?  Then you could add an index on this new column, change your "where" clause to find the records where this new column = 'Y', then change the update to set the value of this new column to null (so the index stays small and efficient) when you set the value of WNDW_SVC_ID.

One option for making sure that only one server finds and updates the rows would be to use two cursors instead of a direct update based on a sub-query.  The first query would use a loop and attempt to find the rows to process, the second query (in a nested block with local error handling) would attempt a "select ... for update no wait" on each individual row.  This will make sure that only one process updates each row.  If the second cursor suceeds, you would update that individual row, otherwise you would skip that row, because the other server has it locked.

If you would like help writing your procedure with these two cursors, please tell us.

Must your procedure also return a "ref cursor" of the rows that were processed?  It looks like you have an "open [cursor_name] for..." command in your procedure, but we don't have the entire procedure (or package) source code posted here, so we can't see for sure.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Please help me in writting the below SP with two cursors. Aim is when more than one service is calling this SP , it should not return same row(s) to more than one server.
CREATE OR REPLACE PROCEDURE GPQPROC.SP_GET_MQ_DATA
(
	i_rec_count NUMBER,
    i_server_id IN NUMBER,
    o_recordset IN OUT SYS_REFCURSOR
)
AS  
BEGIN
       
        UPDATE GPQ_MQ_DATA 
        SET WNDW_SVC_ID = i_server_id  
        WHERE MQ_ID IN (SELECT MQ_ID
                        FROM (SELECT MQ_id, ROWNUM rn
                                FROM (SELECT MQ_ID
                                        FROM GPQ_MQ_DATA
                                        WHERE   STATUS = '1'
                                        AND NVL (WNDW_SVC_ID, 0) = 0
                                        ORDER BY   MQ_ID
                                      )
                              ) x
                        WHERE rn <= i_rec_count
                        );
        
    IF ( SQL%ROWCOUNT <> 0 ) THEN 
    OPEN o_recordset FOR 
    
        SELECT MQD.MQ_ID, MQD.MQ_TYPE, MQD.MQ_DATA_COL.GETSTRINGVAL() XMLDATA, MQD.S_ROW_UPD
	    FROM GPQDBO.GPQ_MQ_DATA MQD
        WHERE WNDW_SVC_ID = i_server_id  
        AND STATUS = '1';                                                 -- 1 scheduled to process. 
    ELSE
        RETURN;
    END IF;
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            raise_application_error (-20101, SQLERRM, TRUE);
            
END;
/

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Have you thought of an efficient way to find records to process?  Can you add a default value of "0" (zero) for this column?  Or, can you add a "before insert" trigger to set a default value for this column?  If you can't do either of those, can you at least add a new column to the table with a default value of "Y"?  Are you allowed to add an index on this table?

Please help us with answers to those questions first.  Because until that problem is solved, it will be difficult (and/or very inefficient) for this procedure to do what you want it to do.
0
 

Author Comment

by:GouthamAnand
Comment Utility
I have added "0" as default value and modified my procedure.
This table is having index on mq_id.
CREATE OR REPLACE PROCEDURE GPQPROC.SP_GET_MQ_DATA
(
	i_rec_count NUMBER,
    i_server_id IN NUMBER,
    o_recordset IN OUT SYS_REFCURSOR
)
AS  
BEGIN
       
        UPDATE GPQ_MQ_DATA 
        SET WNDW_SVC_ID = i_server_id  
        WHERE MQ_ID IN (SELECT MQ_ID
                        FROM (SELECT MQ_id, ROWNUM rn
                                FROM (SELECT MQ_ID
                                        FROM GPQ_MQ_DATA
                                        WHERE   STATUS = '1'
                                        AND WNDW_SVC_ID = 0
                                        ORDER BY   MQ_ID
                                      )
                              ) x
                        WHERE rn <= i_rec_count
                        );
        
    IF ( SQL%ROWCOUNT <> 0 ) THEN 
    OPEN o_recordset FOR 
    
        SELECT MQD.MQ_ID, MQD.MQ_TYPE, MQD.MQ_DATA_COL.GETSTRINGVAL() XMLDATA, MQD.S_ROW_UPD
	    FROM GPQDBO.GPQ_MQ_DATA MQD
        WHERE WNDW_SVC_ID = i_server_id  
        AND STATUS = '1';                                                 -- 1 scheduled to process. 
    ELSE
        RETURN;
    END IF;
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            raise_application_error (-20101, SQLERRM, TRUE);
            
END;
/

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Which column did you add the default value on?  Is it: WNDW_SVC_ID or MQ_ID?  I hope it is: WNDW_SVC_ID.  An index on MQ_ID doesn't help much.  An index on WNDW_SVC_ID and a default value of 0 on that column should help greatly.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Ok, I have added an index on WNDW_SVC_ID and also its default value is 0.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
Comment Utility
Setting the default value for that column may or may not be enough.  Depending on how the application does the inserts into this table (if it includes this column in the insert statement) you will need a "before insert for each row" trigger to force that value to 0 (zero).

Then with an index on this column that should give you an efficient way to find the records that have a value of 0 (zero).

That leaves the problem of how to make sure that only one server processes each row, if you run this procedure simultaneously from two different servers.

First test this, to make sure that your procedure now finds the rows quickly.
0
 

Author Closing Comment

by:GouthamAnand
Comment Utility
Thanks a lot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now