Professional Web Applications Themes

Q: How to rowlevel lock a record? - IBM DB2

Hello, I have application that requires to rowlevel lock a record: I have a table named 'Dnevnik' from witch all clients access data. My question is: Is there some way, when one of the client goes to edit mode (button in app) of one record, to lock that record (only reading is available). I need to be notifyed in some way, when other client want's to go to edit mode on same record, that this record is only available for reading. I tryed going into transaction mode and select only one record (pesimistic lock), until the updating of that record ...

  1. #1

    Default Q: How to rowlevel lock a record?

    Hello,

    I have application that requires to rowlevel lock a record:

    I have a table named 'Dnevnik' from witch all clients access data. My
    question is:
    Is there some way, when one of the client goes to edit mode (button in app)
    of one record, to lock that record (only reading is available).
    I need to be notifyed in some way, when other client want's to go to edit
    mode on same record, that this record is only available for reading.

    I tryed going into transaction mode and select only one record (pesimistic
    lock), until the updating of that record is done (button save, cancel), but
    then i found no way to access that record on other clients.

    using: db2 7.2, vb.net, odbc connection to db2.

    thanks for reading this q

    best re, habix


    HABJAN Guest

  2. #2

    Default Re: How to rowlevel lock a record?

    Can you be more specific about what tool or app you are using? Is that the
    "Table Editor"?
    To get a an UPDATE lock on a single row from an SQL point of view you need
    to open a cursor FOR UPDATE and fetch the row.

    Cheers
    Serge


    Serge Guest

  3. #3

    Default Re: Q: How to rowlevel lock a record?

    "HABJAN ®iga" <habix[AT]intelcom[DOT]si> wrote in message news:<newsguy.com>... 


    SELECT ... WITH UR will read dirty data no problem 
    AK Guest

  4. #4

    Default Re: Q: How to rowlevel lock a record?

    Thanks for response.

    Is there a SQL statement to ask myself if a record is locked? or do i have
    to call dummy update to get error (record locked...)?

    best regards,
    habix

    "AK" <com> wrote in message
    news:google.com... 
    news:<newsguy.com>... [/ref]
    app) [/ref]
    edit [/ref]
    (pesimistic [/ref]
    but 
    >
    >
    > SELECT ... WITH UR will read dirty data no problem [/ref]


    HABJAN Guest

  5. #5

    Default Re: Q: How to rowlevel lock a record?

    "HABJAN Ziga" <habix[AT]intelcom[DOT]si> wrote in message
    news:newsguy.com... 
    Neither.

    There is no statement to lock a row, but you can issue an SQL statement to
    lock a table. There is no SQL statement to determine lock status. There are
    system monitors that can probably do this. DB2 automatically locks the
    objects depending on the SQL statements involved and the Isolation Level
    (RR, RS, CS, UR).

    As previously mentioned, if you want to read a row and prevent others from
    updating it, then use the SELECT ...FOR UPDATE syntax. This will give you an
    SIX lock (share with intent to update).

    If a row has an SIX lock (share with intent to update), no other program can
    take another SIX lock or an X lock (to actually perform the Update) until
    you release the lock (usually with a commit).

    If another program attempts to take an SIX or X lock while your SIX or X
    lock is held, then they will wait for a while until you release it. If they
    wait past the wait parameter established in DB2 (can be set by the DBA) the
    program that is waiting will get a negative return code indicating a timeout
    (or in some cases a deadlock). But this depends on the timeout wait
    parameter that is established.


    Mark Guest

  6. #6

    Default Re: Q: How to rowlevel lock a record?

    "HABJAN Ziga" <habix[AT]intelcom[DOT]si> wrote in message news:<newsguy.com>... 

    were you ever considering optimistic lpcking rather than pessimistic?
    I'd say optimistic is much easier to deal with
    AK Guest

  7. #7

    Default Re: Q: How to rowlevel lock a record?

    I guess i can't use optimistic locking, because it locks table or row only
    on commit.

    I needed to lock the row for entire time of editig... (even if i rollback)

    Anyways :o), i solved the trouble with Remoteing .NET, and shared object
    that handles all of the clients locks in simple array of ID's :)

    thanks for all the help you guys,

    best re,
    habix


    "AK" <com> wrote in message
    news:google.com... 
    news:<newsguy.com>... [/ref]
    have 
    >
    > were you ever considering optimistic lpcking rather than pessimistic?
    > I'd say optimistic is much easier to deal with[/ref]


    HABJAN Guest

Similar Threads

  1. Record Lock in Oracle
    By CFRAM in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 15th, 03:59 AM
  2. Replies: 1
    Last Post: October 3rd, 04:20 PM
  3. Stop adding record in subform after record count = 1
    By Charlie in forum Microsoft Access
    Replies: 0
    Last Post: July 31st, 10:42 AM
  4. Lock record
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 04:14 PM
  5. Releasing a Record Lock
    By Scott in forum FileMaker
    Replies: 0
    Last Post: June 26th, 05:29 PM

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