How to prevent two users of an application from working on the same dataset

Suppose table T contains stock data and two users are updating the warehouse stock with an optical device. The purpose here is to prevent both users from working on the same product.

User1 and user2 are users of an application APP that has typical front-end, micro-services backend and data persistency layers representedfor simplicity by the “APP PRROCESS N’s” and the data table as follows:

SOLUTION

First, the application logic executes a SELECT FOR UPDATE WITH NOWAIT clause in order to obtain a lock on the raw that has the product code the user has picked up.

Second the application issues an update, the user takes a look to the device screen and finally saves the changes (commit).

If the other user selects the same product while the first is doing the task, the NOWAIT part of the clause raises an ORA-00054 error and exits.

Obviously, if the second user selects the product after the other user finished the task, he will be able to change the data properly.

The code snippets down here are in PL/SQL but seamlessly construct can be done in whatever programing language of your choice.

CREATE FOLLOWING ENTITY

drop table t;
create table t (id number, c varchar2(10));
alter table t add constraint pk primary key("ID");
insert into t values (1,'0');
insert into t values (2,'0');
commit;
select * from t;

OPEN TWO DATABASE CLIENT SESSIONS AND EXECUTE THE FOLLOWING ON BOTH SIDES ONE AFTER THE OTHER

Put one session on the left and other on the right if you will…

--
-- smart locking mechanism
--
set serveroutput on
declare
    x varchar2(10);
    n number;
begin
    n := round(dbms_random.value()*3456);
    dbms_output.put_line(systimestamp || ' before select for update ');
    select c  into x from t where id=1 for update nowait;
    dbms_output.put_line(systimestamp || ' after select for update ');
    dbms_output.put_line(systimestamp || ' before  update ');
    update t set c=to_char(n) where id=1;
    dbms_output.put_line(systimestamp || ' c has been set to ' || n);
    dbms_output.put_line(systimestamp || ' after update ');
    dbms_output.put_line(systimestamp || ' before sleep (app think tme)');
    sys.dbms_session.sleep(15);
    dbms_output.put_line(systimestamp || ' after sleep ' );
    commit;
    dbms_output.put_line(systimestamp || ' after commit ' );
exception
    when others then
        rollback;
        dbms_output.put_line(systimestamp || ' error ' || sqlerrm);
        dbms_output.put_line(systimestamp || ' this side failed cowardly...');
end;
/

WHAT HAPPENS?

Let’s suppose you executed first the one on the left of your screen, then the execution on the right fails because there is a lock on the left not already committed due to the extra idle time of the sleep instruction (simulating application user think time):

Obviously, the opposite happens if we change the order of execution:

EXTRA QUESTION

What happens if you put one session on the top and the other on the bottom of the screen? 🙂

That’s all folks, hope it helps!!