java - Oracle 行锁用于相同数据时的更新

Question

我的表中有 5 个优惠券代码，这些优惠券代码是相同的。如果 10 个客户同时应用优惠券代码 [FIRST5]，那么我需要分别将 5 个客户的优惠券更新为“LOCKED”和 CUST_ID。对于这种情况，我尝试使用下面的 SQL 来锁定行并在客户申请优惠券时获取 P_KEY 来更新状态和客户 ID。但我无法为相应客户更新最新的 P_KEY。请告知正确的做法。

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

Answer 1

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.

我不知道有什么好的纯 SQL 方法可以做到这一点，因为 FOR UPDATE 子句不会影响查询的结果集。它仅影响行的获取方式。

所以，你可能会想尝试一下:

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

有理由认为这将导致 Oracle 读取所有匹配的 coupon_details 行，跳过任何锁定的行，然后在第一行之后停止。但只有在 for update 子句之后应用 rownum=1 条件时，这才有效。

不幸的是，按照其工作方式，首先应用 rownum=1 条件，因为 FOR UPDATE 仅在提取期间发生。因此，最终发生的情况是每个 session 只查看第一行。如果未锁定，则返回该p_key。但如果第一行被锁定，则它不会返回任何数据。 (或者，在您发布的查询中，不包括SKIP LOCKED，第一个 session 之后的 session 将只是等待。)

您真正需要做的是选择所有行，然后获取它们(跳过锁定的行)，然后在第一行之后停止。

为此您需要 PL/SQL。这是一个例子:

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;

...在提交之前，请务必UPDATE coupon_details SET status = 'LOCKED' WHERE p_key = l_key。