Differences between oracle for update and for update

Comparison:
Select * from TTable1 for update locks all rows in the table and can only be read and cannot be written.
2 select * from TTable1 where pkid = 1 for update only lock the row with pkid = 1
3 select * from Table1 a join Table2 B on a. pkid = B. pkid for update lock all records of both tables
4 select * from Table1 a join Table2 B on a. pkid = B. pkid where a. pkid = 10 for update lock the rows in the two tables that meet the conditions
5. select * from Table1 a join Table2 B on a. pkid = B. pkid where a. pkid = 10 for update of a. pkid only lock rows that meet the conditions in Table1
For update: locks all tables. for update: locks the corresponding tables according to the conditions of the tables after.
-----------
About NOWAIT (If you must use for update, I suggest adding NOWAIT)
When there is a LOCK conflict, an error will be prompted and the STATEMENT will be ended instead of waiting there (for example, the row to be queried has been locked by other transactions, the current LOCK transaction conflicts with it, plus nowait, when the current transaction ends, an error will be prompted and the STATEMENT will be ended immediately without waiting ).
If for update is added, this statement is used to lock specific rows (if a where clause exists, it is the rows that meet the where condition ). When these rows are locked, other sessions can select these rows, but they cannot be changed or deleted until the transaction of the statement ends with the commit statement or rollback statement.
Because the for update clause acquires locks, COMMIT releases these locks. When the lock is released, the cursor is invalid.
These are the differences.
 
About select... for update of columns in oracle
The problem is as follows: select * from emp where empno = 7369 for update; locks the record with employee ID 7369 in the table. Other users cannot perform operations on the record, but can only query the record. Select * from emp where empno = 7369 for update of sal; does this statement mean that only the data of the sal field in the 7369 row of the table is locked, other data can be updated by other users. The test result of the student is the same as that of the two statements. Other users cannot update the entire row. Does this mean that for update of columns is meaningless?

It is estimated that many ORACLE students have not thought about this question [There are not many posts on the Internet ]. Now I will explain its functions.

From the operation of a single table, the effects of the above two statements are indeed the same. However, when multiple table operations are involved, for update of columns plays a very important role. Assume there are two users, scott and mm.

Scott executes the statement: select * from emp e, dept d where e. deptno = d. deptno for update; -- the entire table is locked for both tables.
Mm execution statement: select * from scott. dept for update wait 3; -- attempts to lock the scott user's dept table

The result is:
ERROR is located in row 1st:
ORA-30006: the resource is in use; WAIT times out while performing the operation

Now, scott can unlock rollback first, and then add the of columns to the for update statement for testing.

Scott executes the statement: select * from emp e, dept d where e. deptno = d. deptno for update of sal;
Mm execution statement: select * from scott. dept for update wait 3;

The result is:
The data of the dept table is successfully locked.

Mm executes the statement again: select * from scott. emp for update wait 3;

The result is:
ERROR is located in row 1st:
ORA-30006: the resource is in use; WAIT times out while performing the operation

Through this code case, we can draw a conclusion that for update of columns can be used to specify the tables to be locked when multiple tables are connected to the lock, if the column in the table does not appear after for update, it means that the table is not actually locked. Other users can update the data in these tables. This situation often occurs when users perform operations on views with connection queries. The user only locks the data of the relevant table, and other users can still operate on the data of other original tables in the figure.
 
Oracle for update row lock
The syntax of the SELECT... for update statement is as follows:
SELECT... for update [OF column_list] [WAIT n | NOWAIT] [skip locked];
Where:
The OF clause is used to specify the columns to be updated, that is, to lock specific columns on the row.
The WAIT clause specifies the number of seconds to WAIT for other users to release the lock, to prevent an indefinite WAIT.
The "for update wait" clause has the following advantages:
1. Avoid waiting for locked rows indefinitely;
2. Allow more control over the lock wait time in the application.
3. It is very useful for interactive applications because these users cannot wait for uncertainty.
4 If skip locked is used, the locked row can be crossed and the 'Resource busy' exception report caused by wait n is not reported.

Example:
Create table t (a varchar2 (20), B varchar2 (20 ));
Insert into t values ('1', '1 ');
Insert into t values ('2', '2 ');
Insert into t values ('3', '3 ');
Insert into t values ('4', '4 ');
Perform the following operations:
Open two SQL windows in plsql develope,
Run SQL in window 1
Select * from t where a = '1' for update;
Run sql1 in window 2
1. select * from t where a = '1'; this is not a problem, because row-level locks do not affect pure select statements.
Run sql2 again
2. select * from t where a = '1' for update; the SQL statement is always in the waiting state during execution, unless the SQL statement in window 1 is submitted or rolled back.
How can I keep sql2 from waiting or waiting for the specified time? Run sql3 again.
3. select * from t where a = '1' for update nowait; the exception of resource busy is reported directly when this SQL statement is executed.
If select * from t where a = '1' for update wait 6 is executed, a resource exception is reported after 6 seconds.
If we execute sql4
4. select * from t where a = '1' for update nowait skip Locked; when the SQL statement is executed, no waiting or resource Busy Exception is reported.
Now let's take a look at what will happen when we perform the following operations?
In window 1, execute:
Select * from t where rownum <= 3 nowait skip Locked;
In window 2, execute:
Select * from t where rownum <= 6 nowait skip Locked;
Select for update. Row-level locks are applied for insert, update, and delete operations by default. The principle and operation are the same as that for select for update operations.
Select for update of. This of clause plays a major role in associating multiple tables. for example, if you do not use the column of the Table to be locked, the related rows of all tables are locked. If you specify the columns to be modified in of, only the rows of the tables associated with these columns are locked.