Select for update & Select for update nowait

As everyone knows , Oracle normally has 2 kinds of locks : row-level lock & table-level lock . So how do we use these 2 locks in our daily work is a touchstone to test whether a qualified Software Engineer really knows about the database and utilises them in their projects.

Differences between for update and for update nowait

Firstly, if you only write a select in your sql statement , Oracle won't add any locks there. In other word . Oracle doesn't bind any limitation to the content of your query. So even though there exists another thread modifying the content of the table and commit it which greatly affects your query result at that time, Because there're not locks,  your query result may be outdated . 

If for update is added,  Oracle will monitor thoese rows being modified, blocking other threads to fire select statement. once the modification finishs (commited / rollbacked), Oracle will automatically let go the blocking select.

For update and for update nowait will both lock the rows fulfiling the where statements . the difference is that if another thread is modifying the data , for update nowait will not wait for any resource to release . as long as it find the row locked , it will throw an "ORA-00054" error: the resouce is busy.

In practice, for update or for update nowait is doing the query with lock . In the select statement , as long as any row is locked , then the whole result won't return and waiting for the resouce to be released ( if added nowait, it will throws error)

Statement explanation 

sql1 : select * from t where a='1' for update; 
sql2 : select * from t where a='1'; // executed succesfully, for row-level lock won't affect pure select statement;
sql3 : select * from t where a='1' for update; // Waiting, unless sql1 is commited or rollbacked
sql4 : select * from t where a='1' for update nowait; // not wait, directly throw an exception
sql5 : select * from t where a='1' for update wait 6; // wait for 6 seconds , then throw exception if resource not released
sql6 :  select * from t where a='1' for update nowait skip Locked; // neither wait nor throw exceptions , it will ignore rows locked and result rows not locked

Select for update of 

The of is very important when multiple tables are involved.  if we don't use of to specify which column will lock the table , then all the tables with related rows will be locked. if in of, we specify the column to be monitored(modifed) , then only those table rows related to those columns will be locked.

sql1 : select * from tt,tt2 where tt.a = tt2.a2 for update; // lock the whole 2 tables 
sql2 : select * from tt,tt2 where tt.a = tt2.a2 for update of a; // only lock table tt, not lock table tt2
sql3 : select * from tt2 for update wait 3  // lock table tt2
sql4 : select * from tt for update ; // lock all rows = lock whole table , read-only, can't write 
sql5 :  select * from t where a='1' for update; // lock all rows with a = '1'