Demonstrating Performance Improvements with Lock-Free Reservation

In this example, we showcase the significant performance enhancements achieved when utilizing a lock-free column in an application. We achieve this by simulating a scenario involving product sales and updates to stock quantities.

1. Table Creation and Data Seeding: We start by creating a table to store product information, populating it with sample data. This table includes an ITEMS_SOLD column, which maintains a running total of sales for restocking purposes.

CREATE TABLE products (
    product VARCHAR2(10) PRIMARY KEY,
    items_sold NUMBER,
    date1 DATE
);

-- Add CHECK CONSTRAINT
ALTER TABLE products ADD CONSTRAINT lock_condition CHECK (items_sold >= 0);
-- Sample data insertion
-- (product, initial items_sold, date1)
INSERT INTO products (product, items_sold, date1)
VALUES 
    ('banana', 1000, SYSDATE),
    ('apple', 1000, SYSDATE),
    ('lemon', 1000, SYSDATE),
    ('lime', 1000, SYSDATE);
COMMIT;

2. Sales Table and Trigger Setup: We create a sales table to track individual sales transactions. Additionally, an update trigger is established to adjust the stockpile in the products table when sales occur. Note that the trigger includes a simulated transaction time of one second.

CREATE TABLE sales (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product VARCHAR2(10),
    quantity NUMBER,
    CONSTRAINT sales_products_fk FOREIGN KEY (product) REFERENCES products (product)
);

CREATE OR REPLACE TRIGGER sales_trg
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE products p
    SET p.items_sold = p.items_sold - :new.quantity
    WHERE p.product = :new.product;
    
    DBMS_SESSION.SLEEP(1);
END;
/

1. Concurrent Sales Insertion test case:

We set up a scenario where ten users concurrently attempt to insert product sales. This simulates a scenario where multiple users are trying to purchase the same product in turn triggering all transactions updating row in the products table.

We run the test as a normal table and next rerun test after modifying the table with the SQL clause “ALTER TABLE products MODIFY (items_sold RESERVABLE) ;” This will eliminate the lock held on product rows during simultaneous updates.

SET SERVEROUTPUT ON;
DECLARE
    cnt NUMBER := 10;
    cnt1 NUMBER := 1;
    start_time NUMBER;
    mid_time NUMBER;
    end_time NUMBER;
    
    -- Procedure to wait until all the concurrent jobs are done.
    PROCEDURE wait (jobname IN VARCHAR2) IS
        cnt1 NUMBER := 1;
    BEGIN
        NULL;
        WHILE cnt1 >= 1 LOOP
            SELECT COUNT(1) INTO cnt1 FROM dba_scheduler_running_jobs srj
            WHERE srj.job_name LIKE jobname || '%';
            IF cnt1 > 0 THEN
                DBMS_LOCK.SLEEP(1);
            END IF;
        END LOOP;
    END wait;

    -- Procedure to run 10 current jobs to insert into sales table.
    PROCEDURE concurrent_insert IS
    BEGIN
        NULL;
        FOR i IN 1..cnt LOOP
            DBMS_SCHEDULER.CREATE_JOB(
                job_name => 'RLJOB$' || i,
                job_type => 'PLSQL_BLOCK',
                auto_drop => TRUE,
                enabled => TRUE,
                job_action => '
                    BEGIN
                        INSERT INTO sales(product, quantity) VALUES (''banana'', 100);
                        COMMIT;
                    EXCEPTION WHEN OTHERS THEN
                        NULL;
                    END;
                '
            );
        END LOOP;
    END concurrent_insert;
BEGIN
    -- Test time with no lock-free column     
    start_time := DBMS_UTILITY.GET_TIME;
    concurrent_insert();
    wait('RLJOB');
    mid_time := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('Time taken with locks is ' || TO_CHAR((mid_time - start_time)/100) || ' seconds');
 
    -- enable lock-free column and time the insert again
    EXECUTE IMMEDIATE 'ALTER TABLE products MODIFY (items_sold RESERVABLE)';
    
    concurrent_insert();
    wait('RLJOB');
    end_time := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('Time taken without locks is ' || TO_CHAR((end_time - mid_time)/100) || ' seconds');
END;
/

Output:

time taken with locks is 11.32 seconds
time taken without locks is 1.08 seconds

This example showcases a scenario where lock-free columns significantly enhance performance in a concurrent transaction environment.