sql - How to ensure that two transactions have the same version of data? -

i have table columns , b. let's assume both booleans. these columns accessed functions in program's code.

one function first read , if true, modify b(set false).

the second function first read b , if of value(e.g. true), modify a(set false).

the functions might run in order.

the problem have both of these kind of depending on each other(if second function reads b true, set false), can lead incorrect state of database(a being false , b being false).

ideally, make function triggers second wait until first 1 done rows, , start working. however, i've looked up, looks none of 3 protection levels forbid function reading row, make wait until write.

one of scenarios want avoid is:

function1 starts. reads a(true). function2 starts , reads b(true). function1 sets b false. function2 sets false. left 2 false, should not happen.

the correct scenario might this: function 1 starts. reads a(true). function2 starts, has wait function1 finish. function1 sets b false. function2 can access b , sees it's false, , stops/signals error.

can somehow make work using database mechanisms?

either application lock @squirrel suggests, or code transaction select row updlock (which conflicts other u locks , x locks), or xlock conflicts other locks. no isolation level prevent concurrent reads of row.

also note these locks prevent reads other locking transactions. if in snapshot or read committed snapshot consistent reads not blocked transactions holding u or x lock.