Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Posts
    58

    Unanswered: select for update

    hi,
    i want to lock a table from the moment i read a record.
    i know "select for update" locks it from updates, but i don't want others even to read the record until i commit the records.
    any ideas ?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    use the WITH RR clause:

    SELECT COL1 from mytable where (col2 = x) FOR UPDATE WITH RR

    HTH

    Andy

  3. #3
    Join Date
    Jun 2003
    Posts
    58
    we tried that, didn't help...
    for db2 on NT it works...
    the problem is with db2 on os/390
    do you know something about it ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know anything specific about OS/390 but you could try to lock
    the table in exclusive mode. This will prevent everyone else from accessing the table until you commit.

    Andy

  5. #5
    Join Date
    Jun 2003
    Posts
    58
    yep,
    but it's a sequence table, which means it get accessed all the time, and we don't want to lock all the table, but only the one record that's changed.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Well, I cannot help you with OS/390. I suggest you repost the question and mention OS/390 this time. I also suggest you first scan the archives to see if this has already been answered.

    Andy

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Why not bind your application package with isolation level RR ...

    Cheers
    Sathyaram

    Originally posted by maayanp
    yep,
    but it's a sequence table, which means it get accessed all the time, and we don't want to lock all the table, but only the one record that's changed.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jun 2003
    Posts
    58
    it's dynamic...
    there are in the general plan (of WSAD) 4 paackgesfor every isolation kevel. so it's supposed to be ok... (but it's not)

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the locksize of the tablespace set at?

  10. #10
    Join Date
    Jun 2003
    Posts
    58
    ANY

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Change it to ROW. Locksize ANY starts at a page lock and can escalate to a table lock. I suspect that your table is quite small anyway and a page lock is about the same as table lock. But to get a ROW lock, you have to explicity ask for it.
    Last edited by Marcus_A; 09-11-03 at 07:00.

Posting Permissions

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