CODEWITHSUNDEEP
db2procedureatomic
rahul prakash
rahul prakash

Reputation: 1

How to do row level locking for ensuring select and update statement thread safe in db2 procedure?

I have one select statement in proc which is query the integer value from one table and then i increment that integer value by one , i have one update statement which is updating the increment value in table .

I want make this atomic while calling the procedure for getting the updated integer value in each request.

please help to make this atomic .

i was trying to use only update command with inline assignment variable like

Update table SET col=col+1, @variable = col+1 where ?

but it is working in sybase db but not wrking in db2 .

Upvotes: 0

Views: 138

Answers (1)

mao
mao

Reputation: 12267

Consider using the following syntax:

select columnName from final table ( update yourTable set columnName = ColumnName + 1 where ... )

This removes the need for two separate statements, better for concurrency.

For best results ensure that the WHERE clause is fully indexed, so you should examine the access plan to confirm this.

Choose the correct isolation level (at connection level, or statement level) to match the other statements (if any) in the transaction with the business requirements.

Upvotes: 1

Related Questions