DB : perform an update query after a fetch one inside a transaction

Question

I am working on an IBM DB, but I think the concepts are pretty much the same.

I want to fetch a single line, return these data (or save them into variables let's say) and update some fields of that specific row. There might be a number of instance who try to perform that query in the same time, and therefore we need the operation of fetching to be atomic.

Each row has a field called pending, init to FALSE. When one instance fetches this row, this is set to TRUE (that's the update I want to do). Reason that I need it too be atomic, is that my query fetches the first row of the table, where pending is FALSE.

In pseudocode I have something like this :

OPEN_DB(myDb, "DBNAME");  // opening the DB
BEGIN_TRANSACTION(myDb);  // beginning transaction on my db
EXECUTE_QUERY(myDb,"SELECT * FROM tbname WHERE pending == 0 ORDER BY colid LIMIT 1");
... assign a cursor to my results
while (valid_data) {
    // assign column fields to variable

    // and here i want to do the update for this column
    // I guess the problem is here
     EXECUTE_QUERY(myDb,"UPDATE tbname SET pending = 1 WHERE colid=@colid")

}
COMMIT();

Don't worry about the syntax, they are macros and once executed on its own they work. I am guessing the problem is on the second query but why?

• Should I commit first and then perform the second query?
• If yes, then how can I ensure that there will be no other instance reading the same row before I update it?

I guess this doesn't have to do with the DB I am using.

If I comment the second one, code works. If I execute the second one stand alone, it works as well.

Answer 1

Option 1 - do a select for update for the curor which would be pessimistic locking

Option 2 - do an optimistic lock and handle the exception. The lock needs a column like last updated time or version illustrated below:

OPEN CURSOR

UPDATE table set col = :new_value
WHERE id = :id_from_cursor
last_updated = :last_updated_from_cursor;

CHECK if update row count == 1 COMMIT
IF not throw exception

Though I think Option 1 should work nicely unless you a significant time gap between the select & update