Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: DB2 locking and isolation levels

    Hi, I am fairly new to DB2. I have been working on Oracle for a while now. I am having trouble understanding DB2's default behavior when it comes to locking and isolation levels. I would appreciate it if someone would provide any insight. Thanks so much.

    I have read and have been told that DB2 by DEFAULT does ROW level locking just like Oracle. I installed DB2 on my laptop and haven't changed anything to it's configuration, so it's running right out the box. Now I do the following..

    1. Open two CLP windows, connected to the same database as two different users

    Machine A
    DB2 connect to db1 user admin1 using admin1
    db2 +c "update master.employee set lastname = 'NEW' where empno='000010'"

    Machine B
    db2 connect to db1 user admin2 using admin2
    db2 "select * from master.employee /* Hangs until I commit in admin1's window. */
    I also tried running
    db2 +c update employee set lastname = 'JASTI' where empno='000010' /* It hung until I committed in admin1's tran. as well */

    How and why in the world is DB2 not allowing user admin2 to select from employee? And if DB2 advertises they do row level locking, how come if I just update 1 row out of the 10000 rows in the table, it locks the entire table and does not let the second user to update another row in the same table?

    In Oracle, you only get a exclusive ROW level lock on the row updated and the rest of the users can update the reminder of the unlocked rows and of course can select any row they want and they will get the read consistent data before the update until the user holding the lock commits or rolls back.

    My questions..
    I thought the default behavior in DB2 for locking is ROW level. Is it not?
    What is the default Isolation level for DB2?
    And how do I have DB2 let different users update different users in the same table?
    How do I show the current isolation level at the database/database manager level?
    How do I set the isolation level at the database/database manager level?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 locking and isolation levels

    Originally posted by ryanveach

    Machine B
    db2 connect to db1 user admin2 using admin2
    db2 "select * from master.employee /* Hangs until I commit in admin1's window. */

    How and why in the world is DB2 not allowing user admin2 to select from employee? And if DB2 advertises they do row level locking, how come if I just update 1 row out of the 10000 rows in the table, it locks the entire table and does not let the second user to update another row in the same table?


    My questions..
    I thought the default behavior in DB2 for locking is ROW level. Is it not?
    What is the default Isolation level for DB2?
    And how do I have DB2 let different users update different users in the same table?
    How do I show the current isolation level at the database/database manager level?
    How do I set the isolation level at the database/database manager level?

    First of all, you're right about row-level locking being default behaviour.

    1) It seems to me your second session is trying to update the same row as the first one: "...where empno='000010'". Since the change made by the first session has not been committed (your "db2 +c" turns autocommit off) the row remains locked.

    2) Default isolation level for the "select" statement is CS (cursor stability), which prevents the second connection from seeing uncommited changes from the first connection. This is why your select locks.

    3) Isolation level is set at the connection level, not the database/dbm level. To change it use "change isolation to ur | cs | rr | rs" before connecting to the database. Alternatively, you can use "select ... with ur" to execute that specific query with the isolation level you need.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: DB2 locking and isolation levels

    Answers to your questions:

    1) Yes, Row Level locking is the default.
    2) Default Isolation Level is CS
    3) Updating different rows concurrently is fine due to row level locking.
    4) the isolation level is not set at the DB/DBM level. Each clients sets their own isolation level. I have not figured how to show it yet.
    5) see 4. to change: CHANGE ISOLATION LEVEL TO CS,RR,RS,UR

    Now why you got the behavior you got
    For reading while updating. The update held a row lock on the row being changed. Since isolation level is CS, the read will not read uncommitted data, It will wait until the locks are clear to proceed.

    The update did not work because you were trying to update a row that was locked and uncommitted. It to will wait until the lock is released by either a commit or a rollback.

    You can specify a timeout to wait for locks. This defaults to no timeout. You set this at the database level.

    HTH

    Andy

    Originally posted by ryanveach
    Hi, I am fairly new to DB2. I have been working on Oracle for a while now. I am having trouble understanding DB2's default behavior when it comes to locking and isolation levels. I would appreciate it if someone would provide any insight. Thanks so much.

    I have read and have been told that DB2 by DEFAULT does ROW level locking just like Oracle. I installed DB2 on my laptop and haven't changed anything to it's configuration, so it's running right out the box. Now I do the following..

    1. Open two CLP windows, connected to the same database as two different users

    Machine A
    DB2 connect to db1 user admin1 using admin1
    db2 +c "update master.employee set lastname = 'NEW' where empno='000010'"

    Machine B
    db2 connect to db1 user admin2 using admin2
    db2 "select * from master.employee /* Hangs until I commit in admin1's window. */
    I also tried running
    db2 +c update employee set lastname = 'JASTI' where empno='000010' /* It hung until I committed in admin1's tran. as well */

    How and why in the world is DB2 not allowing user admin2 to select from employee? And if DB2 advertises they do row level locking, how come if I just update 1 row out of the 10000 rows in the table, it locks the entire table and does not let the second user to update another row in the same table?

    In Oracle, you only get a exclusive ROW level lock on the row updated and the rest of the users can update the reminder of the unlocked rows and of course can select any row they want and they will get the read consistent data before the update until the user holding the lock commits or rolls back.

    My questions..
    I thought the default behavior in DB2 for locking is ROW level. Is it not?
    What is the default Isolation level for DB2?
    And how do I have DB2 let different users update different users in the same table?
    How do I show the current isolation level at the database/database manager level?
    How do I set the isolation level at the database/database manager level?

    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 locking and isolation levels

    Just curious .... How would have Oracle handled this condition, ie, two apps trying to update the same row ?

    Cheers

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

  5. #5
    Join Date
    Dec 2001
    Posts
    40
    Sorry, first let me correct myself. You guys are right. I was trying to update the same row in both the sessions. That was my fault. I tried updating different rows and it works just like I thought it should.

    But the Select behavior is really wired to me. To answer sathyaram_s question,

    the way it works in Oracle is as follows.

    1. Say we have a table with 100 rows.
    2. User1 updates 10 rows out of the 100 rows, user2 updates 50 rows and user3 updates 1 row. All updating different rows, of course.

    3. Now User4 comes along and does a "select * from <table_name>. What happens? He does not WAIT whatsoever. What the User gets is all 100 rows the way they were before the update (Just like DB2, Oracle will not let the user see the uncommitted changes at the time of the select). The way Oracle accomplishes this is through a database structure called ROLLBACK segments. When data is updated in Oracle, it stores the before and after image in the rollback segments. Now, when some user tries to select the data before the transaction is committed, Oracle uses the rollback segments and gives the user who did a select, a READ consistent image.

    It bothers me, how DB2 can just a place an entire hold on a table for selects when say just one row in a table gets updated. Doesn't this cause problems in enterprise level applications?

  6. #6
    Join Date
    Dec 2001
    Posts
    40
    Hmm.. I actually tested this after I replied to your posts. It still doesn't seem to work. check this out, these are the exact commands I run and just cut and pasted..

    USER1-Time T1
    C:\PROGRA~1\SQLLIB\BIN>db2 +c "update db2admin.employee set lastname='bbb' where empno='000340'"
    DB20000I The SQL command completed successfully.

    USER2-time T2
    C:\PROGRA~1\SQLLIB\BIN>db2 +c "update db2admin.employee set lastname='bbb' where empno='000330'"
    <<<HANGS>>

    USER1-Time T3
    C:\PROGRA~1\SQLLIB\BIN>db2 commit
    DB20000I The SQL command completed successfully.

    USER2-time T4
    DB20000I The SQL command completed successfully.


    I still can't see the ROW level locking. Even though the users update different rows, user2 is still waiting until user1 commits. This is DB2 UDB on NT. can you all think of why this is happening?

    I also check the locksize on the employee table and it is set to ROW and not TABLE.

    Thanks

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for the detailed explanation about Oracle ....

    For select , you can 'simulate' Oracle's behaviour using 'with ur' clause ..

    select * from table1 with ur

    DB2 does not place a lock on the selects for the entire table ... It works like this :

    Whenever a row is to be selected, db2 tries takes a share lock on the row .... It tries the same over the row which you are trying to update(ie uncommitted one) ... DB2 will have a X lock on that row ... X and S locks are not compatible with each other and hence the 'reading' application waits for the lock ... To put in a different way, DB2 thinks ' The user wants all rows from this table .... This row is being updated ... So what value do I return for this row ? .... Let me wait and get the correct value to the user ' .... So, if you are happy to see uncommitted data, use with UR ...

    Comparing db2's and oracle's locking mechanisms :

    IBM's view -
    http://www-3.ibm.com/software/data/p...ng/locking.pdf

    Oracle's view - http://otn.oracle.com/products/oracl...VSDB2_PERF.PDF


    HTH

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

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650


    I'm assuming you are new to the db2 world ...

    When your application is hanging, can you get snapshot for locks and applications on the database ...

    Before doing this test, can you switch on the monitors

    Buffer pool (DFT_MON_BUFPOOL) = ON
    Lock (DFT_MON_LOCK) = ON
    Sort (DFT_MON_SORT) = ON
    Statement (DFT_MON_STMT) = ON
    Table (DFT_MON_TABLE) = ON
    Unit of work (DFT_MON_UOW) = ON


    ie , db2 update dbm cfg using DFT_MON_BUFPOOL ON
    etc
    Disconnect all applications
    Stop db2 - db2stop
    start db2 - db2start

    Make you application 'hang'

    From the third window,
    db2 get snapshot for all on <databasename>
    and redirect the output to a file, say snapshot.txt

    go through the snapshot and it will give details on why the application is waiting ... Under the tail end of 2nd application's snapshot information, you will find what sort of lock the app is trying to take and why this has to wait ...

    HTH

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

  9. #9
    Join Date
    Dec 2001
    Posts
    40
    sathyaram,
    That's really funny. Thanks for the links. And, thanks so much for all the input. I really apperciate it.

    I am familiar with monitor switches, snapshots etc.. The thing is, my test case is so simple..two users, updating two different rows in the same table. I know what DB2 is locking and holding locks on. It's got to be the employee table. Even after I look at the snapshot logs, I will have the same question..

    Why can't two users update two different rows in the same table at the same time?

    --
    USER1-Time T1
    C:\PROGRA~1\SQLLIB\BIN>db2 +c "update db2admin.employee set lastname='bbb' where empno='000340'"
    DB20000I The SQL command completed successfully.
    USER2-time T2
    C:\PROGRA~1\SQLLIB\BIN>db2 +c "update db2admin.employee set lastname='bbb' where empno='000330'"
    <<<HANGS>>
    USER1-Time T3
    C:\PROGRA~1\SQLLIB\BIN>db2 commit
    DB20000I The SQL command completed successfully.
    USER2-time T4
    DB20000I The SQL command completed successfully.
    --
    Thanks

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by sathyaram_s

    For select , you can 'simulate' Oracle's behaviour using 'with ur' clause ..

    select * from table1 with ur
    I think this won't be an exact simulation: Oracle's SELECT would read _old_ values, whereas in DB2 SELECT... WITH UR will fetch the _new_ values.

    As for the UPDATE behaviour, I suspect indexes could play a role in it. It sounds strange even to myself but it looks like if there's a table scan involved as opposed to the index scan when selecting a record for update then it stumbles upon the locked record and blocks.

    I just tried this with v 7.2 on Windows. Without indexes the second update blocks until the first one is committed. If I build an index on EMPNO the second update doesn't block.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    thanks for pointing out the difference between Oracle and DB2 ...

    Did you have an opportunity to check why the apps without indexes were put in lock wait state ?

    Cheers

    Sathyaram


    Originally posted by n_i
    I think this won't be an exact simulation: Oracle's SELECT would read _old_ values, whereas in DB2 SELECT... WITH UR will fetch the _new_ values.

    As for the UPDATE behaviour, I suspect indexes could play a role in it. It sounds strange even to myself but it looks like if there's a table scan involved as opposed to the index scan when selecting a record for update then it stumbles upon the locked record and blocks.

    I just tried this with v 7.2 on Windows. Without indexes the second update blocks until the first one is committed. If I build an index on EMPNO the second update doesn't block.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Dec 2002
    Posts
    134
    Originally posted by ryanveach
    Why can't two users update two different rows in the same table at the same time?
    They can if you have regular/unique index on the column you use in update. Otherwise you will have situation you described.

    regards,
    dmitri

  13. #13
    Join Date
    Dec 2002
    Posts
    134
    One clarification - the where predicate should be indexes, in you case it's empno

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by sathyaram_s

    Did you have an opportunity to check why the apps without indexes were put in lock wait state ?
    The "Administration: Performance" manual has some discussion regarding lock compatibility etc. I believe what happens is this: when the table scan is used for locating a row for UPDATE each scanned row is left with an U (update) lock. The second connection attempts to do the same and blocks since the U lock cannot be obtained twice for the same row. However, if the index scan is used to locate the record only that record is locked; therefore, the other connection is able to lock a different row.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Dec 2001
    Posts
    40
    Originally posted by chuzhoi
    They can if you have regular/unique index on the column you use in update. Otherwise you will have situation you described.

    regards,
    dmitri
    Thanks for the reply. That was pretty much the answer I was looking for. But I find it odd, DB2 has a restriction of this sort. What if my tables are not very big and get updated a lot? And I want to use Merge joins instead of Nested loop joins in my execution plans. In that case I wouldn't want to over index because of the overhead of updating etc..(even the columns in the where clause). In this situtation, the only reason for me to create a index is to make DB2 perform row level locking?

    Thanks

Posting Permissions

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