jeudi 8 juin 2017

Selecting a random row from a table - DBMS_RANDOM.VALUE is giving same row when executed from two different users

A quick help folks! My application needs to randomly fetch a single value from a table in my Oracle database with around 200,000 rows. Multiple users might be using the application at the same time, so the purpose is to have all of them see a different value everytime they use click on a button. Once a row has been used, as mark it as used and don't use it again. Here's the sample of the query that I am using:

        SELECT
           MY_COLUMN
        FROM
               ( SELECT
                        MY_COLUMN
                 FROM
                        MY_TABLE
                 WHERE
                       COLUMN_STATUS = Not used
                 ORDER BY
                        DBMS_RANDOM.VALUE )
             WHERE
                 ROWNUM = 1;

It works when I use on a single user, it gives me a different value every single time. But I did a test in which I logged in with two different users on two different machines, and I clicked the "button" at the same moment, and on both users I got the same value. Am I missing something here? Is that how DBMS_RANDOM.VALUE is supposed to behave? If yes, do you have any suggestion that can solve the above problem.

Thanks, I appreciate your help people!




Aucun commentaire:

Enregistrer un commentaire