Results 1 to 13 of 13

Thread: Oracle issue

  1. #1
    Join Date
    Feb 2005
    Posts
    48

    Question Unanswered: Oracle issue

    Hello all ,

    i have users doing a "select %A for update" and others doing "select %AB for update" ..
    suppose the user doing the first one , was away after lauching this select , the second user should wait until he finishes it ?
    is there any way so that both users can work and keeping data consistency ?
    i thought about creating a FIFO file , or circualr file , does this help ?

    Tx for the replies
    Regards

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    But data consistency is already there if you remove the 'FOR UPDATE' clause. From the reference manual The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries. And yes, users that select record %A for update, will block other users until they end the transaction. There's a dark point in your scenario, try to explain better what you're trying to do.

  3. #3
    Join Date
    Feb 2005
    Posts
    48

    Question oracle issue

    Hi,

    i don't want users doing select %A for update to take the lock and maybe they will not leave it for a time while users doing select %AB for update are waiting .

    i want to allow both users to do their jobs .

    i thought about a FIFO file, or a circualr file , or maybe create a table in oracle that will hold the requests ( select for update) and execute them one after one .is it feasible ?

    the problem is that users doing these kind of select for update , leave their offices for some time while others are still working and waiting the locks to be released.

    someone told me something about "Concurrent manager" in Oracle Applications that take the requests and execute them in a First In First Out .
    and the system will not have any bottleneck.

    Tx For ur replies
    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to explain your requirements more clearly. If you want users to be able to fire off updates but not wait for them to complete then maybe you need to look at DBMS_JOB? Or maybe you just want to use optimistic locking instead of pessimistic? i.e. don't select for update, but when doing update check that the record has not been updated by someone else since you selected it.

  5. #5
    Join Date
    Feb 2005
    Posts
    48

    Question Oracle issue

    Hi
    i have around 100 users , that might access and update the same data , in the same table at the same time , could this be done without having a deadlock ? or hanging in the system ?
    what is the best way to access the same data and update it in the same time?
    we thought about creating an external Function that each user will access it whenver he wants to do an update instead of updating the records direclty from the Form...

    Tx

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You mean that 100 users might try to update the same row in the same table at the same time? If so, why???

    There is no problem with 100 users updating different rows in the same table at the same time.

    I suspect you are worrying about a problem that doesn't arise, simply because neither you nor your colleagues has any (Oracle) database experience. You should perhaps start by reading the Oracle Concepts Guide, and try to get hold of a copy of Effective Oracle by Design (Tom Kyte), rather than trying to introduce FIFO files and goodness knows what other inappropriate "solutions" to this non-problem!

  7. #7
    Join Date
    Feb 2005
    Posts
    48

    Exclamation Oracle Issue

    Hi,

    many users are doing select %A or %AB or %AZ from table1 for update and so on ....
    so it could happen that 2 users or more are doing an update on the same row at the same time ..

    the enqueue mechanism in Oracle will resolve this issue ?
    i don't want a user to wait to do its transaction if the other one who's having aa lock on a row is away or not doing any work ....

    is that feasible ?

    Tx

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it is feasible: use optimistic locking instead of pessimistic locking. That means not letting users select for update while they look at the data on their screen, and maybe go to lunch. Instead, when they come back from lunch and press the Save button the application performs the update, but only if no one else changed the data since they selected it. There are various ways to do that, such as holding all the selected values in variables and then updating like this:
    Code:
    UPDATE emp SET sal = sal + 1000, bonus=500
    WHERE empno = :selected_empno
    AND sal = :selected_sal
    AND bonus = :selected_bonus;
    IF SQL%ROWCOUNT = 0 THEN
      show_error('Sorry, someone else updated it while you were having lunch');
    END IF;
    Another way is to maintain an update_number or update_timestamp on each row (probably maintained by a trigger), and just check whether that has changed:
    Code:
    UPDATE emp SET sal = sal + 1000, bonus=500
    WHERE empno = :selected_empno
    AND update_timestamp = :selected_timestamp;
    IF SQL%ROWCOUNT = 0 THEN
      show_error('Sorry, someone else updated it while you were having lunch');
    END IF;
    Search Ask Tom for "optimistic locking" for more details.

  9. #9
    Join Date
    Feb 2005
    Posts
    48

    Exclamation Oracle Issue

    Hi

    what about the Lock mode ? which one is the best to use in this case?

    and something else , since i have 100 users accessing my server , i was thinking about using a Shared Server with dispatchers to decrease the Slowness while retrieving and querying ? what do u suggest ..?

    Tx
    Regards

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I am not suggesting using any lock mode. I am saying don't lock the record at all when the user selects it. Instead, wait until the user tries to save his changes and then update the record on condition that no one else changed it in the meanwhile.

    I can't comment on use of shared server etc. as I'm not a DBA. Perhaps you should hire someone who is?

  11. #11
    Join Date
    Feb 2005
    Posts
    48

    Exclamation Oracle Issue

    Hi,

    i was thinking about another issue also regarding the locks ..


    i have user1 doing a select and in the same time user2 is doing the same select .

    now user2 did an update on a field and commit ;
    user1 is still seeing the uncommitted value and maybe he will do an update on the same field , how to overcome this issue from happenning?

    this issue of locks is making a pb for us ..

    Tx

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I have detailed how to do exactly this 2 posts back. It is called optimistic locking. Did you not read that?

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    alfrednone, did you read andrewst suggestion on post #8 above ?

    Are you looking at a problem that is _happening_ or are you just questioning before the problem arises ?

    What kind of application is this ? (Web, Desktop, Batch, etc..)

    Shared server with dispatcher will not health anything if your problems are on the application side.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •