Differences and uses for the for update and for update nowait

First, for update and for update nowait is to lock the data rows of the operation and prevent other operations from modifying the data before the transaction commits.

The main difference between for update and for update nowait is whether to wait, if not nowait, to give an error when the select is executed, and if a nowait is added, the select is executed until the lock is released.

First of all we use two sql:

1.select * from HH t where id= ' 1 ' for update

2.select * from HH t where id = ' 1 ' for update nowait

SQL1 executed in PL/SQL, SQL2 executed in OB12 (open two windows in PL/SQL or OB12 No, now I don't know why):

After executing the SQL1, the correct information is queried, the error message "ORA-00054: The resource is busy, but the SQL2 is specified to obtain the resource in NOWAIT mode, or the time-out expires" in the execution. This is because the data of the row is locked when the SQL1 is executed, and other operations cannot access the diverted. When we execute a commit after SQL1, SQL2 will be able to display the correct data.

Replace the SQL2 with for update according to the steps above, SQL2 will wait until the lock is released, until SQL1 commit,sql2 can query the data;

There is also a for update wait n (n is the time, in seconds), which will wait for n seconds, and then the data will be locked after n seconds to report the above mentioned error;

In fact, the for update is to prevent data changes when querying data, such as the following two sql:

Sql1:select * from HH t where id= ' 1 ' for update

Sql2:update HH set name= ' Zhang San ' where id = ' 1 '

When we execute the SQL1, the execution sql2,sql2 will wait until the SQL1 lock is released before execution, so when the query will not travel data changes, after the SQL1 execution commit,sql2 will automatically execute.

Differences and use of the for update and for update nowait