Do I need explicit locking when using atomic update

Question

In my web application, I store a counter value in the database table, which I need to increment or reset at each transaction (which are highly concurrent). Do I need to explicitly lock the row to avoid lost updates? Read committed transaction isolation level is being used at the connection level. The following statement updates the counter

UPDATE Counter c SET value =
  CASE
    WHEN c.last_updated = SYSDATE THEN c.value+1
    ELSE 1
  END,
  last_updated = SYSDATE
WHERE c.counter_id = 123;

The statement is atomic and read committed isolation level implicitly locks the rows for update statements, as far as I know. Does this render the use of explicit locking redundant in this case?

Kirill Leontev Kirill Leontev · Accepted Answer · 2019-11-25T03:14:57

You're talking optimistic locking vs pessimistic locking ("explicit lock").

If you go with pessimistic locking, you're guaranteed to have no lost updates. However, the approach comes at a price:

It doesn't scale well - you're essentially serializing access to the row being updated, and if the client running the first transaction hangs for some reason - everyone is stuck.
Given the nature of the usually multi-tier web apps, it may be difficult (or impossible) to implement, as the explicit lock needs to be run in the same database connection as the update itself, which your middle tier may or may not guarantee.

So you can go with optimistic locking instead. Assume the following table:

create table t (key int, value int, version int);
insert into t (1, 1, 1);

Basically, the logic would be like this (PL/SQL code):

 declare
    current_version t.version%type;
    current_value t.value%type;
    new_value t.value%type;
begin 

    -- read current version of a row
    select version, value 
    into current_version, current_value 
    from t where id = 1;

    -- calculate new value; while we're doing this, 
    -- someone else may update the row, changing its version
    new_value = func_calculate_new_value(current_value);

    -- update the row...
    update t 
    set 
        value = new_value,
        version = version + 1
    where 1 = 1 
        and id = 1 
        -- but ONLY if the version of the row is the one we read
        -- otherwise there would be a lost update
        and version = current_version
    ;

    if sql%rowcount = 0 then
        -- 0 updated rows means the version is different 
        -- we're not updating because we don't want lost updates
        -- and we throw to let the caller know
        raise_application_error(-20000, 'Row version has changed');
        rollback;
    end if;
end;

Do I need explicit locking when using atomic update

1 Answers