Oracle knowledge point Summary 1 (select from for update)

Oracle knowledge point Summary 1 (select from for update) 1. select from for update: When we use ORACLE for data processing, we sometimes need to lock the queried records and prohibit other processes from modifying the records. Oracle Database provides a way to use the select for update clause (sqlserver may also have this function, but it has not been tested ). You can use the select * from ta for update method to lock the records in the query results, and do not allow others to modify these records. We can also use select * from a for update of a. a; (column a of table a) to lock a column in the record. When we use the for update clause to lock a record, other processes will pause and wait FOR unlocking when performing update or delete operations on the lock record. The lock process will be resumed only after the lock is released. If you do not want to pause the operation, you can use the nowait clause to directly return the operation exception information, prompting that the operation record is locked and cannot be modified. Therefore, when performing multi-threaded synchronization, we can use the select * form ta for update nowait method to prevent multiple processes from simultaneously operating the same data. This locking method is based on the database connection. Once the connection is closed or the process commit is locked, the lock is automatically released. At the same time, this locking method is an update lock, and the lock timing does not affect other select operations. 2. the query used to create a table using the subquery can be used as the table structure and row source. Its syntax is as follows:

create table [schema.] table as subquery;

 

The query is used to return a two-dimensional set of rows. The result is stored as a new table. A simple example of creating a table using a subquery is as follows:

create table employees_copy as select * from employees;

 

This statement creates the table employees_copy, which is a copy of the employees table and has the same definition and contains rows. Note: The not null and check constraints used in the column are also applied to the new table, but all primary key, unique, or foreign key constraints are not applicable.