This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Sep 24, 2010 5:29 AM by 635471 Go to original post RSS
  • 30. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    This is the information I found in the trace file for teh deadlock..see if its of any help.

    DEADLOCK DETECTED
    [Transaction Deadlock]
    Current SQL statement for this session:
    SELECT CURRENT_VALUE FROM MY_SEQUENCES WHERE SEQ_NAME = :B1 FOR UPDATE OF CURRENT_VALUE
    ----- PL/SQL Call Stack -----
    object line object
    handle number name
    0x507352b4 7 function OMERO.SEQ_FUN
    The following deadlock is not an ORACLE error. It is a
    deadlock due to user error in the design of an application
    or from issuing incorrect ad-hoc SQL. The following
    information may aid in determining the deadlock:
    Deadlock graph:
    ---------Blocker(s)-------- ---------Waiter(s)---------
    Resource Name process session holds waits process session holds waits
    TX-0005001a-0000494e 21 20 X 21 20 X
    session 20: DID 0001-0015-00000BED session 20: DID 0001-0015-00000BED
    Rows waited on:
    Session 20: obj - rowid = 00007B18 - AAAHsYAABAAALziAAA
    (dictionary objn - 31512, file - 1, block - 48354, slot - 0)
    Information on the OTHER waiting sessions:
    End of information on OTHER waiting sessions.
  • 31. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Can you explain the deadlock? Or are you just being sarcastic?

    The code could cause a chain of waiters.
    But it can not cause a circle of waiters.

    I can think of a reason though...
    But need to see the tracefile for that.
  • 32. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Ok, can you reproduce this consistently?

    If so, please tell me (show it here) how to do so.
  • 33. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    I have tried running it multiple times again in 3 sessions....and it seems to be working....why did I get a Dead lock in the first try...I am not sure...you have seen my trace file....any ideas....why I got the dead lock the frist time and then now I am not getting any dead lock...will appreciate your response...you have been very helpfull unlike many and I really do appreciate that. Thanks a lot for all your help.
  • 34. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Can you describe to me how you ran those three sessions the first time?

    Did you have three sqlplus sesssions?
    Did you request a block of numbers from the same sequence in those three sessions?

    The tracefile is a bit of a mistery to me, for now, since there is no other-waiting-sessions information in it...
  • 35. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    How I tested is was, I had three oracle sessions opened in three different windows...and i called the function from all the three windows with probably .5 of sec difference from one from anothere. The first time around it gave me a dead lock...and ever since its been working right....as expected...
  • 36. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Were/was the row(s) that you inserted into the my_sequences table already committed?
    Or where they still uncommitted in the session (one of the three) that you used to insert them?
  • 37. Re: Sequence Issue
    William Robertson Oracle ACE
    Currently Being Moderated
    jihuyao wrote:
    increment by 1000 is good option here. get one sequence # and return 1000 for use.
    I agree. I can't run up a demo right now but I was thinking along the same lines.
  • 38. Re: Sequence Issue
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    mkmunir wrote:
    How I tested is was, I had three oracle sessions opened in three different windows...and i called the function from all the three windows with probably .5 of sec difference from one from anothere. The first time around it gave me a dead lock...and ever since its been working right....as expected...
    And what happens when it deadlocks again? What happens when this approach results in a severe performance bottleneck due to serialisation?

    How are you going to address this then? You are guarantee that it will happen (again) - as you are explicitly using Oracle contrary to best practice methods.

    So why dig that hole in the current when you will fall into it sometime in the future? And no, saying that "+this is a requirement from the client+" is not acceptable. Software is designed to meet user and business requirements. Such requirements by the very nature is not, and never can be, technical in nature. Actual and proper User Requirements can never place artificial limits on how the s/w is designed and coded.

    If you cannot understand that, or convey this fact to the client... then it is an <font color="red">Epic Fail</font> on your part. Design and code for failure? That approach deserves utter and total contempt.
  • 39. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    Well, I do not understand why you are getting so frustrated and making personal attacks on me. I looked into the issue, the dead lock was because of a forgotten COMMIT command. The function is working as per requirements as of now. The requirements are, 'NO HOLES IN THE NUMBERS GATHERED FROM THE SEQUENCE BY ANY INDEPENDENT PROCESS' and I cannot change these requirements..This is the best solution for this scenario for now. I am still open to other suggestions, but for now the best suggestion on the table is the one I am using. I read a lot about it, and as I said earlier, I tried everything from BULK Collection to creating my own table to have a process control flag to make the processes run in a queue, one after an other, to looking at the V$ tables to control the processes, and I guess this is the simplest and the best solution for now. The DEAD LOCK was becasue of human error, not program logic.
  • 40. Re: Sequence Issue
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    mkmunir wrote:
    Well, I do not understand why you are getting so frustrated and making personal attacks on me.
    I "+attacked+" the approach - not you personally. And IMO, that approach deserves to be "+attacked+" as it is wrong.
    I looked into the issue, the dead lock was because of a forgotten COMMIT command.
    And what about a so-called forgotten commit in future? What about forcing serialisation?
    The requirements are, 'NO HOLES IN THE NUMBERS GATHERED FROM THE SEQUENCE BY ANY INDEPENDENT PROCESS' and I cannot change these requirements..
    This is not a requirement. What you have written is an expected solution for some kind of problem. And that is where the failure is.

    User requirements are just that - requirements. These exclude what the user may think the solution should or should not be. Business Analysis 101.
  • 41. Re: Sequence Issue
    731280 Explorer
    Currently Being Moderated
    You say it is a REQUIREMENT that your 'sequence' be gap-free.

    I pointed out that you can not guarantee that, because you use an autonomous transaction. You need to recognize that your design is vulnerable to gaps. You need to tell the client (who is insistent that there be no gaps) that they may happen from time to time. What will be done then?

    When you get that answer, it may be 'well, that should be OK.' Then you should go back and re-design knowing that gaps are OK - which may lead you back to using an Oracle SEQUENCE.

    If the answer is not 'well, that should be OK' then you need to redesign anyway.
  • 42. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    I'm tired of hearing people insist that "gaps are OK" and shrieking that gap-free sequences are a perfomance killer -- it's becoming a religion.

    Sometimes there is a legal requirement that a sequence be gap free. If you don't like it then go live in a universe where laws are subserviant to software development principles.

    Sometimes gap free sequences are not a performance killer, because you have very few sessions trying to use them concurrently and the time lost due to serialisation on generating the sequence numbers is a small fraction of the total processing time, or the generation of the sequence values can be performed retrospectively.

    So, sometimes the requirement for gap-free sequences is unneccessary, but sometimes it has to be respected. Just explain the issues, and move on.
  • 43. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    But this value (the 1000) is supposed to be variable. It is not fixed. Which is why I came up with my solution.

    As for all the other replies on this thread:

    - I ofcourse subscribe to the serialization going on in my solution. The question is, though, whether that is an issue for the OP. Everybody seems to assume that it is. Well maybe it isn't, and will requests for blocks of sequence only happen concurrently now and then, in which case a sub millisecond wait might very well be affordable.

    - The deadlock that occurred just happened because the setup of rows in the my_sequences table, wasn't committed yet, while testing the function inside the same session: this caused that session to deadlock with itself. What bothers me though, is that apparently there seems to be some 'consensus' here on this forum that autonomous transactions always cause deadlocks... This of course is not true.

    - And yes the solution is not guaranteed gap-free as mentioned. The OP should indeed be aware of that.
  • 44. Re: Sequence Issue
    BluShadow Guru Moderator
    Currently Being Moderated
    Toon Koppelaars wrote:
    - The deadlock that occurred just happened because the setup of rows in the my_sequences table, wasn't committed yet, while testing the function inside the same session: this caused that session to deadlock with itself. What bothers me though, is that apparently there seems to be some 'consensus' here on this forum that autonomous transactions always cause deadlocks... This of course is not true.
    Nah, not a consensus. I've never considered autonomous transactions as a real cause of deadlocks, but rather a potential cause of data inconsistency.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points