locking tables
We have a table, id_table, that have 2 columns:
cust_id NUMBER(10)
cust_name VARCHAR2(16)
My requirement, for each request find the min cust_id that has a null cust_name and send it to an external system to get the cust_name.
Then cust_name is updated with value returned by the external system.
The problem arises when we 2 requests at the same time.
The solution we came up with is
LOCK TABLE id_table IN EXCLUSIVE MODE;
SELECT min(cust_id) into min_cust_id from id_table where cust_name is null;
Send min(cust_id) to an external system, then update the table with the Returned_cust_name