Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    50

    Unanswered: How to lock records

    How can I achieve a pessimistic lock in a DB2 sql stored procedure?
    Can I start a transaction with serializable isolation level?
    how?

    Thank you,
    Dan

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you want a pessimistic lock on data that you will eventually update, use the FOR UPDATE clause on the select.
    I am not sure what you mean by serializable, but you can use the "WITH RR USE AND KEEP EXCLUSIVE LOCKS". For further info:

    http://publib.boulder.ibm.com/infoce...n/r0000879.htm

    Andy

  3. #3
    Join Date
    Sep 2007
    Posts
    50
    Thanks Andy.
    Just a question: how can I span the lock over multiple commands (for example in a stored proc that executes a few queries and calls some other stored procs) ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Locks will stay until either a COMMIT or ROLLBACK.

    Andy

  5. #5
    Join Date
    Sep 2007
    Posts
    50
    Cheers mate!

  6. #6
    Join Date
    Sep 2007
    Posts
    50
    can you help me with this one as well please:

    what if instead of lock for update I want to lock the whole table for an insert?

    Something like:

    declare seqNo INT;
    select sequence_number into seqNo
    from myTable
    order by sequence_number desc
    fetch first 1 row only;
    set seqNo = seqNo + 1;
    ...do more stuff here ...
    insert into myTable (sequence_number) VALUES (seqNo);


    but with a lock around it so that I don't get concurrency issues?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try this:

    select * from final table (insert into myTable (sequence_number) VALUES ((select max(sequence_number) + 1 from myTable));
    commit;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by iskander
    can you help me with this one as well please:

    what if instead of lock for update I want to lock the whole table for an insert?

    Something like:

    declare seqNo INT;
    select sequence_number into seqNo
    from myTable
    order by sequence_number desc
    fetch first 1 row only;
    set seqNo = seqNo + 1;
    ...do more stuff here ...
    insert into myTable (sequence_number) VALUES (seqNo);


    but with a lock around it so that I don't get concurrency issues?
    This might work also:

    declare cursor1 cursor with hold for select max(sequence_number) from myTable for update;
    declare seqNo INT;

    open cursor1;
    fetch cursor1 into seqNo;

    set seqNo = seqNo + 1;
    ...do more stuff here ...
    insert into myTable (sequence_number) VALUES (seqNo);

    close cursor1;

    Andy

  9. #9
    Join Date
    Sep 2007
    Posts
    50
    Thanks guys, will try

Posting Permissions

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