The Difference and use of for update and for update nowait, updatenowait

The Difference and use of for update and for update nowait, updatenowait

First, for update and for update nowait are used to lock the operated data rows and prevent other operations from modifying the data before the transaction is committed.

The main difference between for update and for update nowait is whether to wait. If nowait is not added, an error will be reported when the select statement is executed. If nowait is added, the select statement will wait, the lock is released.

First, we use two SQL statements:

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

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

Sql1 is executed in pl/SQL, and sql2 is executed in ob12 (two windows in pl/SQL or ob12 cannot be executed, and I don't know why ):

After sql1 is executed, the correct information is queried. When sql2 is executed, the error message "ORA-00054: the resource is busy, but it specifies that the resource is obtained in NOWAIT mode, or timeout is invalid ". This is because the data to be modified is locked when sql1 is executed, and other operations cannot be accessed. After SQL 1 is executed, SQL 2 displays the correct data.

Replace sql2 with for update and perform the preceding steps. sql2 will wait until the lock is released until after sql1 is commit and sql2 will be able to query the data;

Here there is also for update wait n (n is time, unit: seconds), that is, waiting for n seconds, if the data is locked after n seconds, the above error will be reported;

In fact, for update is to prevent data modification when querying data. for example, there are two SQL statements:

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

Sql2: update HH set name = 'zhang san' where id = '1'

After sql1 is executed, sql2 and sql2 will wait until sql1 releases the lock before execution. In this way, no data changes will be made during the query and commit will be executed after sql1, sql2 is automatically executed.