0

I am developing a database application which calculates scores and shows the ranking of people. What I'm doing is executing procedures which calculate the scores and store these scores in a database table. From that, a final score is calculated. I then display the ranking according to the score.

What I'm worried about is this: what if when two users are simultaneously using the application. If one has already calculated the scores and just before retrieving the score, the other user executes the procedure and overrides the table. As different users can specify different parameters, the tables will be different. Is there any way i can create a unique table for each unique simultaneous user currently using that application?

Database: oracle

grindel
  • 29
  • 1
  • 6
  • 1
    Do you actually need to materialize the results? It's certainly possible to write them to a temporary table (though that gets a bit challenging in three-tier applications where the application session does not map cleanly to a database session). Normally, you wouldn't materialize the result, you'd simply have a stored procedure that runs a query based on whatever parameters you need and returns a result set to the caller. – Justin Cave May 22 '16 at 09:03
  • What do you mean by 'result set' ? Do you mean a procedure can return something like a table? – grindel May 22 '16 at 09:58
  • A function can return a `sys_refcursor` which is just the result of a query. A procedure can have an `out` parameter of type `sys_refcursor` as well. – Justin Cave May 22 '16 at 10:01

1 Answers1

0

Use a temporary table for the interim results. A copy of the table is instantiated for each database session so there will be no interference on parallel uses of the application.

Example:

CREATE GLOBAL TEMPORARY TABLE schema.table (
    col_pk          NUMBER PRIMARY KEY
  , col_score       NUMBER
  , col_whatever    VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS; -- alternative: ON COMMIT DELETE ROWS; does the obvious thing ...

Alternatively, add a user id to the table in which you compute the scores ( eg. by adding a foreign key referencing a user identification table ) to keep results for different users apart.

Finally you could shift the score computation to a stored procedure wrapped into a plsql package. These packages are also instantiated by db session.

Update (application architecture)

As Justin Cave notes, additional logic is needed for multi-tiered applications where database sessions are shared between application users.

Given your description, the final score depends on a parameter set (that may be the same for multiple users) anyway, which should probably be reflected in the data model ( example: 'parameter sets' could be a table of their own, containing a 'final_score' column. A table 'ranking' might consist of FKs to tables 'parameter set' and 'user' and a 'score' column. The 'user' table would contain a FK on 'parameter sets'. A user query for their score would then be translated into a query for the score given a specific parameter set ).

Community
  • 1
  • 1
collapsar
  • 17,010
  • 4
  • 35
  • 61
  • So, if in an office, two employees are using the application simultaneously, the temporary table will hold different data for both of them? So, at that time, there will be two instances of the same table? – grindel May 22 '16 at 09:55
  • @grindel yes, that's correct. See [this oracle base article](https://oracle-base.com/articles/misc/temporary-tables) or the answers to [this SO question](http://stackoverflow.com/questions/2671518/how-to-create-a-temporary-table-in-oracle). Technically, iirc, there is s single table server-side; however, the data it contains is isolated by db session, so from a user's perspective there is indeed 1 instance per session. – collapsar May 22 '16 at 10:02
  • 1
    @grindel - There would only be one table. Each session would only be able to see the data that was local to their session. That works well in old school client/ server applications. In a three-tier application where the two users have different application sessions but the middle tier maintains a pool of database connections such that one application session may use many different database connections and multiple application sessions may use the same database connection at different points in time, life gets more complicated. – Justin Cave May 22 '16 at 10:03