Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Exclamation Unanswered: row locking problem

    Hi guys
    I am very much new to DB2

    I am having a problem for getting lock on records in DB2 V5R2 UDB on our AS/400 (iSeries)

    How to get a lock on ROW (not a table) for a set of transactions so that

    1. Other users should be able to update other records (on which lock is not there)

    2. Other users should not be able to see un-committed records

    3. Other users should be able to select committed records including record which is locked.

    4. Other users should NOT go into wait state if their select statement includes a record updated by any other user.
    Rather he get the last committed record.

    I have tried many permutation and combination for the same with Isolation levels and RS and CS but all in vain

    if any one can help me out..

    Can you also tell me how exactly
    Select * from <table name > where <condition> with RS / CS works internally.

    Does it internally commits the records in the file till it is committed at transaction level / rollbacked. please do send in depth information regarding this too.

    Thanks in advance

    Regards
    Manav Khanna

  2. #2
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    No rollback segments in DB2

    DB2 doesn't let you do this.

    Oracle will allow you to select the "before update" version of the row as it uses rollback segments/redo tablespaces in order to store this information. DB2 doesn't have this feature, so you can either:-

    1. Implement your own version of rollback segments using triggers/tables
    or
    2. Use select .... with ur to read the uncommited version of the row
    or
    3. wait for the user updating the row to commit.

    So long as you ensure that you code only short transactions and use appropriate indexes, locking doesn't tend to be as big an issue as Oracle people often imply.

    Our DB2 UDB 8.1 (AIX) database is handling 1m web-based transactions a day/ 30 concurrent transactions without any major locking issues.

    jdey@macehill.co.uk

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by khanna.manav
    Can you also tell me how exactly
    Select * from <table name > where <condition> with RS / CS works internally.
    DB2 stores the locks in the locklist memory (Oracle stores the locks on each data page, and sometimes has to extend the page if it needs more room).

    Isolation level affects how long share locks are held (usually the result of a select statement). For RR, the share locks are released at commit time. For RS, the share locks are released when the SQL statement has finished.

    As already mentioned by jdey123, with update locks DB2 will not allow a transaction to see the before image if a row is currently locked by an un-committed update by a different application thread. The application thread will wait (assuming it wants that row) until the lock is released (which occurs when the first application commits the work).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2005
    Posts
    4

    Question Is this an abnormal behaviour

    Thanks for your reply guys,
    theres one more case with RS

    If a user say User_1 in Read Committed mode

    1. Selects a record WITH RS
    2. Update the same record

    Now if another user say User_2 also in read committed mode is able to see
    this new updated record WHY??
    3. Select the same updated record.
    it give me the result instead of going into WAIT....

    There no commit invloved till now

    Regards
    Manav Khanna

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Are you sure you turned Autocommit off in user_1's session ... Once you do user1's tasks, check the snapshot to confirm that the locks are held in the database ... Then try user_2's part ....

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2005
    Posts
    4

    Question yeah auto commit is off

    Yeah Auto commit is off
    as i am trying the same through my 2 - java application running on my system.

    where auto commit is set to false as
    i tried to rollback the transaction and it did. The rollbacked result is reflected on both the user's end. If auto commit was not set to false then it could'nt have happened..

    Regards
    Manav Khanna

Posting Permissions

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