ORACLE PL/SQL: Testing SELECT FOR UPDATE in Oracle SQL Developer

Question

We are currently trying to implement a SELECT FOR UPDATE in order to lock rows. If I run:

SELECT * FROM data where rowid = 'AAAQA5AAGAACNbEAA1' FOR UPDATE;
SELECT * FROM data where rowid = 'AAAQA5AAGAACNbEAA1' FOR UPDATE NOWAIT;

I get nothing back in ORACLE SQL Developer. Isn't this supposed to throw me an error? Does this have something to do with using the same user id?

Answer 1

What do you mean by "I get nothing back"? Do you mean that neither query returns any rows? If so, that implies that there are no rows in the table that have that ROWID. Do you mean that the first (or second) statement never returns? If so, the session is blocked waiting for the lock to be acquired.

Locks are held by sessions. If the two statements are run in the same session, the second statement will succeed because the the first statement already acquired the lock. If the second statement is run in a different session that happens to be opened by the same user, you should generate an exception that the row is already locked by another session.