CODEWITHSUNDEEP
javasqloracle-databasejdbctemplate
deadend
deadend

Reputation: 1376

Orace Row Lock For Update When Same Data

There are 5 coupon code in my Table, these coupon codes are same. If 10 customer's will apply coupon code [FIRST5] simultaneously, then i need to update coupon as "LOCKED" and CUST_ID respectively only for 5 customers. For this case i have tried below SQL to lock the row & get P_KEY to update status & cust id when customer apply coupon. But i could not able to update latest P_KEY for the respective customer. Please advice the correct way of doing.

SELECT P_KEY FROM
(SELECT P_KEY FROM COUPON_DETAILS WHERE COUPON_CODE = 'FIRST5' 
AND (STATUS  = 'UNLOCK' OR STATUS IS NULL))
WHERE ROWNUM = 1 FOR UPDATE;

P_KEY   COUPON_CODE     STATUS  CUST_ID
1       FIRST5          UNLOCK
2       FIRST5          UNLOCK
3       FIRST5          UNLOCK
4       FIRST5          UNLOCK
5       FIRST5          UNLOCK

Upvotes: 1

Views: 71

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17934

If 10 customer's will apply coupon code [FIRST5] simultaneously, then i need to update coupon as "LOCKED" and CUST_ID respectively only for 5 customers.

I don't know of a good, pure-SQL way to do this, because the FOR UPDATE clause does not affect the query's result set. It only affects how rows are fetched.

So, you might think to try this:

SELECT p_key 
FROM   coupon_details
WHERE  coupon_code = 'FIRST5'
AND    (status = 'UNLOCK' OR status IS NULL)
AND    rownum = 1
FOR UPDATE SKIP LOCKED;

It's reasonable to think this will cause Oracle to read all the matching coupon_details rows, skip any that are locked, and then stop after the 1st. But that would only work if the rownum=1 condition was applied after the for update clause.

Unfortunately, the way it works, the rownum=1 condition is applied first, because the FOR UPDATE only happens during fetching. So, what winds up happening is that every session looks at the first row only. If it is not locked, it returns that p_key. But if that first row is locked, it returns no data. (Or, in the case of the query you posted, which did not include SKIP LOCKED, the sessions after the first one would just wait.)

What you really need to do is select all the rows and then fetch them (skipping locked rows) and then stop after the first one.

You need PL/SQL for that. Here is an example:

DECLARE
  c SYS_REFCURSOR;
  l_key coupon_details.p_key%TYPE;
BEGIN
    -- Open a cursor on all the coupon details that are available to lock
    OPEN c FOR
        SELECT p_key 
        FROM   coupon_details
        WHERE  coupon_code = 'FIRST5'
        AND    (status = 'UNLOCK' OR status IS NULL)
        FOR UPDATE SKIP LOCKED;
    -- Fetch the first one.  The (FOR UPDATE SKIP LOCKED) will ensure that
    -- the one we fetch is not locked by another user and, after fetching,
    -- will be locked by the current session.
    FETCH c INTO l_key;
    -- Do what you need with the locked row.  In this example, we'll
    -- just print some debug messages.
    IF l_key IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('No free locks!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Locked key ' || l_key);
    END IF;
    -- Close the cursor
    CLOSE c;
END;

... be sure to UPDATE coupon_details SET status = 'LOCKED' WHERE p_key = l_key before you commit.

Upvotes: 1

Related Questions