Oracle experience 5 -- add, delete, modify, query, and @ oracle transaction processing @ Case Analysis

Oracle experience 5 -- add, delete, modify, query, and @ oracle transaction processing @ Case Analysis

1. add, delete, modify, and query DML (Data Manipulation Language-Data operation Language) in Oracle can be executed under the following conditions: insert Data into the table, modify existing Data, and delete existing Data. A transaction is composed of DML statements that complete several tasks.
2. Insert data
The values of each column are listed by default. The column names and their values are randomly listed in the INSERT clause. Character and date data should be included in single quotes.

Use & variable to specify column values in SQL statements. & Put the variable in the VALUES clause. & Variable is a placeholder, that is, entering a value in the console
Insert into orders ments
(Department_id, department_name, location_id)
VALUES (& department_id, '& department_name', & location );



3. Data Update operations
Only one data entry can be added at a time, but multiple data entries can be updated at a time.
If the WHERE clause is omitted, all data in the table will be updated.




4. delete data





5. database transactions are composed of the following parts:
One or more DML statements
A ddl (Data Definition Language-Data Definition Language) Statement
A dcl (Data Control Language-Data Control Language) Statement
Start with the execution of the first DML statement, and end with one of the following:
COMMIT or ROLLBACK statement; DDL or DCL Statement (automatically submitted); user session ends normally; System exception ends.
With the COMMIT and ROLLBACK statements, we can: ensure data integrity.
Preview data changes before they are submitted. Groups logically related operations.

 


Data status before submission or rollback
Automatic submission is executed in the following cases: DDL statement and DCL statement.
If you do not use the COMMIT or ROLLBACK statement to submit or roll back, the session ends normally.
Session ends abnormally or system exceptions may cause automatic rollback.
Status of submitted data
The data status before the change can be restored.
Users who execute DML operations can use the 'select' statement to query the previous corrections.
Other users cannot see the changes made by the current user until the current user ends the transaction.
The Rows involved in DML statements are locked and cannot be operated by other users.
Status after data rollback
ROLLBACK statements can invalidate data changes:
Data changes are canceled. The data status before modification is restored. The lock is released.

6. Database isolation level
For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, various concurrency problems will occur:
Dirty read: For two things T1, T2, T1 read fields that have been updated by T2 but not submitted. if T2 is rolled back, the content read by T1 is temporary and invalid.
Repeatable reading: one field is read for T1, T2, and T1, and then T2. after T2. T1 reads the same field again, the value is different.
Phantom read: For two things T1, T2, T1 read a field from a table, and T2 inserts some new rows in the table. then, if T1 reads the same table again, there will be several more rows.
Database transaction isolation: the database system must be able to isolate and concurrently run various transactions so that they do not affect each other and avoid various concurrency problems.
The degree to which a transaction is isolated from other transactions is called the isolation level. the database specifies multiple transaction isolation levels. Different isolation levels correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

7. Conceptual features of transactions
Concept: transactions in databases are logical units of work. A transaction is composed of one or more SQL statements that complete a group of related behaviors, the transaction mechanism is used to ensure that the operations performed by these SQL statements are either completely successful, and the whole unit of work is completed or not executed at all.
Main features: ensure the integrity of the database.
ACID features of transactions:
A transaction is formed for a group of SQL statement operations. The database operating system must ensure the atomicity, consistency, isolation, and durability of these operations.
Atomicity)
The atomicity of a transaction indicates that all operations contained in a transaction are either fully or not performed. That is to say, all activities are either fully reflected or not reflected in the database to ensure Database Consistency.
Consistency)
Transaction consistency means that data in the database must meet the business rule constraints before and after transaction operations and after transaction processing.
Isolation)
Isolation refers to the ability of the database to allow multiple concurrent transactions to read, write, or modify the data at the same time. isolation can prevent concurrent execution of multiple transactions, data inconsistency is caused by cross-execution of their operation commands.
Durability)
Transaction Persistence means that after the transaction processing is completed, its modifications to the data should be permanent. Even if the system encounters a fault, it will not be lost, which is determined by the importance of data.
8. transaction control statements
In the oracle database, the start transaction processing statement is not provided, and all transactions start implicitly. That is to say, in oracle, you cannot use commands to start a transaction. the first statement of the oracle task to modify the database, or some situations that require transaction processing, is the implicit start of the transaction. However, when you want to terminate a transaction, you must end the transaction with the commit and rollback statements.
According to the ACID attribute of the transaction, oracle provides the following transaction control statements:
Set transaction
Set constrains
The constraint mode refers to whether the constraints in the database are immediately applied to the data when the data is modified in the transaction, or whether the constraints are applied after the end of the current transaction.
The Savepoint creates a storage point in the transaction. when the transaction is rolled back due to an exception in processing the transaction, you can specify that the transaction is rolled back to a storage point and then re-Execute from the storage point.
Release savepoint Delete storage point
Rollback rolls back the transaction to cancel any operations on the database
Commit commits transactions to persistently store database operations.
9. Set the properties of a transaction
The set transaction statement can be used to set various attributes of a transaction. The statement must be placed in the first statement of transaction processing.
That is to say, it must be processed in any insert, update, delete statements and other transactions. The Set transaction statement allows you to Set the following attributes of a transaction: Specify the transaction isolation layer, specify the bucket used by the transaction rollback, and specify the name transaction.
Note: When the set transaction statement is used to set attributes, the bucket used for rollback is rarely used. naming transactions are also very simple. Only distributed transactions can reflect the purpose of naming transactions.
Note: set transaction is only valid for the current transaction and ends the transaction. The current settings of the transaction will be invalid.
10. Data Exception
The isolation of a transaction defines the degree of isolation between a transaction and other transactions. to better understand the isolation layer, we first discuss the possible access to the same database by concurrent transactions. in general, the following three situations occur in concurrent transactions:
Wrong read | dirty read
Non-repeated read | non-repeated read
False read | phantom read
Wrong read | dirty read: when a transaction modifies the data, the other transaction reads the data, but the first transaction cancels the data modification for some reason and returns the data to the original state, this is because the data read by the second transaction is inconsistent with the data in the database. this is called an error.
Non-repeated read: after a transaction reads data from the database, the other transaction updates the data. When the first transaction reads the data again, you will find that the data has changed, which is non-repeated read. The result of Non-repeated reads is that the data read twice before and after a transaction is different.
False read: if a transaction reads data based on a certain condition, the other transaction updates the data in the same table. When the first transaction reads data again, different rows are returned based on the search criteria. This is a false read.
The exceptions encountered in the transaction are related to the isolation settings of the transaction. The more the isolation settings of the transaction, the fewer exceptions occur, but the lower the concurrency effect, the less the isolation settings of transactions, the more exceptions occur, and the higher the concurrency effect.
The isolation layer defines the isolation level of four transactions in ansi SQL standard 92 for the inconsistency of data read in 3:
Isolation layer
Wrong read | dirty read
Non-repeated read | non-repeated read
False read | phantom read
Read uncommitted (non-committed READ)
Yes
Yes
Yes
READ COMMITTED)
No
Yes
Yes
Repeatable READ (Repeatable READ)
No
No
Yes
Serializable)
No
No
No
Read committed: the default isolation layer of oracle. Oracle supports two of the preceding four isolation layers: read committed and serializable. In addition, the oralce also defines the read only and read write isolation layers. Serializable: when you set the isolation layer of a transaction, the transaction is completely separated from the transaction, and the transaction is executed in serial mode. This does not mean that a transaction must end before starting another transaction, it means that the execution results of these transactions are consistent with those of one transaction.
When Read only and read write are used, the transaction cannot contain any operation statements to modify data in the database, including insert, update, delete, and create statements. Read only is a subset of serializable. The difference is that read only is Read-only, while serialable can perform DML operations. Read write is the default setting. This option indicates that the transaction can have access statements and modify statements, but is not frequently used.
Create a set transaction statement
List as follows:
Set transaction read only
Set transaction read write
Set transaction isolation level read committed
Set transaction isolation level serializable
Note: These statements are mutually exclusive. You cannot set two or more options at the same time.
End transaction:
Under what circumstances does the transaction end?
Commit a transaction
Rollback
 
11. Read-Only cases
[SQL]
SQL> set transaction read only;

Transaction set

SQL> select * from users where username = 'mj ';

USERNAME PASSWORD NAME ADDRESS ZIP
 
---------------------------------------------------------------------------------------
 
Mj redarmy Chen hongjun

SQL> update users set password = '000000' where username = 'mj ';
 
Update users set password = '000000' where username = 'mj'

ORA-01456: cannot perform insert/delete/update operations in read only transactions

// Dirty read case
 
SQL> set transaction isolation level read committed;

Transaction set

SQL> select * from users where username = 'mj ';
 
USERNAME PASSWORD NAME ADDRESS ZIP
 
---------------------------------------------------------------------------------------
 
Mj redarmy Chen hongjun
SQL> update users set password = '000000' where username = 'mj ';
 
1 row updated

SQL> select * from users where username = 'mj ';

 
USERNAME PASSWORD NAME ADDRESS ZIP
 
---------------------------------------------------------------------------------------
 
Mj 123 Chen hongjun
SQL> rollback;