There are 2 tables: codes and usage.

I need to get a list of codes from codes table, do some calculations, then insert them into usage table.

But I need to make sure that the same codes aren't inserted more than once in different requests to usage. But since I can have multiple inserts of the same code in the same request, the code column isn't unique in the usage table, so I need to use some kind of lock.

This is the select I have in my transaction:

  SELECT code.code, code.type,
         COUNT(CASE WHEN code.type = 'type_1' THEN 1 END) OVER () cn1,
         COUNT(CASE WHEN code.type = 'type_3' THEN 1 END) OVER () cn2,
         ROW_NUMBER() OVER (PARTITION BY code.type ORDER BY code.id) rn
  FROM  codes
      WHERE usage.code = usage.code
) t
WHERE (cn1 >= 2 AND cn2 >= 1) AND
      ((type = 'type_1' AND rn <= 2) OR (type = 'type_3' AND rn = 1));

I never used locks, but I think that I might be wrong in this one. Because I lock the usage table. But what can happen is that at the same time, another user selects the same codes. So the lock only prevents updating of usage, but if the other user already selected the same codes, what happens is that after the lock is released, then he can insert these codes as well.

Am I correct? Do I need to make a change in the locking?



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.