Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: select .... for update locking

    What kind of lock will be employed if I do select ..... for update? say, for example, table employee contain the following data:

    id Name
    -- -----
    1 John
    2 Mary
    3 Peter

    If I do "select * from employee where id=2 for update"

    What lock(s) will be employed on the row with id=2?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Other programs can select the row, but no other program can do another select for update, or update, or delete on the row.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2001
    Posts
    80
    Thx Marcus_A,
    From documentation, it say that other txn can not do select... for update, but I did the test, and find that other txn can do the select .... for update.

    I opened 3 CLPs, Windows A, B and C.

    Step 1) Windows A:
    db2 +c "connect to db1"
    db2 +c "select * from test1 for update"
    INT1 INT2
    ----------- -----------
    1 2

    1 record(s) selected.


    Step 2) Windows B:
    db2 +c "connect to db1"
    db2 +c "select * from test1 for update"

    INT1 INT2
    ----------- -----------
    1 2

    1 record(s) selected.

    By documentation, it should be locked by txn of Windows A, but in fact it doesn't


    Step 3) Windows C:

    db2 get snapshot for locks on db1

    Database Lock Snapshot

    Database name = DB1
    Database path = D:\DB2\NODE0000\SQL00001\
    Input database alias = DB1
    Locks held = 4
    Applications currently connected = 2
    Agents currently waiting on locks = 0
    Snapshot timestamp = 11/17/2005 14:21:11.189036

    Application handle = 201
    Application ID = *LOCAL.DB2.051117061930
    Sequence number = 0001
    Application name = db2bp.exe
    CONNECT Authorization ID = MATTHEWLAU
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1252
    Locks held = 2
    Total wait time (ms) = Not Collected

    List Of Locks
    Lock Name = 0x53514C4332453036BD4A32C841
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal Plan Lock
    Mode = S

    Lock Name = 0x53514C4445464C5428DD630641
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal Plan Lock
    Mode = S


    Application handle = 200
    Application ID = *LOCAL.DB2.051117061925
    Sequence number = 0001
    Application name = db2bp.exe
    CONNECT Authorization ID = MATTHEWLAU
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1252
    Locks held = 2
    Total wait time (ms) = Not Collected

    List Of Locks
    Lock Name = 0x53514C4332453036BD4A32C841
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal Plan Lock
    Mode = S

    Lock Name = 0x53514C4445464C5428DD630641
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal Plan Lock
    Mode = S


    All all Internal Plan Lock, no update lock

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    Use cursors. Because what I think is the case here is that when you execute just a SELECT statement, it will open a cursor, fetch and close.
    But the locks remain only if you either specify the cursor WITH HOLD or you don't close and then the second comes in in parallel.
    See also: http://publib.boulder.ibm.com/infoce...n/r0000937.htm

    Also: when using a cursor in CS only the current row will be locked. If you would like to lock every row in the resultset, you will have to use RS isolation level.
    Juliane

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by matthewlau

    By documentation, it should be locked by txn of Windows A, but in fact it doesn't
    Would you care to quote that documentation?

    The one I read says that "SELECT ... FOR UPDATE" only affects subsequent positioned updates and row _blocking_ (this is not the same as "locking").

  6. #6
    Join Date
    Dec 2001
    Posts
    80
    Quote Originally Posted by n_i
    Would you care to quote that documentation?

    The one I read says that "SELECT ... FOR UPDATE" only affects subsequent positioned updates and row _blocking_ (this is not the same as "locking").
    "Use the FOR UPDATE OF clause when performing a select. This clause ensures that a U lock is imposed when process A attempts to read the data. Row blocking, however, is disabled. " Quote from Admin. Guide: Performance

  7. #7
    Join Date
    Dec 2001
    Posts
    80
    Quote Originally Posted by juliane26
    Use cursors. Because what I think is the case here is that when you execute just a SELECT statement, it will open a cursor, fetch and close.
    But the locks remain only if you either specify the cursor WITH HOLD or you don't close and then the second comes in in parallel.
    See also: http://publib.boulder.ibm.com/infoce...n/r0000937.htm

    Also: when using a cursor in CS only the current row will be locked. If you would like to lock every row in the resultset, you will have to use RS isolation level.
    thx,
    We are using jdbc, how can I code the jdbc that specifying the cursor WITH HOLD. thx.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think what actually happens to the rows selected with the FOR UPDATE option depends on the isolation level currently in effect.

    If I run a query (with a FOR UPDATE) using CS isolation a row is only locked with U lock while the cursor is positioned on that row. After the last row is fetched by the internal cursor created for that dynamic SELECT statement no U locks remain in effect. Below are two snapshots; one is taken while the query runs:

    Code:
    List Of Locks
     Lock Name                   = 0x00020C020AAA23110000000052
     Lock Attributes             = 0x00000000
     Release Flags               = 0x00000001
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 178922257
     Object Type                 = Row
     Tablespace Name             = USERSPACE1
     Table Schema                = XXX
     Table Name                  = XXX
     Mode                        = U
    
     Lock Name                   = 0x00000001000000010001030056
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal V Lock
     Mode                        = S
    
     Lock Name                   = 0x94928D848F9F949E7B89505241
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    
     Lock Name                   = 0x96A09A989DA09A7D8E8A6C7441
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    
     Lock Name                   = 0x00020C02000000000000000054
     Lock Attributes             = 0x00000000
     Release Flags               = 0x00000001
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 3074
     Object Type                 = Table
     Tablespace Name             = USERSPACE1
     Table Schema                = XXX
     Table Name                  = XXX
     Mode                        = IX
    the other after the last row has been retrieved:
    Code:
    List Of Locks
     Lock Name                   = 0x94928D848F9F949E7B89505241
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    
     Lock Name                   = 0x96A09A989DA09A7D8E8A6C7441
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    If I run the same query using RR isolation the entire table is locked with a U lock and it remains in effect until the transaction is committed. Here's a snapshot taken while the query runs:
    Code:
    List Of Locks
     Lock Name                   = 0x94928D848F9F949E7B89505241
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    
     Lock Name                   = 0x96A09A989DA09A7D8E8A6C7441
     Lock Attributes             = 0x00000000
     Release Flags               = 0x40000000
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 0
     Object Type                 = Internal P Lock
     Mode                        = S
    
     Lock Name                   = 0x00020C02000000000000000054
     Lock Attributes             = 0x00000010
     Release Flags               = 0x40000002
     Lock Count                  = 1
     Hold Count                  = 0
     Lock Object Name            = 3074
     Object Type                 = Table
     Tablespace Name             = USERSPACE1
     Table Schema                = XXX
     Table Name                  = XXX
     Mode                        = U

Posting Permissions

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