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:
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; /
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:
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!!