Professional Web Applications Themes

Locking problem ! - IBM DB2

Hi, i want to lock an defined area of records in a table (e.g. all records to a defined date). No other application should access this record and no insert with this date should be possible. As far as I know it is not possible to achive this with the possibilities a database (DB2 / Oracle) gives me (select/fetch for update or lock table,..). So I think I have to lock the records logical. Me idea was to write a record in a kind of "lock table". But now I have the problem that if the application will crash, the ...

  1. #1

    Default Locking problem !

    Hi,

    i want to lock an defined area of records in a table (e.g. all records
    to a defined date). No other application should access this record and
    no insert with this date should be possible. As far as I know it is
    not possible to achive this with the possibilities a database (DB2 /
    Oracle) gives me (select/fetch for update or lock table,..). So I
    think I have to lock the records logical. Me idea was to write a
    record in a kind of "lock table".
    But now I have the problem that if the application will crash, the
    record in the lock table is still there (my application commits after
    every record and I can't change this !).

    Thank you for helpful ideas !!!

    Jochen
    Jochen Guest

  2. #2

    Default Re: Locking problem !

    Jochen wrote:
    >Hi,
    >
    >i want to lock an defined area of records in a table (e.g. all records
    >to a defined date). No other application should access this record and
    >no insert with this date should be possible. As far as I know it is
    >not possible to achive this with the possibilities a database (DB2 /
    >Oracle) gives me (select/fetch for update or lock table,..). So I
    >think I have to lock the records logical. Me idea was to write a
    >record in a kind of "lock table".
    >But now I have the problem that if the application will crash, the
    >record in the lock table is still there (my application commits after
    >every record and I can't change this !).
    >
    >Thank you for helpful ideas !!!
    >
    >Jochen
    >
    >
    It can be done with Oracle but not with the built-in locks. Oracle
    supplies the DBMS_LOCKS package so that developers can create their own
    in situations such as this. I can't recall but I would think DB2 must
    also have a similar ability.

    --
    Daniel Morgan
    [url]http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp[/url]
    [url]http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp[/url]
    [email]damorganx.washington.edu[/email]
    (replace 'x' with a 'u' to reply)

    Daniel Morgan Guest

  3. #3

    Default Re: Locking problem !

    Jochen <Jochen.Kraemerweb.de> wrote:
    > Hi,
    >
    > i want to lock an defined area of records in a table (e.g. all records
    > to a defined date). No other application should access this record and
    > no insert with this date should be possible. As far as I know it is
    > not possible to achive this with the possibilities a database (DB2 /
    > Oracle) gives me (select/fetch for update or lock table,..). So I
    > think I have to lock the records logical. Me idea was to write a
    > record in a kind of "lock table".
    > But now I have the problem that if the application will crash, the
    > record in the lock table is still there (my application commits after
    > every record and I can't change this !).
    Have a look here:

    [url]http://www7b.boulder.ibm.com/dmdd/library/techarticle/0302stolze/0302stolze.html[/url]

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  4. #4

    Default Re: Locking problem !

    Daniel,
    i would disagree. REPEATABLE READ gives you this functionality
    > It can be done with Oracle but not with the built-in locks. Oracle
    > supplies the DBMS_LOCKS package so that developers can create their own
    > in situations such as this. I can't recall but I would think DB2 must
    > also have a similar ability.
    it's very easy to just SELECT ... WITH RR.
    AK Guest

  5. #5

    Default Re: Locking problem !

    "AK" <ak_tiredofspam> wrote in message
    news:46e627da.0309151212.57476780posting.google.c om...
    > Daniel,
    > i would disagree. REPEATABLE READ gives you this functionality
    >
    > > It can be done with Oracle but not with the built-in locks. Oracle
    > > supplies the DBMS_LOCKS package so that developers can create their own
    > > in situations such as this. I can't recall but I would think DB2 must
    > > also have a similar ability.
    >
    > it's very easy to just SELECT ... WITH RR.
    They want to prevent new rows from being inserted within a certain range of
    keys, in addition to locking existing rows.


    Mark A Guest

  6. #6

    Default Re: Locking problem !

    To the best of my knowledge, RR will stop A)Inserts that would qulify
    for the where clauses in the result set locked B) Deletes that would
    remove rows from that set C) Updates that would qualify for a where
    clause of the statement that generates the locked set.
    RR generally will therefore request a strict share lock on the table.
    HTH, Pierre.

    Mark A wrote:
    > "AK" <ak_tiredofspam> wrote in message
    > news:46e627da.0309151212.57476780posting.google.c om...
    >
    >>Daniel,
    >>i would disagree. REPEATABLE READ gives you this functionality
    >>
    >>
    >>>It can be done with Oracle but not with the built-in locks. Oracle
    >>>supplies the DBMS_LOCKS package so that developers can create their own
    >>>in situations such as this. I can't recall but I would think DB2 must
    >>>also have a similar ability.
    >>
    >>it's very easy to just SELECT ... WITH RR.
    >
    >
    > They want to prevent new rows from being inserted within a certain range of
    > keys, in addition to locking existing rows.
    >
    >
    P. Saint-Jacques Guest

  7. #7

    Default Re: Locking problem !



    AK wrote:
    > Daniel,
    > i would disagree. REPEATABLE READ gives you this functionality
    >
    > > It can be done with Oracle but not with the built-in locks. Oracle
    > > supplies the DBMS_LOCKS package so that developers can create their own
    > > in situations such as this. I can't recall but I would think DB2 must
    > > also have a similar ability.
    >
    > it's very easy to just SELECT ... WITH RR.
    Yes, that's it.

    select/fetch for update
    +
    isolation level = RR




    Fan Ruo Xin Guest

  8. #8

    Default Re: Locking problem !

    We had a similar problem (different data condition), and solved it using
    views.

    Remember that you can:
    1) place access control on views (GRANT),
    2) restrict inserts/updates to data satisfying the views conditions (WITH
    CHECK OPTION).

    Locking is far too heavy, especially if your table is large (ours has many
    millions of rows).

    "Jochen" <Jochen.Kraemerweb.de> wrote in message
    news:366dbd47.0309150452.7def1f74posting.google.c om...
    > Hi,
    >
    > i want to lock an defined area of records in a table (e.g. all records
    > to a defined date). No other application should access this record and
    > no insert with this date should be possible. As far as I know it is
    > not possible to achive this with the possibilities a database (DB2 /
    > Oracle) gives me (select/fetch for update or lock table,..). So I
    > think I have to lock the records logical. Me idea was to write a
    > record in a kind of "lock table".
    > But now I have the problem that if the application will crash, the
    > record in the lock table is still there (my application commits after
    > every record and I can't change this !).
    >
    > Thank you for helpful ideas !!!
    >
    > Jochen

    Mark Yudkin Guest

  9. #9

    Default Re: Locking problem !

    [email]Jochen.Kraemerweb.de[/email] (Jochen) wrote in message news:<366dbd47.0309150452.7def1f74posting.google. com>...
    > Hi,
    >
    > i want to lock an defined area of records in a table (e.g. all records
    > to a defined date). No other application should access this record and
    > no insert with this date should be possible. As far as I know it is
    > not possible to achive this with the possibilities a database (DB2 /
    > Oracle) gives me (select/fetch for update or lock table,..). So I
    > think I have to lock the records logical. Me idea was to write a
    > record in a kind of "lock table".
    > But now I have the problem that if the application will crash, the
    > record in the lock table is still there (my application commits after
    > every record and I can't change this !).
    >
    > Thank you for helpful ideas !!!
    >
    > Jochen

    Thanx for your answers !!! I am sorry, i havn't told you that our
    isolation level is "read committed" (Oracle) respectively "cursor
    stability" (db2). We have to support both databases. For this reason
    we dicided to work with this isolation levels. They are more or less
    compareable.


    Jochen
    Jochen Guest

Similar Threads

  1. Contribute Locking Content Problem
    By PhilipZaengle in forum Macromedia Contribute Connection Administrtion
    Replies: 0
    Last Post: February 10th, 08:15 PM
  2. Need Help with Locking
    By CFRAM in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: July 28th, 03:16 AM
  3. DB2 locking UDF dll
    By W Gemini in forum IBM DB2
    Replies: 2
    Last Post: August 7th, 03:23 AM
  4. Locking file problem
    By kam in forum ASP.NET General
    Replies: 0
    Last Post: July 31st, 03:28 AM
  5. Problem with VB6 App & SQL 2000 (Locking & lost transactions)
    By Javier Villegas in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: March 29th, 03:10 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139