Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: S lock not aquired

    Hi all,
    I have a unit test transaction TS that does
    ....
    select X from T where Y=1 for update with RR
    sleep(30 seconds)
    ....

    and a unit test transaction TX that does
    .....
    update T set N=1 where Y=1 with RS
    sleep(30)
    ......

    I run both TS and TX and during the sleep time I execute
    db2 get snapshot for locks on mydb

    During TX I can see an X lock on T.
    During TS I see NO LOCKS on T, while I thought a S lock should have been acquired....
    In fact, when I run TS after TX, TS doesn't wait for TX to commit.

    Could you please help me understand why I don't see a S lock for TS?
    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Robert1973 View Post
    I have a unit test transaction TS that does
    ....
    select X from T where Y=1 for update with RR
    sleep(30 seconds)
    ....
    I'm assuming you run this in the CLP. By the time your SELECT finishes, you're positioned past the last row of the result set, so there's nothing to S-lock. To observe the lock, you'd need to explicitly open the cursor, fetch a record, then pause.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2012
    Posts
    120
    I'm using DB2 10.1 ESE 64 bit on Windows.

    I'm not running in the CLP but as unit test programs (.exe).
    Each program connects to the DB via CLI, set autocommit off and run the code I summarized... does it change something?

  5. #5
    Join Date
    Mar 2012
    Posts
    120
    If it is like in CLP... I cannot exec a statement like
    "declare c1 cursor for select X from T where Y=1 for update with RR"
    with CLI,
    so could you please suggest a way to acquire a S lock and hold it till commit?
    I'd like to have same behavior of TX which holds the X lock till commit, but I want to hold a S lock instead.
    Thank you very much for any help

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Robert1973 View Post
    Each program connects to the DB via CLI
    I wonder how you do this:

    select X from T where Y=1 for update with RR

    in CLI...

    (I'm sure you do something completely different, so you posted your example just to waste everyone's time.)
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Mar 2012
    Posts
    120
    Quote Originally Posted by n_i View Post
    I wonder how you do this:

    select X from T where Y=1 for update with RR

    in CLI...

    (I'm sure you do something completely different, so you posted your example just to waste everyone's time.)

    I executed the very same statement.
    I created my unit test database and table to do it.. so I really don't understand your point

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    My point is that unless you show your actual code there can't be much useful information coming your way either.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    Have you seen any row locks on table t when you executing TS?
    Can you test it in clp :
    db2 +c "select X from T where Y=1 for update with RR"
    db2pd -d <dbname> -locks showlocks
    and publish the output of db2pd here.
    thx

  10. #10
    Join Date
    Mar 2012
    Posts
    120
    I've seen NO row locks on table T when executing TS.



    The db2pd output is the following:

    C:\Program Files\IBM\SQLLIB_01\BIN>db2pd -d DB2TEST -locks showlocks

    Database Member 0 -- Database DB2TEST -- Active -- Up 1 days 03:05:12 -- Date 31
    /10/2012 14:47:27

    Locks:
    Address TranHdl Lockname Type Mode Sts Owner
    Dur HoldCount Att ReleaseFlg rrIID
    0x73FA6D00 3 01000000010000000100206DD6 VarLock ..S G 3
    1 0 0x00000000 0x40000000 0 01000000010000000100206DD6 SQLP_
    VARIATION (anchor,stmt,env,var={873,1,1,1}, loading = 0, )
    0x73FA7780 3 41414141415649626B457D1AC1 PlanLock ..S G 3
    1 0 0x00000000 0x40000000 0 41414141415649626B457D1AC1 SQLP_
    PLAN ({41414141 62495641 1A7D456B}, loading=0)
    0x73FAA980 3 02004A00000000000000000054 TableLock ..U G 3
    1 0 0x00002000 0x00000001 0 02004A00000000000000000054 SQLP_
    TABLE (obj={2;74})

  11. #11
    Join Date
    Nov 2011
    Posts
    334
    "0x73FAA980 3 02004A00000000000000000054 TableLock ..U G 31 0 0x00002000 0x00000001 0 02004A00000000000000000054 SQLP_TABLE (obj={2;74})"
    That is the table lock ,The lock type is "..U" is because of the "for update" clause。

  12. #12
    Join Date
    Mar 2012
    Posts
    120
    How can you say the U table lock applies to my table?
    If I open a new CLP session and type the same command after days have passed from last commands, the output is the same.
    And if it was related to my table, why if I execute TS while TX is sleeping, TS doesn't wait for TX to release its X lock?

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    You can get application handle through transaction handle ( db2pd -d <dbname> -trans )。
    and then get snapshot for this application handle to see whether it is the exactly session。

    I dont know why tx is not blocking ts at the RR isolation level。
    Could you plz try the following test:
    1、In one clp session executing db2 +c "update T set N=1 where Y=1 with RS"
    2、use db2pd -d <dbname> -locks showlocks to check the locks hold by this session.
    3、 in another clp session executing db2 "select X from T where Y=1 for update with RR"
    to see whether it will be blocked
    4、 use db2pd -d <dbname> -locks showlocks to check the locks hold by this two sessions
    and publish the screen shot here。

Posting Permissions

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