Concurrency Issue

Database
N/A

Concurrency Issue

Hi All,

I am facing concurrency issue now. My script have concurrent update on same record and 

SELECT statement is executed before update in order to get the updated data for calculation. 

Therefore, I need to lock the record, after executing the SELECT statement.

=====Script like ===

Sel col_a, col_b, Is_reported from table_a where col_a='x';

Update table_a set Is_reported = 1 where Is_reported=null and col_a='x';

=====

I'm facing a case, when table_a is being loaded select is going for dirty read but by the time update statements starts, 

table was loaded so it updates the transactions more than the select reported which leads to report inconsistency.

Is there a way we can bundle both the transactions against same set of data. Like whatever the select reports only those will be eligible for update.

Will BT/ ET help avoid this or any locking modifier?

Thanks

Sen

Tags (2)
2 REPLIES
N/A

Re: Concurrency Issue

How I understand your problem I would expect that  BT / ET plus read lock for the select should solve your issue.

But this mean also that you can not load and do the SELECT/UPDATE in parallel. 

N/A

Re: Concurrency Issue

Hi Sen,

is col_a the PI of table_a?

Are there additional statements between the select and the update?

This should probably work:

BT;

lock row write

Sel col_a, col_b, Is_reported from table_a where col_a='x';

Update table_a set Is_reported = 1 where Is_reported=null and col_a='x';

ET;

If this is a BTEQ script the best way would be a multistatement request, so the optimizer knows there's an update after the select:

Sel col_a, col_b, Is_reported from table_a where col_a='x'

;Update table_a set Is_reported = 1 where Is_reported=null and col_a='x';

Dieter