Oracle stored procedure thread-safe?

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'?

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.

Oracle does not lock on SELECT (unless there is a FOR UPDATE clause), so there is a chance that the row will be updated between the SELECT and UPDATE.

But the more likely scenario is that the row would have been updated PRIOR to the select and not committed. The SELECT would show the current committed state. Then the UPDATE would come along and wait on the lock.

Assuming you don't want to block the session, look at SELECT...FOR UPDATE NOWAIT, and deal with the exception. An alternative is SERIALIZABLE isolation level. That will basically throw an "ORA-08177 can't serialize access for this transaction" error if an attempt is made to change data that was not current at the start of the transaction.

You don't need to roll your own checking mechanism. Oracle already provides this functionality with its SELECT ... FOR UPDATE syntax. The WAIT|NOWAIT controls the behaviour if the selected row is locked (wait or fail immediately). Find out more. (In 11g Oracle exposed the SKIP ROWS clause which allows us to implement our own queuing mechanism.)

If you want need a more complicated locking process Oracle allows us to build our own with the DBMS_LOCK package. However, privileges on this package are not granted to anybody by default; this is because building custom locking routines is tricky.