Page 1 of 1

Exclusive lock on table row to solve concurrency

PostPosted: Wed Aug 08, 2012 8:36 pm
by abhinav_kr
Hi, I have a table containing column emp_id and salary. Now I have a situation where my DB2 Cobol batch program is accessing a table to get minimum emp_id conditioned on salary say; Select min(emp_id), salary from table where salary< xyz for update of salary.
this emp_id is used by program and salary is increased by some amount to reach more than xyz value.
There can by multiple instances running in parallel to access this table.
I want to make sure that my program won't get concurrency and first table get the exclusive lock on result set so 2nd program won;t retrieve the same emp_id as result of query.
for Ex: table has
Emp Id Salary
11 4999
12 50001
13 3000
Now first program will retrieve emp_id 11 and 2nd program will also retrieve emp_id 11. My requirement is that second instance of program wait till first program process emp_id, change the salary and commit the changes. second instance should get emp_id 12.
I tried to execute query with isolation level 'RR' but doesn't sure it's working.
Please suggest.

Thanks , A

Re: Exclusive lock on table row to solve concurrency

PostPosted: Wed Aug 08, 2012 9:12 pm
by dick scherrer
Hello and welcome to the forum,

Suggest you consider a different way to run. You probably do not want multiples of this running concurrently in batch.

If you read rows for update they will be locked to other processes, but you would be better off (imho) to run these serially rather than concurrently.