How can I lock a single row in Oracle SQL

sql oracle row locked
Cr1ms0nStraY picture Cr1ms0nStraY · Jan 13, 2016 · Viewed 20.5k times · Source

It seems simple but I struggle with it. The question is how can I lock for example a single row from the table JOBS with JOB_ID = IT_PROG. I want to do it, because I want to try an exception from a procedure, where it displays you a message when you try to update a locked row. Thanks in advance for your time.

Answer

Marmite Bomber picture Marmite Bomber · Jan 13, 2016

You may lock the record as described in other answers, but you will not see any exception while UPDATEing this row.

The UPDATE statement will wait until the lock will be released, i.e. the session with SELECT ... FOR UPDATE commits. After that the UPDATE will be performed.

The only exeption you can manage is DEADLOCK, i.e.

Session1   SELECT FOR UPDATE record A
Session2   SELECT FOR UPDATE record B
Session1   UPDATE record B  --- wait as record locked
Session2   UPDATE record A  --- deadlock as 1 is waiting on 2 and 2 waiting on 1

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More