Update in Oracle

The Oracle database update was used a few days ago, but there were a lot of problems with this simple problem. Therefore, we will search online materials and summarize them here for temporary use.

The SQL statements listed below are based on the following table:

Create table test (name varchar2 (30), code varchar2 (10), I _d varchar2 (10 ));

Insert data

Insert into test (name, code, I _d) values ('zhu1', '001', '1 ');
Insert into test (name, code, I _d) values ('zhu2', '002', '2 ');
Insert into test (name, code, I _d) values ('zhu3', '003 ', '3 ');
Commit;
Select * from test s;

1. update the data whose I _d is 1

-- Method 1
Update test set name = 'zhurhyme1 ',
Code = '007 'where I _d = '1 ';
Commit;

This can be successful

-- Method 2

Update test set (name, code) = (
'Zhurhyme2', '007 ')
Where I _d = '1 ';

Note: The update set must be a subquery, so you need to change it:

-- Method 3

Update test set (name, code) = (
Select 'zhurhyme3', '007 'from dual)
Where I _d = '1 ';

Commit;

2. After update is finished, let's write about for update, for update

The following information is found on the Internet, but the specific website cannot be found now. Please forgive me for your carelessness and reference others without writing any source.

For update is often used, but for updade of is not often used. Now we make a distinction between the two.

A. select * from test for update locks all rows in the table and can only be read but not written.

B. select * from test where I _d = 1 for update: only the row I _d = 1 is locked, but other rows in other tables are not locked.

Create another table

Create table t (dept_id varchar (10), dept_name varchar2 (50 ));

C. select * from test a join t on a. I _d = t. dept_id for update; in this way, all data in the two tables will be locked.

D. select * from test a join t on a. I _d = t. dept_id where a. I _d = 1 for update; this will lock the data that meets the conditions

E. select * from test a join t on. I _d = t. dept_id where. I _d = 1 for update of. I _d; pay attention to the distinction between d and e. e only locks the data rows that meet the conditions in the test table, but does not lock the data in the t table, because the previous update in procedure, the data to be updated needs to be associated for query, so the use of for update causes blocking for updates by other users.

For update of is a row-Level Lock. This row-Level Lock starts when a cursor is opened, ends at the transaction's commit or rollback, rather than the close of the cursor.

If two cursor columns update the same row record of the table at the same time, in fact, only one cursor is executing, and the other one is waiting until the other is complete, and it will execute it on its own. if the first cursor cannot be properly processed and the second cursor does not release resources, a deadlock is generated.

Executing the following code will lead to deadlocks (executed in two command Windows)

Declare
Cursor cur_test
Is
Select name, code from test where I _d = 1 for update of name;
Begin

For rec in cur_test loop
Update test set name = 'tttt1' where current of cur_test;
End loop;
End;
/

Declare
Cursor cur_test
Is
Select name, code from test where I _d = 1 for update of name;
Begin

For rec in cur_test loop
Update test set name = 'tttt2' where current of cur_test;
End loop;
End;
/

Note that no commit exists in two pl/SQL blocks;

To solve this deadlock problem, either the first block releases resources or the second block voluntarily gives up. it is easy to release resources for the first time, so execute commit or rollback, and let the second part voluntarily give up, add no wait after for update; this will report

ORA-00054 [resource busy and acquire with NOWAIT specified error, so there is no deadlock.