Exclusive lock on table row to solve concurrency



IBM's flagship relational database management system

Exclusive lock on table row to solve concurrency

Postby abhinav_kr » Wed Aug 08, 2012 8:36 pm

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
abhinav_kr
 
Posts: 1
Joined: Wed Aug 08, 2012 8:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Exclusive lock on table row to solve concurrency

Postby dick scherrer » Wed Aug 08, 2012 9:12 pm

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post