Please note that I am a newbie to Oracle SQL.So according to my knowledge in any database, there are ways to handle concurrency. So what I want to get clarified is, like I mentioned previously according to my knowledge, when we do two or more transactions parallelly, database will automatically figure it out by it self , how to process these transactions parallelly(As serializable schedules) by overcoming the read-write conflicts(to achieve this database use locking mechanisms) .So by following all of these mechanisms in the end I know the database will by default ensure that when we run two or more transactions concurrently, there will be isolation among them. So my problem is when there is always isolation among transactions (it means that there won't be any read-write conflicts happen between transactions) why we still need to lock any data in the database by using statements like SELECT FOR UPDATE.I mean why we can't proceed with whatever we want in a transaction block? Ex:

Begin transaction
//Queries
//commit
End transaction

1 Answer

why we can't proceed with whatever we want in a transaction block?

Because in Oracle, readers don't block writers. If you select from a table you don't lock it and there is no transaction. So, imagine you do:

select col1, col2 into l_col1, l_col2
from table1
where ...;
if (<some logic based on l_col2`) then update table1 set col2 = something where col1 = l_col1;
end if;

That's fine; but if two sessions run that code at the same time, one session could update the row between the other session selecting and performing its update - causing a lost update, or incorrect logic as the state wasn't actually as expected.

If instead you do select ... for update:

select col1, col2 into l_col1, l_col2
from table
where ...
for update; -- or: for update of col2
if (<some logic based on l_col2`) then update table1 set col2 = something where col1 = l_col1;
end if;

then the table is locked by the first session when it does select for update, so a second session has to wait for that transaction to complete before it can obtain the lock, and it will see the state of the row after the first update has applied. No lost update, no confusion.

You can also lock rows you don't want to update, as the documentation says:

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update, as in Example 9-6.

And you can tweak the behaviour:

By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement. For information about these clauses, see Oracle Database SQL Language Reference.

If the select for update is in a cursor loop then it also allows you to do

 update table1 set col2 = something where current of <cursor>;

When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. (The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor.)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy