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

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.

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).