Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: locks inside stored procedures

    Hi,
    I have 2 stored procedures that I call from CLI (I work with DB2 ESE 10.1 for LUW).

    In proc1 I have

    ----
    select x into myvar from TABLE;
    IncrementFunction(myvar); --here myvar is incremented following some logic
    update TABLE set x= myvar;
    ----

    In proc 2 I have

    -------
    update TABLE set x= y;
    select x into myvar from TABLE;
    -------

    The procedures can be called from within multiple processes so I would like the operations to be serialized.
    I use autocommit=true from CLI, what else should I care about to reach what I want?
    To be clearer I make an example in case 2:
    I have process1 invoking proc2 --- TABLE is updated with val1
    I have process2 invoking proc2 --- TABLE is updating with val2
    process1 selects and gets val2 -- is it a possible error? how can I avoid it?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Locks work the same way in DB2 regardless if they are obtained within a Stored Procedure or not. If in your example if process1 does not commit before process2 runs, process2 will wait for the update from process1 to commit before proceeding (or timeout). If on the other hand, you issue a commit between the update and select in perocess1, then you can get what you describe, but it is only an error if it is in your processes, in which case YOU need to do the processes correctly.

    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Thanks Andy,
    so if I understand it correctly, since I don't commit within the stored procedures, I should not have the problems I was worried about.

    So in case of proc2

    I have process1 invoking proc2 --- TABLE is updated with val1
    I have process2 invoking proc2 --- TABLE is NOT updated with val2 since proc2 will wait for process1 to commit
    process1 selects and gets val2 -- val1 is returned

    In case of proc1

    select x into myvar from TABLE;
    IncrementFunction(myvar); --here myvar is incremented following some logic
    update TABLE set x= myvar;

    should the same occur or should I use select .. FOR UPDATE ?

    Thank you very much for your kind reply!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use SELECT ... FOR UPDATE.

    Andy

Posting Permissions

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