Oracle stored procedure thread-safe?

Question

Pseudo code:

1. begin stored procedure

2. commit

3. check the value column 1 of row a //step 1

4. update column 1 of row a //step 2

5. commit

6. end stored procedure

Is this sp thread safe?

edit:

Declare
  tag_rec prep_tag%ROWTYPE;
begin

  COMMIT; 

  SELECT * INTO tag_rec 
  FROM PREP_TAG 
  WHERE project = 'a' and categoryId = 'b'; 

  if tag_rec.locked = 'No' then

    UPDATE prep_tag 
    SET locked = 'Yes' 
    WHERE TAG_NUMBER = tag_rec.TAG_NUMBER;

  end if;

  COMMIT; 

end;

Is this sp thread safe? Is it possible that thread A checked tag_rec.locked = 'No' then it's about to update it. But before it does that, thread B sneaks in and also see tag_rec.locked = 'No'?

Answer 1

The short answer to your question is no, this is not thread safe. Some other session could come in and update prep_tag in between your SELECT and your UPDATE statements.

The long answer is that you're doing this the wrong way. It sounds like you want to update the locked field for those records. You might as well just do:

UPDATE prep_tag SET locked = 'Yes' 
 WHERE project = 'a' 
   AND categoryId = 'b'
   AND locked = 'No'

One statement, and it's guaranteed to be atomic.

Also, I'd suggest not committing inside your block, assuming there's more to this transaction.