Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: DB2 Concurency and locks

    Hello everybody,
    I'm going to make a question on concurrency and on lock. I'm working on IBM z/OS plattform and on CICS environment start a transaction E1 that locks a group of data (select * for update ....) . After 1 second start a batch job E2 with a query on the same table (select * from t1 "for read only"). Question: Will E2 wait for the end of E1 (or timeout), or it'll execute the query with the data consistence at the start of E1?
    Thanks in advance
    Gabriele

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A lock taken as a result of "select for update" does not block other selects that are read only. It will block other "select for update" or any other exclusive lock needed (for update or delete).

    DB2 does not have point in time read consistency like Oracle where E2 can retrieve data consistent with how it looked at the start of E1. DB2 looks at each row, page, or table (depending on the lock size) to see if it is locked or not locked by other applications as each row is accessed.

    So if E1 is holding locks on data that E2 needs, then E2 will wait and may timeout (depending on what the timeout parameter is set). The only exception would be if a deadlock occurs (locks on multiple resources that can never be resolved), in which case DB2 would resolve that by canceling one of the applications.
    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
    Jun 2006
    Posts
    3
    Hello Marcus,
    what do you mean when you say "So if E1 is holding locks on data that E2 needs"? For you "needs" means " need to lock"? Else you contradict your previous statement....
    Thank you for the answer
    G

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by gabrielefrank
    Hello Marcus,
    what do you mean when you say "So if E1 is holding locks on data that E2 needs"? For you "needs" means " need to lock"? Else you contradict your previous statement....
    Thank you for the answer
    G
    DB2 is a pessimistic locking database in that it automatically locks based on the SQL statement executed, and in some cases dependent on the isolation level and release/commit parms.

    So if E1 is holding locks on data and E2 needs to acquire a lock (the lock is acquired automatically by DB2 in order to execute an SQL statement) that is not compatible with the locks E1 already has, then E2 will have to wait (or eventually time-out).

    For example, a lock taken for "select for update" is compatible with a lock taken for a "select" (assuming the select is read only). But two locks that are both "select for update" (assuming they are coming from different applications) are not compatible with each other (cannot exist concurrently).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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